//数据的分组与聚合
GROUP BY 用于根据前面的的参数集分组结果
SELECT _State
FORM MemberDetails
GROUP BY _State;
SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State;
//可以基于多列分组
SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State,City;
//数据汇总聚合函数
COUNT() //用于统计结果中记录的数目,统计非NULL值,可以接受表达式作为参数,可以插入通配符*
SELECT COUNT(*)
FROM MemberDetails;
SELECT COUNT(City),COUNT(LastName)
FROM MemberDetails;
//根据SQL规则,不允许组合聚合函数和非聚合的列
SELECT City,COUNT(MemberId)
FROM MemberDetails;
//这样是不行的,因为City可能返回多行数据,而COUNT仅返回一行
SELECT _State,COUNT(LastName)
FROM MemberDetails
GROUP BY _State;
//这样就可以了
//Group by 实质上将结果分为多个组,每个组都是所有记录的一个子集
SUM() //用于累加结果
//自动忽略NULL值
AVG() //用于求结果平均值
MAX() //求最大值
MIN() //求最小值
//HAVING子句用于过滤最终结果中的分组.HAVING子句类似于一个WHERE子句,它的位置紧紧跟在GROUP BY子句之后
SELECT Category,COUNT(FavCategory.CategoryId) AS Popularity
FROM FavCategory INNER JOIN Category
ON FavCategory.CategoryId = Category.CategoryId
GROUP BY Category.Category
HAVING COUNT(FavCategory.CategoryId) > 3
ORDER BY Popularity DESC;
GROUP BY 用于根据前面的的参数集分组结果
SELECT _State
FORM MemberDetails
GROUP BY _State;
SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State;
//可以基于多列分组
SELECT _State
FROM MemberDetails
WHERE _State IN ('Mega state','Golden State','new State')
GROUP BY _State,City;
//数据汇总聚合函数
COUNT() //用于统计结果中记录的数目,统计非NULL值,可以接受表达式作为参数,可以插入通配符*
SELECT COUNT(*)
FROM MemberDetails;
SELECT COUNT(City),COUNT(LastName)
FROM MemberDetails;
//根据SQL规则,不允许组合聚合函数和非聚合的列
SELECT City,COUNT(MemberId)
FROM MemberDetails;
//这样是不行的,因为City可能返回多行数据,而COUNT仅返回一行
SELECT _State,COUNT(LastName)
FROM MemberDetails
GROUP BY _State;
//这样就可以了
//Group by 实质上将结果分为多个组,每个组都是所有记录的一个子集
SUM() //用于累加结果
//自动忽略NULL值
AVG() //用于求结果平均值
MAX() //求最大值
MIN() //求最小值
//HAVING子句用于过滤最终结果中的分组.HAVING子句类似于一个WHERE子句,它的位置紧紧跟在GROUP BY子句之后
SELECT Category,COUNT(FavCategory.CategoryId) AS Popularity
FROM FavCategory INNER JOIN Category
ON FavCategory.CategoryId = Category.CategoryId
GROUP BY Category.Category
HAVING COUNT(FavCategory.CategoryId) > 3
ORDER BY Popularity DESC;