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