1.group by与where
一,group by 字句也和where条件语句结合在一起使用。当结合在一起时,where在前,group by 在后。
即先对select xx from xx的记录集合用where进行筛选,然后再使用group by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选。
二,需要注意having和where的用法区别:
1.having只能用在group by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。
2.where肯定在group by 之前。
3.where后的条件表达式里不允许使用聚合函数,而having可以。
三,当一个查询语句同时出现了where,group by,having,order by的时候,执行顺序和编写顺序是:
1.执行where xx对全表数据做筛选,返回第1个结果集。
2.针对第1个结果集使用group by分组,返回第2个结果集。
3.针对第2个结果集中的每1组数据执行select xx,有几组就执行几次,返回第3个结果集。
4.针对第3个结集执行having xx进行筛选,返回第4个结果集。
5.针对第4个结果集排序。
2.partition by用法
1)partition by:partition by与group by不同之处在于前者返回的是分组里的每一条数据,并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。
2)rank() over(partition by A order by B)与dense_rank() over(partition by A order by B)区别:
rank()
是按照A进行分组,分组里面的数据按照B进行排序,over即在什么之上,rank()即跳跃排序(比如存在两个第一名,接下来就是第三名)
dense_rank()
连续排序(如果有两个第一名时,接下来仍然是第二名)
3)row_number() over(partition by A order by B):
row_number(): 如果有两个第一名时,只返回一个结果。
参考:https://blog.csdn.net/locken123/article/details/127411319
3.ORDER BY 2 DESC,1 ASC:先按照选择列里的第二列进行降序排序,再按选择列的第一列降序排序
4.case when then else end
Case when 语句能在sql语句中植入语句判断的逻辑,类似于if else语句
1 case 字段 when 什么时候 then 结果 else 结果2 end
eg:以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT SC.CId,Course.Cname,MAX(SC.score) 最大值,MIN(SC.score) 最小值,AVG(SC.score) 平均数,
SUM(CASE WHEN SC.score>=60 then 1 else 0 END)*1.0/COUNT(SC.score) 及格率,
SUM(CASE WHEN SC.score BETWEEN 70 AND 80 then 1 else 0 END)*1.0/COUNT(SC.score) 中等率,
SUM(CASE WHEN SC.score BETWEEN 80 AND 90 then 1 else 0 END)*1.0/COUNT(SC.score) 优良率,
SUM(CASE WHEN SC.score>=90 then 1 else 0 END)*1.0/COUNT(SC.score) 优秀率
FROM SC,Course
WHERE SC.CId=Course.CId
GROUP BY SC.CId,Course.Cname
5.distinct:去重
6.DATEDIFF( datepart , startdate , enddate ):计算时间差
datepare值:year | quarter | month | week | day | hour | minute | second | millisecond
startdate:开始日期
enddate :结束日期
GetDate():获取当前的系统日期(类似于mysql中Current_timestamp())
参考:https://blog.csdn.net/Candy_Sir/article/details/85231400
7.WEEKOFYEAR( date):
参数:此方法仅接受一个参数。
date -我们要从中提取周数的日期或日期时间。
返回值:它返回星期数。
8.MONTH(data):同上返回月份函数
9.limit 仅可用于没有重复数值时(eg:37,38题对比)
学习链接:https://blog.csdn.net/wbc199685/article/details/108605284?spm=1001.2014.3001.5501
记录题号:9.11.15,21,27 38 39