写好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就可以了)
(取第一个,则是=0,取第2个则是=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 order by 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也可以)
posted @ 2021-08-31 20:11  qiezi_online  阅读(58)  评论(0编辑  收藏  举报