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

1、在Navicat Premium中测试

表竖形结构图 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 结果截图

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 o3iaqi from 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 后面跟的字段所对应