返回数据库中不存在的 where 条件。

huangapple go评论62阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年7月13日 09:35:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76675370.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定