让我们假设以下内容:
表A
id | value ---------- 1 | red 2 | orange 5 | yellow 10 | green 11 | blue 12 | indigo 20 | violet我有一个ID列表(10、11、12、13、14),可用于在此表中查找ID.这个ID列表是在我的前端生成的.
I have a list of id's (10, 11, 12, 13, 14) that can be used to look up id's in this table. This list of id's is generated in my frontend.
使用纯SQL,我需要从此列表(10、11、12、13、14)中选择ID,这些ID在表A中没有条目(在"id"列上联接).结果应该是id的13和14的结果集.
Using purely SQL, I need to select the id's from this list (10, 11, 12, 13, 14) that do not have entries in Table A (joining on the 'id' column). The result should be the resultset of id's 13 and 14.
如何仅使用SQL来完成此操作? (此外,如果可能的话,我想避免使用存储过程)
How can I accomplish this using only SQL? (Also, I'd like to avoid using a stored procedure if possible)
我能想到的唯一方法是可以动态创建一个内联SQL表以临时保存我的ID列表的方法.但是,我不知道该怎么做.这可能吗?有更好的方法吗?
The only approach I can think of is something that would create an inline SQL table on the fly to temporarily hold my list of id's. However, I have no idea how to do this. Is this possible? Is there a better way?
谢谢! :)
推荐答案您可以使用 UNION 子查询:
You can create an "inline table" with a UNION subquery:
( SELECT 10 AS id UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 -- etc. ) AS inline_table更多推荐
动态创建内联SQL表(用于排除左联接)
发布评论