代码之家 › 专栏 › 技术社区 › Kishore

如何在sql中使用IN子句为不匹配的元素获取空值

sql-server-2000 sql-server sql

Kishore · 技术社区 · 7 年前

SELECT 
  ColAlphaNum, 
  ColId 
FROM SomeTable 
WHERE ColAlphaNum IN ('01AAA','02BBB','03CCC','04DDD')

该表包含值01AAA、02BBB的记录。sql返回以下结果集(我只需要查询“SomeTable”表中的数据

 ColAlphaNum | ColId
 ------------+------
 01AAA       | 5
 02BBB       | 3

我想返回非匹配的记录值为空的如下所示,但无法让它工作。

ColAlphaNum | total
------------+------
01AAA       | 5
02BBB       | 3
03CCC       | NULL
04DDD       | NULL

我试图用案例陈述来达到同样的效果,但没能成功。尝试了这个建议的解决方案 Here

谢谢你的帮助。

3 回复 | 直到 7 年前

Salman Arshad 7 年前

使用和之类的方法从列表中构建一个表 LEFT JOIN 使用它(应该在SQL 2000中工作):

SELECT List.ListItem, SomeTable.ColId
FROM (
    SELECT '01AAA' UNION
    SELECT '02BBB' UNION
    SELECT '03CCC' UNION
    SELECT '04DDD'
) AS List(ListItem)
LEFT JOIN SomeTable ON List.ListItem = SomeTable.ColAlphaNum

Hannah Vernon 7 年前

如果你有大量的项目在 IN (...) 子句,一种可能更好、更有效的方法,可能是创建一个临时表来保存匹配项。

创建临时表:

IF OBJECT_ID(N'tempdb...#Matches', N'U') IS NOT NULL
DROP TABLE #Matches;
CREATE TABLE #Matches
(
    AlphaNum char(5) NOT NULL
        CONSTRAINT Matches_pk
        PRIMARY KEY
        CLUSTERED
);

INSERT INTO #Matches (AlphaNum)
SELECT TOP(100) 
    RIGHT('00' + CONVERT(varchar(2)
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1  ), 2)
    + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
    + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
    + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
FROM sys.syscolumns c1;

temp表中10行的内容:

SELECT TOP(10) *
FROM #Matches;

ââââââââââââ
â AlphaNum â
â âââââââââââ£
â 00BQY    â
â 01RZJ    â
â 02YQB    â
â 03JAY    â
â 04QJB    â
â 05QIB    â
â 06ZYY    â
â 07QBJ    â
â 08ZAI    â
â 09QBA    â
ââââââââââââ

创建 SomeTable

IF OBJECT_ID('dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;
CREATE TABLE dbo.SomeTable
(
    SomeTableID int NOT NULL IDENTITY(1,1)
        CONSTRAINT SomeTable_pk
        PRIMARY KEY
        CLUSTERED
    , AlphaNum char(5) NOT NULL
    , SomeCol varchar(500) NOT NULL
);

插入一些测试数据(同样,此部分不会在SQL Server 2000上运行):

INSERT INTO dbo.SomeTable (AlphaNum, SomeCol)
SELECT TOP(10000) 
    AlphaNum = RIGHT('00' + CONVERT(varchar(2)
               , (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) % 99)
               , 2)
        + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
        + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
        + CHAR((CRYPT_GEN_RANDOM(1) & 25) + 65)
    , SomeCol = CONVERT(varchar(1000), CRYPT_GEN_RANDOM(500))
FROM sys.syscolumns c1
    CROSS JOIN sys.syscolumns c2;

创建支持的非聚集索引:

CREATE NONCLUSTERED INDEX SomeTable_AlphaNum
ON dbo.SomeTable (AlphaNum)
INCLUDE (SomeCol); --INCLUDE clause does not work on SQL Server 2000, ignore it.

和 NULL 某样东西

SELECT m.AlphaNum
    , st.SomeCol
FROM #Matches m
    LEFT JOIN dbo.SomeTable st ON m.AlphaNum = st.AlphaNum;

输出的前20行:

ââââââââââââ¦âââââââââââââââââââââââ
â AlphaNum â       SomeCol        â
â âââââââââââ¬âââââââââââââââââââââââ£
â 00BQY    â NULL                 â
â 01RZJ    â NULL                 â
â 02YQB    â NULL                 â
â 03JAY    â NULL                 â
â 04QJB    â NULL                 â
â 05QIB    â NULL                 â
â 06ZYY    â NULL                 â
â 07QBJ    â SR{mâx â¢Â¨HÃ³â¹ÂµÃ¤Ã´ÃPÃ   â
â 08ZAI    â NULL                 â
â 09QBA    â NULL                 â
â 10RQA    â NULL                 â
â 11IAZ    â NULL                 â
â 12RZI    â NULL                 â
â 13ZRA    â NULL                 â
â 14IAI    â NULL                 â
â 15BIZ    â NULL                 â
â 16JBI    â NULL                 â
â 17AYJ    â Ã NÂ©Uâ¦C4MÃ²ÂºÂ³5Ã¶âiÃ    â
â 18ZJI    â NULL                 â
â 19YRI    â NULL                 â
ââââââââââââ©âââââââââââââââââââââââ

Yogesh Sharma 7 年前

你可以用 values 构造和执行 left join :

select t.ColAlphaNum, s.ColId as total
from ( values ('01AAA'),('02BBB'),('03CCC'),('04DDD') 
     ) t(ColAlphaNum) left join
     SomeTable s
     on s.ColAlphaNum = t.ColAlphaNum;