2017.7.14 使用case when和group by将多条数据合并成一行,并且根据某些列的合并值做条件判断来生成最终值
参考来自:http://bbs.csdn.net/topics/390737006
1.效果演示
(1)不做处理
(2)合并多列,并对后四列的值做并集处理
2.SQL语句
(1)不做处理
1 SELECT 2 C .fd_tenantid AS fdTenantId, 3 C .fd_resid AS fdResid, 4 C .fd_res_name AS fdResName, 5 C .fd_service AS fdService, 6 b.fd_get AS fdGET, 7 b.fd_post AS fdPOST, 8 b.fd_put AS fdPUT, 9 b.fd_delete AS fdDELETE 10 FROM 11 t_usergroup AS A 12 INNER JOIN t_permission AS b ON A .fd_groupid = b.fd_groupid 13 INNER JOIN t_resource AS C ON b.fd_resid = C .fd_resid 14 INNER JOIN t_tenant AS d ON A .fd_tenantid = d.fd_tenantid 15 WHERE 16 d.fd_tenant_name = 'root' 17 and a.fd_groupid in (10026,10029) 18 19 ORDER BY c.fd_res_name
(2)合并多列,并对后四列的值做并集处理
当列fd.get的和>=1(至少有一个真),则返回1。
1 SELECT 2 C .fd_tenantid AS fdTenantId, 3 C .fd_resid AS fdResid, 4 C .fd_res_name AS fdResName, 5 C .fd_service AS fdService, 6 (CASE WHEN SUM(b.fd_get) >=1 THEN 1 ELSE 0 END) AS fdGET, 7 (CASE WHEN SUM(b.fd_post) >=1 THEN 1 ELSE 0 END) AS fdPOST, 8 (CASE WHEN SUM(b.fd_put) >=1 THEN 1 ELSE 0 END) AS fdPUT, 9 (CASE WHEN SUM(b.fd_delete) >=1 THEN 1 ELSE 0 END) AS fdDELETE 10 FROM 11 t_usergroup AS A 12 INNER JOIN t_permission AS b ON A .fd_groupid = b.fd_groupid 13 INNER JOIN t_resource AS C ON b.fd_resid = C .fd_resid 14 INNER JOIN t_tenant AS d ON A .fd_tenantid = d.fd_tenantid 15 WHERE 16 d.fd_tenant_name = 'root' 17 and a.fd_groupid in (10026,10029) 18 19 GROUP BY 20 d.fd_tenantid,c.fd_resid,c.fd_service
21 22 ORDER BY c.fd_res_name
fighting for this