34,576
社区成员
发帖
与我相关
我的任务
分享
/* 测试数据
WITH table1(column1,column3,column4) AS (
SELECT 1,10,null UNION ALL
SELECT 2,10,6 UNION ALL
SELECT 3,20,8
) */
SELECT *
FROM (
SELECT column1, column3, 0 column4
FROM table1
WHERE column3 IS NOT NULL
UNION ALL
SELECT column1, 0 column3, column4
FROM table1
WHERE column4 IS NOT NULL
) t
ORDER BY column1, column3 DESC
column1 column3 column4
----------- ----------- -----------
1 10 0
2 10 0
2 0 6
3 20 0
3 0 8
select distinct id,c1,c2 from (
SELECT id
,CASE WHEN c1 IS null or c2 IS NULL THEN c1
WHEN RN=1 THEN c1 ELSE 0 END c1
,CASE WHEN c1 IS NULL or c2 IS NULL THEN c2
WHEN RN=2 THEN c2 ELSE 0 END c2
FROM cc T1,(SELECT 1 RN UNION SELECT 2)T2
--ORDER BY T1.id,RN
) tt
改进了一下SELECT column1
,CASE WHEN column3 IS NULL OR column4 IS NULL THEN column3
WHEN RN=1 THEN column3 ELSE 0 END column3
,CASE WHEN column3 IS NULL OR column4 IS NULL THEN column4
WHEN RN=2 THEN column4 ELSE 0 END column4
FROM TB T1,(SELECT 1 RN UNION SELECT 2)T2
ORDER BY T1.column1,RN