PGSql竖向数据转横向数据，适用于其他Sql

1、在Navicat Premium中测试

1. 表竖形结构图
   
2. sql语句

select m.monitor_time,  m.sta_code,max(case m.parameter_code when '34004' then m.value else -999 end) as pm25,max(case m.parameter_code when '34002' then m.value else -999 end) as pm10,max(case m.parameter_code when '21026' then m.value else -999 end) as so2,max(case m.parameter_code when '35012' then m.value else -999 end) as o3,max(case m.parameter_code when '21004' then m.value else -999 end) as no2,max(case m.parameter_code when '21005' then m.value else -999 end) as co
from data_air_station m  
where m.monitor_time between '2020-04-01 00:00:00' and '2020-04-10 00:00:00' and m.duration_code = '31'GROUP BY m.monitor_time , m.sta_code ORDER BY m.monitor_time asc

3. 结果截图
   
   

2、mybatis中的写法

<select id="selectDayData" resultType="java.util.Map" parameterType="com.clear.multiple.domain.station.CheckDataParam">select m.monitor_time, m.sta_code,t.sta_name ,t.area_name,q.area_code ,q.area_name sta,max(case m.parameter_code when '34004' then m.value else -999 end) as pm25,max(case m.parameter_code when '34002' then m.value else -999 end) as pm10,max(case m.parameter_code when '21026' then m.value else -999 end) as so2,max(case m.parameter_code when '35012' then m.value else -999 end) as o3,max(case m.parameter_code when '21004' then m.value else -999 end) as no2,max(case m.parameter_code when '21005' then m.value else -999 end) as co,max(case m.parameter_code when '35009' then m.value else -999 end) as aqi,max(case m.parameter_code when '35010' then m.value else -999 end) as pripoll,max(case m.parameter_code when '35001' then m.value else -999 end) as pm25iaqi,max(case m.parameter_code when '35002' then m.value else -999 end) as pm10iaqi,max(case m.parameter_code when '35003' then m.value else -999 end) as so2iaqi,max(case m.parameter_code when '35004' then m.value else -999 end) as no2iaqi,max(case m.parameter_code when '35005' then m.value else -999 end) as coiaqi,max(case m.parameter_code when '35008' then m.value else -999 end) as o3iaqifrom data_air_station m left join base_air_sta_info t on m.sta_code =  t.sta_code left join dic_air_area_info q on m.sta_code = q.area_code<where>1=1<if test="startDate!=null">and m.monitor_time &gt;= #{startDate}</if><if test="endDate!=null">and m.monitor_time &lt;= #{endDate}</if>and m.parameter_code in<foreach collection="parameterCodes" open="(" separator=","close=")" item="code">#{code}</foreach>and m.sta_code in<foreach collection="staCodes" open="(" separator="," close=")" item="stacode">#{stacode}</foreach>and m.duration_code = #{durationCode}</where>GROUP BY m.monitor_time, m.sta_code, t.sta_name,t.area_name,q.area_code,q.area_name</select>

注意：select后面跟着所查询的字段要和group by 后面跟的字段所对应

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！