mysql - 减少 MySQL 代码或合并 SELECT 语句

Question

我已经与银行数据库系统建立了一些关系。这是我当前的代码。该表有

SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000001;
SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000002;
SELECT COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
and SortCode = 00000003;

代码的其余部分是这部分的重复，具有下一个“帐户”类型，并再次循环返回排序代码的 1-3。

我想知道是否有更优雅的方式来制作这个。我需要计算每家银行的学生账户、活期账户和储蓄账户的数量。

或者有没有办法将大量选择组合在一起以制作一个整洁的表格？

Answer 1

这就是GROUP BY 的用途!

SELECT SortCode,COUNT(AccountType) AS Student_Total FROM Account
WHERE AccountType ='Student' 
GROUP BY SortCode;

更新:

您还可以使用多个分组字段进行GROUP BY:

SELECT SortCode,AccountType,COUNT(AccountType) AS Student_Total FROM Account
GROUP BY SortCode,AccountType;