论坛 › DB2 › 检索行合并

doushi 发表于 2009-04-10 09:39

检索行合并

SELECT
USER_ID ,
CASE WHEN SYSTEM_CODE = '01' THEN '1' END AS sys1 ,
CASE WHEN SYSTEM_CODE = '02' THEN '1' END AS sys2 ,
CASE WHEN SYSTEM_CODE = '03' THEN '1' END AS sys3 ,
CASE WHEN SYSTEM_CODE = '04' THEN '1' END AS sys4 ,
FROM
T_SYS
WHERE
DELETE_FLG = '0'
AND CHANGE_TIMESTAMP >= '2009-04-1-00.00.00.000000'
GROUP BY USER_ID,SYSTEM_CODE

用上面的SQL检索的结果如下：
USER_ID    sys1 sys2sys3sys4
---------- --- ------ ----- ------
901236   1   -      -   -   
901236 -       1      -   -   
901236 -      -       -1    -   
901236 -       -      -   -1   

请问我怎么能把4行的901236合并为1行显示出来。
USER_ID    sys1 sys2sys3sys4
---------- --- ------ ----- ------
901236   1   1      1   1

Germin 发表于 2009-04-25 00:10

SELECT
USER_ID ,
CHAR(SUM(CASE WHEN SYSTEM_CODE = '01' THEN1 ELSE 0 END) )AS sys1 ,
CHAR(SUM(CASE WHEN SYSTEM_CODE = '02' THEN1 ELSE 0 END) )AS sys2 ,
CHAR(SUM(CASE WHEN SYSTEM_CODE = '03' THEN1 ELSE 0 END) )AS sys3 ,
CHAR(SUM(CASE WHEN SYSTEM_CODE = '04' THEN1 ELSE 0 END)) AS sys4 ,
FROM
T_SYS
WHERE
DELETE_FLG = '0'
AND CHANGE_TIMESTAMP >= '2009-04-1-00.00.00.000000'
GROUP BY USER_ID,SYSTEM_CODE

查看完整版本: 检索行合并