写好SQL(持续更新)
1. 分类统计
select 单位名称,count(case 项目类别 when '理工类' then 1 end) 理工类,
count(case 项目类别 when '社科类' then 1 end) 社科类,
count(case 项目性质 when '横向' then 1 end) 横向,
count(case 项目性质 when '纵向' then 1 end) 纵向,
count(case 项目性质 when '校外' then 1 end) 校外,
count(*) 总数
from Item_Info
groupo by 单位名称
SELECT year, SUM(CASE WHEN type=1 THEN value ELSE 0 END) as type1, SUM(CASE WHEN type=2 THEN value ELSE 0 END) as type2, SUM(CASE WHEN type=3 THEN value ELSE 0 END) as type3, FROM table_test GROUP BY year
2. 解析json和通配符用法
书写:select–from–where–group by–having–order by
执行: from–where–group by–having–select–order by
原文链接:https://blog.csdn.net/qq_25615395/article/details/78873925
4. concat_as加分隔符, collect_set合并一列
https://blog.csdn.net/weixin_37536446/article/details/80597480
5. 希望实现先排序,后group by
mysql中建议使用 group_concat : https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc
hiveSQL中没有这个,建议使用sort_array,参考:https://www.cnblogs.com/huaxiaoyao/p/14792918.html 【有用】
https://www.cnblogs.com/qi-yuan-008/p/13583959.html
6. 高级函数如map,str_to_map
select
*
from
StudentGrade t
where
(
select
count
(1)
from
StudentGrade
where
subid=t.subid
and
grade>t.grade)<=1
order
by
subId,grade
desc
select
m, n
from
(
select
row_number () over (partition
by
m
order
by
n
desc
) rn,
--以m分组,分组内以n倒序排列求每组中各自的序号
m, n
from
table
where
...
) w
where
w.rn <=10;序号小于10
order
by
m, n
desc