如何显示表中不存在的子句的元素 [英] How Can I Show Elements Of Where Clause That Are Not Present In Table
本文介绍了如何显示表中不存在的子句的元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表
Table
ID
1234
3456
7890
SELECT *
FROM Table
WHERE ID IN (1234,9999,8888)
如果ID在where子句中不在表中,则显示那些ID。
If ID is in where clause are not in table, then show those ids.
Ouput
9999,8888
推荐答案
一种简单的方法是使用EXCEPT
从所有值中删除现有值。请考虑以下示例
A simple way would by to useEXCEPT
to remove existing values from all values. Consider the following example
SELECT all_values.Id
FROM (SELECT 1234 AS Id UNION ALL
SELECT 9999 AS Id UNION ALL
SELECT 8888 AS Id ) all_values
EXCEPT
SELECT Id
FROM MyTable
有关详细信息,请参阅:EXCEPT和INTERSECT(Transact-SQL) [ ^ ]
>
我使用cte:
I'd use a cte:
with mycte as (
select 1234 as id,
union select 9999,
union select 8888)
select m.id
from mycte m
where not exists (select * from table t where m.id = t.id)
似乎还有很长的路要走,但它非常可扩展。
希望它至少会让你朝着新的方向发展
it seems a long way around but it's VERY scaleable.
Hopefully it will at least push you in a new direction
可以有更好的方法来做到这一点但是检查这个和还等待一些更好的答案。
我用 除了
There can be better ways to do this but check this and also wait for some better answers.
I have used EXCEPT
SELECT * FROM
(
SELECT 1234 AS ID
UNION
SELECT 9999
UNION
SELECT 8888
) T1
EXCEPT
SELECT * FROM
(
SELECT 1234 AS ID
UNION
SELECT 3456
UNION
SELECT 7890
) AS T2
希望,它有助于:)
Hope, it helps :)
这篇关于如何显示表中不存在的子句的元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文