今天基本完成了项目的模块的基本功能,剩下的就是对项目的吃透,对各种工具类的掌握,其中sql语句的书写就显得很重要了,特别是新的任务就是 写一个统计板块,sql语句基本从网上各大扒,基本已经成型,上没有完成的是对sql语句运行完之后得到的数据的封装并且转发给前端
现总结今天的SQL函数
select sum(case when sys_user.uName ='超级管理员' then 1 else 0 end) ,
sum(case when biz_order.uId = '21' then 1 else 0 end) ,
sum(case when biz_order.uId = 'QiuZhiLinCeShi' then 1 else 0 end)
from biz_order
LEFT JOIN sys_user ON sys_user.uId = biz_order.uId
WHERE year(biz_order.oAcceptanceDate)BETWEEN '2017' AND '2018';
group by biz_order.uId
这是一个列转行的方式
select
sum(case month(biz_order.oAcceptanceDate) when '1' then 1 else 0 end) as January,
sum(case month(biz_order.oAcceptanceDate) when '2' then 1 else 0 end) as February,
sum(case month(biz_order.oAcceptanceDate) when '3' then 1 else 0 end) as March,
sum(case month(biz_order.oAcceptanceDate) when '4' then 1 else 0 end) as April,
sum(case month(biz_order.oAcceptanceDate) when '5' then 1 else 0 end) as May,
sum(case month(biz_order.oAcceptanceDate) when '6' then 1 else 0 end) as June,
sum(case month(biz_order.oAcceptanceDate) when '7' then 1 else 0 end) as July,
sum(case month(biz_order.oAcceptanceDate) when '8' then 1 else 0 end) as August,
sum(case month(biz_order.oAcceptanceDate) when '9' then 1 else 0 end) as September,
sum(case month(biz_order.oAcceptanceDate) when '10' then 1 else 0 end) as October,
sum(case month(biz_order.oAcceptanceDate) when '11' then 1 else 0 end) as November,
sum(case month(biz_order.oAcceptanceDate) when '12' then 1 else 0 end) as December
from biz_order
where year(biz_order.oAcceptanceDate)='2018';
以上就是我的项目涉及到的行转列的地方