SQL根据不同类型分类统计(类型在表中出现的次数)

最新推荐文章于 2024-06-12 16:00:55 发布

万德佛

最新推荐文章于 2024-06-12 16:00:55 发布

阅读量4.1k

点赞数 2

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.youkuaiyun.com/qq_39251440/article/details/108486254

版权

SQL 专栏收录该内容

1 篇文章

订阅专栏

//这里模拟的是根据人员分组统计，想了解每个人职员最近哪种工作做得比较多
//还可以根据时间范围查询，条件可以根据个人需求来修改
select us.u_name,
 sum(case when tb.t_name = '新增需求' then 1 else 0 end) as '新增需求',
 sum(case when tb.t_name = '数据维护' then 1 else 0 end) as '数据维护',
 sum(case when tb.t_name = '设备维护' then 1 else 0 end) as '设备维护',
 sum(case when tb.t_name = '日常工作' then 1 else 0 end) as '日常工作',
 sum(case when tb.t_name = '常用软件安装' then 1 else 0 end) as '常用软件安装',
 sum(case when tb.t_name = '网络维护' then 1 else 0 end) as '网络维护',
 sum(case when tb.t_name = '其他需求' then 1 else 0 end) as '其他需求',
 sum(case when tb.t_name = '系统维护' then 1 else 0 end) as '系统维护',
 sum(case when tb.t_name = '权限维护' then 1 else 0 end) as '权限维护',
 sum(case when tb.t_name = '程式维护' then 1 else 0 end) as '程式维护',
 sum(case when tb.t_name = '程序需求' then 1 else 0 end) as '程序需求'
from sys_user us 
left join type_body tb 
on us.id = tb.id 
GROUP BY us.u_name;

//不带参数就统计出现次数，不需要看是谁做了多少事，就想看每个类型总共出现的次数
select
 sum(case when tb.t_name = '新增需求' then 1 else 0 end) as '新增需求',
 sum(case when tb.t_name = '数据维护' then 1 else 0 end) as '数据维护',
 sum(case when tb.t_name = '设备维护' then 1 else 0 end) as '设备维护',
 sum(case when tb.t_name = '日常工作' then 1 else 0 end) as '日常工作',
 sum(case when tb.t_name = '常用软件安装' then 1 else 0 end) as '常用软件安装',
 sum(case when tb.t_name = '网络维护' then 1 else 0 end) as '网络维护',
 sum(case when tb.t_name = '其他需求' then 1 else 0 end) as '其他需求',
 sum(case when tb.t_name = '系统维护' then 1 else 0 end) as '系统维护',
 sum(case when tb.t_name = '权限维护' then 1 else 0 end) as '权限维护',
 sum(case when tb.t_name = '程式维护' then 1 else 0 end) as '程式维护',
 sum(case when tb.t_name = '程序需求' then 1 else 0 end) as '程序需求'
from type_body tb