写好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 date='20210724' and get_json_object(details,"$.TaskType") =2

 

3. 书写顺序和执行顺序

书写：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  【有用】

select type, concat_ws('&',sort_array(collect_list(struct(id,name)),false).name) as c from test group by type

https://www.cnblogs.com/qi-yuan-008/p/13583959.html

6. 高级函数如map，str_to_map

str_to_map(concat_ws(',',collect_list(concat(gift_id,":", gift_consume_diamond_30d)))) as user_gift_consume_30d

 

7. 取分组的前n个：

https://www.cnblogs.com/wangpei/p/6600584.html

（这里把1改成n-1就可以了）

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

 

8.  sql只根据某一字段去重，并保留其他字段

 

select id,name,age from user a where id in ( select max(id) as id from user b group by age )

 

（max改成distinct也可以）