主页 / dba / 问题 / 167778
Accepted
Maury Markowitz
Asked: 2017-03-22 10:10:57 +0800 CST

在 SQL Server 的 WHERE 中解析具有“范围”的列

  • 772

我正在查询一个包含多个字母数字键列的表...

KEY1  KEY2  KEY3 ... SOMEDATA  SOMEOTHERDATA...
s001
s002
s003  s004  s005
s006

键是我们需要加入的另一个表的 PK，这很烦人但并不困难。但后来他们决定这样做......

s010-s105

这意味着 s010、s011...s015。有什么方法可以进行查询来打破这一点，以便我可以在这些列中找到给定的键，比如 s013？

sql-server sql-server-2012

4 个回答

  1. 好吧，请不要因为这个查询而恨我，因为我确信有更好的方法来做到这一点（从修复这个设计开始）。使用您发布的示例数据，此查询应将原始表中的每个键作为单个列 ( KEY_Unified) 提供给您：

    WITH CTE AS
(
    SELECT *
    FROM dbo.YourTable t
    CROSS APPLY 
    (
        VALUES
            (t.KEY1),
            (t.KEY2),
            (t.KEY3)
    ) x (KEY_Unified)
    WHERE x.KEY_Unified IS NOT NULL
), CTE2 AS
(
    SELECT  KEY_Unified, 
            CAST(RIGHT(LEFT(KEY_Unified,CHARINDEX('-',KEY_Unified,0)-1),3) AS INT) Start,
            CAST(RIGHT(SUBSTRING(KEY_Unified,CHARINDEX('-',KEY_Unified,0)+1,4),3) AS INT) Finish
    FROM CTE 
    WHERE KEY_Unified LIKE '%-%'
), CTE3 AS 
(
    SELECT 's' + RIGHT('000' + CAST(B.number AS VARCHAR(3)),3) KEY_Range, CAST(B.number AS VARCHAR(3)) n
    FROM CTE2 A
    CROSS JOIN (SELECT *
                FROM master.dbo.spt_values
                WHERE type = 'P') B
    WHERE B.number BETWEEN A.Start AND A.Finish
), CTE4 AS
(
    SELECT KEY_Unified
    FROM CTE
    WHERE KEY_Unified NOT LIKE '%-%'
    UNION
    SELECT KEY_Range
    FROM CTE3
)
SELECT *
FROM CTE4 t1
LEFT JOIN SomeOtherTable t2
    ON t1.KEY_Unified = t2.SomeKey
;

    不过有几个假设。

    • 您可以拥有的唯一范围是这种： ， 例如s001-s100，您不能拥有s001-s100,s005-s006
    • 每个键的长度为 4 个字符，从s001till开始s999
    • 3

  2. 如果你有一个数字表，你可以解析出范围的两个部分并加入你的数字表。然后你只需要将它格式化回你的连接键的格式。

    create table t (key1 varchar(10),key2 varchar(10),key3 varchar(10));
insert into t values ('s001',null,null),('s010-s105',null,null),('s002','s003','s004');
/* numbers table substitute for demo*/
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, numbers as (
  select n = row_number() over(order by (select 1))-1
  from n as deka cross join n as hecto cross join n as kilo 
                 cross join n as tenK cross join n as hundredK
)
select 
    t.key1
  , x.*
  , joinKey = left(key1,1)+right('000'+convert(varchar(10),n.n),3)
from t 
  cross apply (values (key1),(key2),(key3)
    ) as u (keyN)
  cross apply (values (stuff(left(u.keyN,charindex('-',u.keyN+'-')-1),1,1,'')
                      ,stuff(u.keyN,1,charindex('-',u.keyN)+1,''))
    ) as x (fromkey,thrukey)
  left join numbers n
    on n >= try_convert(int,fromkey)
   and n <= try_convert(int,thrukey)
where u.keyN is not null

    rextester演示：http ://rextester.com/KAJ77714

    回报：

    +-----+-----------+---------+---------+---------+
