SQL 查询需要在 count(id) = 2 处获取名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1429930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query need to get names where count(id) = 2
提问by mattgcon
I have a table programparticipants. I am currently successfully querying the IDs where count(name) > 1. What I need now is to query the names that belong to those IDs where count(name) > 1.
我有一张桌子programparticipants。我目前正在成功查询 ID 在哪里count(name) > 1。我现在需要的是查询属于那些 ID 的名称 where count(name) > 1.
Example, data result currently being returned:
例如，当前返回的数据结果：
ID count(name)
1 2
3 4
4 3
Example, data result needed:
示例，需要的数据结果：
ID name
1 nm1
1 nm3
3 nm2
3 nm3
3 nm4
3 nm7
4 nm5
4 nm8
4 nm9
采纳答案by Cem Kalyoncu
You may use this:
你可以使用这个：
SELECT
(SELECT name FROM participants WHERE id=p.participantid) AS name
FROM
programparticipants AS p
WHERE
.... (the part where you find count(name)>1)
回答by kragan
select count(id), name
from programparticipants
group by name
having count(id) > 1
回答by duffymo
I think GROUP BY and HAVINGare what you want.
我认为GROUP BY 和 HAVING是你想要的。
回答by Joel Coehoorn
select id, Name
from programparticipants
where id in ( <your current query selecting only id here> )
回答by Omar Qureshi
Think you'll want to look at group by with having:
认为您会希望通过以下方式查看组：
select id, name, count(name) from table group by 2,1 having count(name) = 2;
You can substitute = 2 for > 1 depending on whatever you want (title says = 2, question says > 1)
您可以根据需要将 = 2 替换为 > 1（标题说 = 2，问题说 > 1）
回答by Steve Weet
SELECT id, Name
FROM programparticipants
GROUP BY id, Name
HAVING COUNT(*) > 1
