当多列在SQL中具有相同的ID时，加入覆盖

发布于 2025-02-13 04:47:01 字数 1878 浏览 0 评论 0原文

我剩下的是加入SQL中的另一个表格的表格，但是第二个表具有多个具有相同ID的情况，并且当我在与ID匹配时将表连接在一起时，只有最后一个带有匹配ID的情况。我希望发生的是，SQL计数与ID相匹配的次数，然后创建计数和单个情况。

表1：

ID	值
1	AB
2	SD
3	OH
4	SD
5	作为

表2：

ID	PET
1	CAT
1	狗
2	鱼
3	狗
5	猫
5	猫

预期输出：

ID	Val	Count_pets	cat	dog cat	Fish
2	1	1	1	0	dog
2	SD	1	0	0	1
3	OH	1	0	1	0
4	SD	0	0	0	0
5	AS	2	2	0	0

当前我的代码看起来像：

SELECT
    t1.*, t2.*
    FROM Table1 t1
    LEFT Table2 t2
    ON t1.ID = t2.ID;

SD	值	pet pet
1	ab	dog
2	sd	Fish
3	Oh	Dog
4	dog 2 sd	Na
5	AS	Cat

如何如何我得到了这些计数列？

原文

I am left joining a table to another table in SQL, but the second table has multiple cases with the same ID and when I join the tables together while matching with ID, only the last case with the matching ID is appended. What I would prefer to happen is that SQL count the amount of times something matches ID and then creates a count as well as the individual cases.

TABLE 1:

ID	value
1	ab
2	sd
3	oh
4	sd
5	as

TABLE 2:

ID	pet
1	cat
1	dog
2	fish
3	dog
5	cat
5	cat

Expected Output:

ID	val	count_pets	cat	dog	fish
1	ab	2	1	1	0
2	sd	1	0	0	1
3	oh	1	0	1	0
4	sd	0	0	0	0
5	as	2	2	0	0

Currently my code looks like:

SELECT
    t1.*, t2.*
    FROM Table1 t1
    LEFT Table2 t2
    ON t1.ID = t2.ID;

which results in

ID	value	pet
1	ab	dog
2	sd	fish
3	oh	dog
4	sd	NA
5	as	cat

How do I get those count columns instead?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

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

戏舞 2025-02-20 04:47:01

您正在“旋转”加入的结果。您可以使用有条件的聚合来生成这些额外的列。

例如：

select
  a.id,
  max(a.value) as val,
  count(*) as count_pets,
  sum(case when b.pet = 'cat' then 1 else 0 end) as cat,
  sum(case when b.pet = 'dog' then 1 else 0 end) as dog,
  sum(case when b.pet = 'fish' then 1 else 0 end) as fish
from table1 a
left join table2 b on b.id = a.id
group by a.id

You are "pivoting" the result of the join. You can use conditional aggregation to produce those extra columns.

For example:

select
  a.id,
  max(a.value) as val,
  count(*) as count_pets,
  sum(case when b.pet = 'cat' then 1 else 0 end) as cat,
  sum(case when b.pet = 'dog' then 1 else 0 end) as dog,
  sum(case when b.pet = 'fish' then 1 else 0 end) as fish
from table1 a
left join table2 b on b.id = a.id
group by a.id

回复收藏 0 原文

~没有更多了~