PostgreSQL_行转列（filter用法）

行列转换，不是很统一，有的地方叫转置，有的地方叫透视

停车表中车位共有四种状态，0（未绑定区域）、1（绑定区域、也代表空闲）、2（有车）、3（异常）

id	street	status	count
41010503	A	1	3
41017206	B	1	1
41017206	B	2	2
41017206	B	3	1
41017240	C	0	0
41017240	C	1	2
41017240	C	2	2
41017240	C	3	1

前端展示时候需要展示出总车位（0、1、2、3）、在用（2、3）、空余（0、1）

对应的表格为status0(空闲)、status1(在用)、status(总车位)

id	street	status0	status1	status
41010503	A	1	0	0
41017206	B	1	3	4
41017240	C	2	3	5

实现过程

传统的case when语法。在PostgreSQL中，还提供了一个filter语法（简化case when）

SELECT T11.id AS id,street,status0,status1,T11.status,
       T22.cameraid,T22.name,T22.url,T22.location,
       CASE  WHEN status1=T11.status AND status0!=0        THEN '2'
             WHEN status0< status1                         THEN '1'
             ELSE  '0'
       END AS flag
  FROM(
     SELECT  id,street,
     COALESCE(SUM(count) FILTER (WHERE status=0 OR status=1            ),0) AS "status0",
     COALESCE(SUM(count) FILTER (WHERE             status=2 OR status=3),0) AS "status1", 
     COALESCE(SUM(count) FILTER (WHERE 1=1                             ),0) AS "status" 
     FROM (SELECT T1.id,T1.name AS street,T2.status,COUNT(*)         
            FROM(SELECT *  FROM "street" )T1
       LEFT JOIN(SELECT streetid,status FROM "parkingplace" )T2 ON T1.id = T2.streetid
         GROUP BY T1.id,T1.name,T2.status 
           )T  GROUP BY id,street
       )T11
LEFT JOIN (SELECT T2.streetid,T3.location,T3.url,T3.cameraid,T3.name
             FROM (SELECT *  FROM "parkingplace" )T2
        LEFT JOIN (SELECT parkingid,T22.location,T22.url,T22.id AS cameraid,T22.name
                     FROM "cameraarea" T11
                LEFT JOIN "camera" T22 ON T11.cameraid = T22.id
                   )T3 ON T2.id=T3.parkingid WHERE T3.location <> ''                             GROUP BY T2.streetid,T3.location,T3.url,T3.cameraid,T3.name 
          )T22 ON T11.id=T22.streetid