|     |   key1    | fromkey | thrukey | joinKey |
+-----+-----------+---------+---------+---------+
|   1 | s001      |     001 |     001 | s001    |
|   2 | s002      |     002 |     002 | s002    |
|   3 | s002      |     003 |     003 | s003    |
|   4 | s002      |     004 |     004 | s004    |
|   5 | s010-s105 |     010 |     105 | s010    |
|   6 | s010-s105 |     010 |     105 | s011    |
|   7 | s010-s105 |     010 |     105 | s012    |
|   8 | s010-s105 |     010 |     105 | s013    |
|   9 | s010-s105 |     010 |     105 | s014    |
...
|  97 | s010-s105 |     010 |     105 | s102    |
|  98 | s010-s105 |     010 |     105 | s103    |
|  99 | s010-s105 |     010 |     105 | s104    |
| 100 | s010-s105 |     010 |     105 | s105    |
+-----+-----------+---------+---------+---------+

    号码表参考：

    • 1
  3. Best Answer

    T-SQL - 246 字节

    又快又脏：

    create table #DataInRanges (KeyId varchar(10), FirstName varchar(10), LastName varchar(10));

insert into #DataInRanges (KeyId, FirstName, LastName) values
('s010-s015', 'Nelson', 'Casanova'),
('s009-s010', 'Karl', 'Johnson'),
('s090-s110', 'Michael', 'Buxton'),
('s079-s080', 'Terrence', 'Dickson'),
('s010-s030', 'Mario', 'Carrizo'),
('s025-s085', 'Victor', 'Lee'),
('s019-s040', 'Jim', 'Buckney'),
('s030-s110', 'Solomon', 'Bennett')

declare @ANumber INT = 20;

select * 
from
(
    select
        cast(replace(parsename(replace(KeyId, '-','.'),2), 's', '') as int) as Key1, 
        cast(replace(parsename(replace(KeyId, '-','.'),1), 's', '') as int) as Key2
    from #DataInRanges
) t
where (@ANumber) between t.Key1 and t.Key2

-----------
|key1|key2|
-----------
| 10 | 30 |
| 19 | 40 |
-----------

    需要注意的是 parsename 不会挖掘超过 4 个级别

    • 1

  4. 而且我还要加一个...

    结合上面的一些代码和一些基本的字符串操作，我所做的是进行一个内部查询，返回两个字段中减号的任一侧......

    select Substring(KEY1,CharIndex('-',KEY1)) as KEYF,
Substring(KEY1,CharIndex('-',KEY1)+1,len(KEY1)) as KEYT
from TheDumbTable
where KEY1 like '%-%'

    这会返回类似...

    -------------
|KEYF |KEYT |
-------------
| S10 | S30 |
| S19 | S40 |
-------------

    接下来，我将其包装在另一个使用 PATINDEX 将数字与数字分开的查询中......

    SELECT cast(substring(KEYF,patindex('%[0-9]%', KEYF),len(KEYF)) as int) as KEYFINT,
       cast(substring(KEYT,patindex('%[0-9]%', KEYT),len(KEYT)) as int) as KEYTINT,
       replace(KEYF,substring(KEYF,patindex('%[0-9]%', KEYF),len(KEYF)),'') as KEYTEXT
FROM
(select Substring(KEY1,CharIndex('-',KEY1)) as KEYF,
Substring(KEY1,CharIndex('-',KEY1)+1,len(KEY1)) as KEYT
from TheDumbTable
where KEY1 like '%-%') inside

    结果是这样的......

    -------------------------
|KEYFINT|KEYTINT|KEYTEXT|
-------------------------
| 10    | 30    | S     |
| 19    | 40    | S     |
-------------------------

    如果有人有好的方法将前面的两个查询合并为一个，我将不胜感激。

    此示例的一大优点是密钥的文本部分可以是任何内容，因此此方法适用于“ABC100”和“S10”。最后的技巧是将该查询包装在另一个具有您的 WHERE ...

    WHERE cast(substring('THETHINGYOUWANT',patindex('%[0-9]%', 'THETHINGYOUWANT'),len('THETHINGYOUWANT')) as int) 
BETWEEN UNITMATCHINGRANGES.keyintfrom AND UNITMATCHINGRANGES.keyintto
AND KEYTEXT LIKE replace('THETHINGYOUWANT',substring('THETHINGYOUWANT',patindex('%[0-9]%', 'THETHINGYOUWANT'),len('THETHINGYOUWANT')),''))

    第一部分提取您要查找的密钥的数字部分和 BETWEENs，但这当然意味着“A100”和“B100”都匹配。所以第二部分提取非数字部分并将其与范围内键的非数字部分进行比较。

    这假设范围将始终类似于“A100-A999”，而不是“A100-B100”

    我怀疑有一些方法可以将其显着简化为一个或两个步骤的查询，但目前它正在运行！

    • 0

相关问题