，这种sql如何优化啊生成报表格式数据

求助，这种sql怎么优化啊？生成报表格式数据？
    今天做查询统计的时候遇到了一个问题，就是要查出类似数据报表的数据。数据库里面查数据不是我需要的，需要自己组装数据，觉得麻烦。
例如：

    SELECTcount(*)shuliang,j.zhuangtaibh,a.zhuanlilx    FROMaj_ajxxb a left JOIN aj_ztls j ON a.wofangjh = j.wofangjh         AND j.zhuangtaibh <> ''         AND a.zhuanlilx IN(1, 2, 3)    GROUP BYj.zhuangtaibh,a.zhuanlilx

而我想要的专利类型1,2,3都在一行，这时就需要将列转行？
我的方法是：

SELECT temp_xx.zhuangtaibh 状态编号, CASE WHEN temp_xx.zhuanlilx = '1' THEN shuliang ELSE '' END AS '发明', CASE WHEN temp_xx.zhuanlilx = '2' THEN shuliang ELSE '' END AS '新型', CASE WHEN temp_xx.zhuanlilx = '3' THEN shuliang ELSE '' END AS '外观' FROM  (    SELECTcount(*)shuliang,j.zhuangtaibh,a.zhuanlilx    FROMaj_ajxxb aJOIN aj_ztls j ON a.wofangjh = j.wofangjhAND j.zhuangtaibh <> ''AND a.zhuanlilx IN(1, 2, 3)GROUP BY    j.zhuangtaibh,             a.zhuanlilx   )AS temp_xx

成为这样的数据，还是不符合要求
想到group_concat()函数分组能够和合并行数据，正好满足我的要求：

SELECTt1.zhuangtaibh,group_concat(faming SEPARATOR ' ')AS 发明,group_concat(xinxing SEPARATOR ' ')AS 新型,group_concat(waiguan SEPARATOR ' ')AS 外观FROM(     SELECT        temp_xx.zhuangtaibh,CASE WHEN temp_xx.zhuanlilx = '1' THEN shuliang ELSE ''END AS 'faming',CASE WHEN temp_xx.zhuanlilx = '2' THEN shuliang ELSE ''END AS 'xinxing',CASE WHEN temp_xx.zhuanlilx = '3' THEN shuliang ELSE ''END AS 'waiguan'     FROM(       SELECTcount(*)shuliang,j.zhuangtaibh,a.zhuanlilxFROM  aj_ajxxb a    JOIN aj_ztls j ON a.wofangjh = j.wofangjh     AND j.zhuangtaibh <> ''              AND a.zhuanlilx IN(1, 2, 3)     GROUP BY     j.zhuangtaibh,     a.zhuanlilx)AS temp_xx)AS t1GROUP BYt1.zhuangtaibh

这样基本我的要求，可是怎么优化Sql？？？请大神帮忙想想。

查看更多 下一篇