自己工作中,sql中的的聚合函数和group by 经常用,having用的比较少,所以经常忘记用法,在此做个备忘。
group by 可以理解为,根据一个或多个列对数据进行分组,如
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
having可以理解为对分组后的数据进行筛选,如
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
如果再加上where和order by 如
SELECT Customer,SUM(OrderPrice) FROM Orders where name like'liu%' GROUP BY Customer HAVING SUM(OrderPrice)<2000 order by age执行过程为:
1.执行where筛选数据;
2.执行group by 对数据进行分组;
3.执行having 对分组后的数据进行过滤;
4.执行 order by 对结果排序;
一个工作中的例子:
select * from SimpleListingSetting
where ListingSKU in
(select ListingSKU from SimpleListingSetting WHERE PlatformName='Amazon' group by ListingSKU,StoreId having count(ListingSKU) >1 )