带有GROUP BY id的T-SQL SELECT

I have table with 'People'

我有'人'的桌子

Columns

列

ID
ID
Name
名称
Age
年龄

And table with 'Notes':

和表''注释'：

Columns

列

ID
ID
Text
文本
FK_Author
FK_Author

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.

我想从人员表及其姓名和年龄中选择所有作者的笔记数，但我想用PERSON 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.

当我选择数据时，如果我想选择此列，我也必须按名称分组，因为如果我不这样做，我会收到错误。

4 个解决方案

#1

Since you want to get all records from table People, you need to join it with Notes by using LEFT JOIN so any user without any record on Notes will be included in the list with thev value of totalCount with zero.

由于您希望从表People中获取所有记录，因此您需要使用LEFT JOIN将其与Notes连接，以便任何没有Notes记录的用户都将包含在列表中，其值为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

SQLFiddle Demo
SQLFiddle演示

To further gain more knowledge about joins, kindly visit the link below:

要进一步了解联接，请访问以下链接：

Visual Representation of SQL Joins
SQL连接的可视化表示

OUTPUT

╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME  ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║  1 ║ John  ║  12 ║          2 ║
║  2 ║ Annie ║  29 ║          2 ║
║  3 ║ John  ║  44 ║          1 ║
╚════╩═══════╩═════╩════════════╝

4 个解决方案

#1

更多相关文章

随机推荐