英文:
Sql Query: Return the where condition that not available in database
问题
我是新手,正在尝试学习查询!我想知道是否可能在数据库中没有任何条件存在的情况下获得 where 条件的结果/返回值,以便我可以区分哪些条件存在于数据库中,哪些条件不存在!
例如:
选择 *
从学生
其中学生。id 在(“1”,“2”,“3”)
我期望的结果是,如果 id 为 2 的记录在数据库中不存在,那么我将获得 id 为 2 的结果。因此,我将知道 id 2 尚未在数据库中存在。
谢谢!
英文:
I'm new and trying to learn query! I wonder if it possible to get result/ return value of the where condition if none of the conditions are exist in the database, so I can differentiate which conditions exist and not in database!
For example:
Select *
From Student
Where Student.id IN ("1","2","3")
The Result I expect is if the id of 2 is not exist in database, then I will get the result of 2. Hence, i will know that id 2 is not yet exist in database.
Thank you!
答案1
得分: 0
你使用了一种类似于在表中查找没有在另一个表中找到匹配项的技术,如 https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 中所描述的方法。
但是,由于你是与列表而不是表进行比较,所以你需要从该列表中合成一个表。你可以通过使用子查询或CTE(公共表表达式)来实现这一点。
WITH id_list AS (
SELECT "1" AS id
UNION
SELECT "2"
UNION
SELECT "3"
)
SELECT id_list.id
FROM id_list
LEFT JOIN Student ON id_list.id = Student.id
WHERE Student.id IS NULL
如果需要进一步的帮助,请告诉我。
英文:
You use a technique similar to finding a row in a table that doesn't have a match in another table, as described in https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1
But since you're comparing with a list rather than a table, you have to synthesize a table from that list. You can do that with a subquery or CTE.
WITH id_list AS (
SELECT "1" AS id
UNION
SELECT "2"
UNION
SELECT "3"
)
SELECT id_list.id
FROM id_list
LEFT JOIN Student ON id_list.id = Student.id
WHERE Student.id IS NULL
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论