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