链接到多行的左连接仅返回一行
我正在尝试连接两个表(称为 table1 和 table2),但每次匹配仅返回 1 个条目。在 table2 中,有一列名为“current”,可以是“y”、“n”或“null”。我已经离开连接了两个表并放置了一个 where 子句来获取“y”和“null”实例,这些很简单。我需要帮助来获取连接到只有“n”的行的行,以返回“none”或“null”的一个实例。这是一个示例
表1 身份证号
1
2
3
表2
身份证 |表1ID |当前
1 | 1 | y
2 | 2 |空
3 | 3 |号
4 | 3 |号
5 | 3 | n
我当前的查询连接 table1.ID=table2.table1ID,然后有一个 where 子句(其中 table2.current = 'y' 或 table2.current = 'null'),但是当没有 'y' 时,这不起作用并且该值不为“空”。
有人能想出一个像我一样加入表的查询,但可以像这样从 table1 中获取所有 3 条记录吗?
查询返回
ID |表2ID |当前
1 | 1 | y
2 |空 |空
3 | 3 |空或无
I'm trying to join two table (call them table1 and table2) but only return 1 entry for each match. In table2, there is a column called 'current' that is either 'y', 'n', or 'null'. I have left joined the two tables and put a where clause to get me the 'y' and 'null' instances, those are easy. I need help to get the rows that join to rows that only have a 'n' to return one instance of a 'none' or 'null'. Here is an example
table1
ID
1
2
3
table2
ID | table1ID | current
1 | 1 | y
2 | 2 | null
3 | 3 | n
4 | 3 | n
5 | 3 | n
My current query joins on table1.ID=table2.table1ID and then has a where clause (where table2.current = 'y' or table2.current = 'null') but that doesn't work when there is no 'y' and the value isn't 'null'.
Can someone come up with a query that would join the table like I have but get me all 3 records from table1 like this?
Query Return
ID | table2ID | current
1 | 1 | y
2 | null | null
3 | 3 | null or none
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
首先,我假设“null”值实际上是字符串,而不是数据库值 NULL。
如果是这样,下面的这个查询应该可以工作(注意 ON 子句中包含 where 条件)
如果这确实有效,我强烈建议将“null”字符串值更改为其他值,因为它完全具有误导性......您或其他开发人员将来会浪费时间进行调试。
如果“null”实际上指的是空值,则将上面的查询更改为:
First off, I'm assuming the "null" values are actually strings and not the DB value NULL.
If so, this query below should work (notice the inclusing of the where criteria INSIDE the ON sub-clause)
If this does work, I would STRONGLY recommend changing the "null" string value to something else as it is entirely misleading... you or some other developer will lose time debugging this in the future.
If "null" acutally refers to the null value, then change the above query to:
您需要决定 table2 中 table1id = 3 的三行中的哪一行是您想要的:
标准是什么?
you need to decide which of the three rows from table2 with table1id = 3 you want:
what's the criterion?
不过,正如有人指出的那样,一旦表 2 中有多行带有“y”的行,这可能无法按您的预期工作。
although, as justsomebody has pointed out, this may not work as you expect once you have multiple rows with 'y' in your table 2.