带有 GROUP BY id 的 T-SQL SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15785877/
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
T-SQL SELECT with GROUP BY id
提问by Ellbar
I have table with 'People'
我和“人”有桌子
Columns
列
- ID
- Name
- Age
- ID
- 姓名
- 年龄
And table with 'Notes':
和带有“注释”的表：
Columns
列
- ID
- Text
- FK_Author
- ID
- 文本
- FK_作者
I want to select count of notes for all authors from people table and their name and age, but I want to group it by PERSON ID, not name. There are many situations when people have same name, but ID is obviously always different.
我想从 people 表中选择所有作者的笔记数以及他们的姓名和年龄，但我想按个人 ID 分组，而不是姓名。人名相同的情况有很多，但ID显然总是不同的。
EXAMPLE (input)
示例（输入）
PEOPLE:
人们：
╔════╦═══════╦═════╗
║ ID ║ NAME ║ AGE ║
╠════╬═══════╬═════╣
║ 1 ║ John ║ 12 ║
║ 2 ║ Annie ║ 29 ║
║ 3 ║ John ║ 44 ║
╚════╩═══════╩═════╝
NOTES:
笔记：
╔════╦═══════╦═══════════╗
║ ID ║ TEXT ║ FK_AUTHOR ║
╠════╬═══════╬═══════════╣
║ 1 ║ 'aaa' ║ 1 ║
║ 2 ║ 'aaa' ║ 1 ║
║ 3 ║ 'aaa' ║ 2 ║
║ 4 ║ 'aaa' ║ 2 ║
║ 5 ║ 'aaa' ║ 3 ║
╚════╩═══════╩═══════════╝
Expected result:
预期结果：
╔═══════╦═════╦════════════╗
║ NAME ║ AGE ║ TOTALCOUNT ║
╠═══════╬═════╬════════════╣
║ John ║ 12 ║ 2 ║
║ Annie ║ 29 ║ 2 ║
║ John ║ 44 ║ 1 ║
╚═══════╩═════╩════════════╝
When I select data I have to group by Name too if I want to select this column because if I dont, I get error.
当我选择数据时，如果我想选择此列，我也必须按名称分组，因为如果我不选择，则会出错。
回答by John Woo
Since you want to get all records from table People, you need to join it with Notesby using LEFT JOINso any user without any record on Noteswill be included in the list with thev value of totalCountwith zero.
由于您想从 table 获取所有记录People，因此您需要Notes使用 using将其连接起来，LEFT JOIN因此任何没有任何记录的用户Notes都将包含在列表中，其 v 值totalCount为零。
SELECT a.ID, a.Name, a.Age,
COUNT(b.FK_Author) totalCount
FROM People a
LEFT JOIN Notes b
ON a.ID = b.FK_Author
GROUP BY a.ID, a.Name, a.Age
To further gain more knowledge about joins, kindly visit the link below:
要进一步了解有关联接的更多信息，请访问以下链接：
OUTPUT
输出
╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║ 1 ║ John ║ 12 ║ 2 ║
║ 2 ║ Annie ║ 29 ║ 2 ║
║ 3 ║ John ║ 44 ║ 1 ║
╚════╩═══════╩═════╩════════════╝
回答by Werner Waage
SELECT P.ID,P.Name,P.Age,COUNT(N.ID)
FROM People P
INNER JOIN Notes N ON N.FK_Author = P.ID
GROUP BY P.ID,P.Name,P.age
回答by pratik garg
you can use subquery also to complete this task as-
您也可以使用子查询来完成此任务 -
select people.Name,people.Age,(select count(notes.id)
from notes
where notes.FK_Author= people.id)
from people;
回答by Kaf
You could also get the count from the Notes table first and then left join with the People table like below.
您也可以先从 Notes 表中获取计数，然后像下面这样与 People 表保持连接。
Fiddle-demo(thanks to J W for the fiddle)
小提琴演示（感谢 JW 的小提琴）
select name, age, Notate_Count
from People p left join (
select fk_author, count(*) Notate_Count
from Notes
group by fk_author ) x
on p.Id = x.fk_author
order by name --Ordering by name here, change as required
| NAME | AGE | NOTATE_COUNT |
------------------------------
| Annie | 29 | 2 |
| John | 44 | 1 |
| John | 12 | 2 |
