链接到多行的左连接仅返回一行

发布于 2024-08-14 11:40:38 字数 591 浏览 7 评论 0原文

我正在尝试连接两个表(称为 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

请别遗忘我 2024-08-21 11:40:38

首先,我假设“null”值实际上是字符串,而不是数据库值 NULL。
如果是这样,下面的这个查询应该可以工作(注意 ON 子句中包含 where 条件)

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current in ('y','null'))

如果这确实有效,我强烈建议将“null”字符串值更改为其他值,因为它完全具有误导性......您或其他开发人员将来会浪费时间进行调试。

如果“null”实际上指的是空值,则将上面的查询更改为:

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current = 'y' or table2.current is 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)

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current in ('y','null'))

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:

select 
table1.ID as ID
,table2.ID as table2ID
,table2.current 
from table1 left outer join table2 
on (table2.table1ID = table1.ID and 
(table2.current = 'y' or table2.current is null))
蓝眸 2024-08-21 11:40:38

您需要决定 table2 中 table1id = 3 的三行中的哪一行是您想要的:

3 | 3 | n
4 | 3 | n
5 | 3 | n

标准是什么?

you need to decide which of the three rows from table2 with table1id = 3 you want:

3 | 3 | n
4 | 3 | n
5 | 3 | n

what's the criterion?

此生挚爱伱 2024-08-21 11:40:38
select t1.id
     , t2.id
     , case when t2.count_current > 0 then
           t2.count_current 
       else
           null
       end as current
from table1 t1
left outer join
(
  select id
  , max(table1id)
  , sum(case when current = 'y' then 1 else 0 end) as count_current
  from table2
  group by id
) t2
on t1.id = t2.table1id

不过,正如有人指出的那样,一旦表 2 中有多行带有“y”的行,这可能无法按您的预期工作。

select t1.id
     , t2.id
     , case when t2.count_current > 0 then
           t2.count_current 
       else
           null
       end as current
from table1 t1
left outer join
(
  select id
  , max(table1id)
  , sum(case when current = 'y' then 1 else 0 end) as count_current
  from table2
  group by id
) t2
on t1.id = t2.table1id

although, as justsomebody has pointed out, this may not work as you expect once you have multiple rows with 'y' in your table 2.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文