统计sql的动态列查询

最新推荐文章于 2022-04-14 01:42:27 发布

原创最新推荐文章于 2022-04-14 01:42:27 发布 · 880 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

数据库专栏收录该内容

9 篇文章

订阅专栏

通过foreach遍历生成列：

select top 100 percent
year(e.alarm_time) year,
month(e.alarm_time) month,

COUNT(CASE e.area_name WHEN ‘ $e.area_name ELSE null END) as '$ {i.get(“areaCode”)}’

from
(select c.area_name,d.alarm_time,d.camera_code from
(select a.area_code,a.area_name,b.camera_code from
(select distinct area_name,area_code from tb_zngk_znfx_area_info where area_name is not null and area_name != ‘’) a
left join
tb_zngk_znfx_camera_info b on a.area_code = b.area_code) c
left join
(select * from tb_zngk_znfx_realtime_alarm union select * from tb_zngk_znfx_realtime_alarm_history) d
on c.camera_code = d.camera_code where d.alarm_time is not null) e

and year(e.alarm_time) = #{year}

and month(e.alarm_time) = #{month}

group by year(e.alarm_time),month(e.alarm_time)
ORDER BY year(e.alarm_time),month(e.alarm_time)