可能我问题没说的太清楚,是这样的:假如返回结果有两行数据, 第一行:id weight date site (这些都是字段名,不是具体值,第二行也一样) 第二行:id weight date site 我想要的效果是 1.weight 1.date 1.site 2.weight 2.date 2.site (两行中的某些字段合为一行),我想问下这样的效果用一句sql语句能实现吗?
with test as ( select 'A001' AS CH,'20' AS weight,'10' AS dateA,'A3' AS site FROM DUAL UNION ALL select 'A001' AS CH,'30' AS weight,'20' AS dateA,'B3' AS site FROM DUAL UNION ALL select 'A002' AS CH,'45' AS weight,'30' AS dateA,'C3' AS site FROM DUAL UNION ALL select 'A002' AS CH,'55' AS weight,'40' AS dateA,'D3' AS site FROM DUAL UNION ALL select 'A003' AS CH,'60' AS weight,'50' AS dateA,'E3' AS site FROM DUAL ) SELECT MAX(DECODE(RN, 1, weight, '')) AS weight1, MAX(DECODE(RN, 1, dateA, '')) AS dateA1, MAX(DECODE(RN, 1, site, '')) AS site1, MAX(DECODE(RN, 2, weight, '')) AS weight2, MAX(DECODE(RN, 2, dateA, '')) AS dateA2, MAX(DECODE(RN, 2, site, '')) AS site2 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CH ORDER BY dateA) AS RN, TEST.* FROM TEST) GROUP BY CH=============================== 1 20 10 A3 30 20 B3 2 45 30 C3 55 40 D3 3 60 50 E3
第一行:id weight date site (这些都是字段名,不是具体值,第二行也一样)
第二行:id weight date site
我想要的效果是 1.weight 1.date 1.site 2.weight 2.date 2.site (两行中的某些字段合为一行),我想问下这样的效果用一句sql语句能实现吗?
select 'A001' AS CH,'20' AS weight,'10' AS dateA,'A3' AS site FROM DUAL
UNION ALL
select 'A001' AS CH,'30' AS weight,'20' AS dateA,'B3' AS site FROM DUAL
UNION ALL
select 'A002' AS CH,'45' AS weight,'30' AS dateA,'C3' AS site FROM DUAL
UNION ALL
select 'A002' AS CH,'55' AS weight,'40' AS dateA,'D3' AS site FROM DUAL
UNION ALL
select 'A003' AS CH,'60' AS weight,'50' AS dateA,'E3' AS site FROM DUAL
)
SELECT MAX(DECODE(RN, 1, weight, '')) AS weight1,
MAX(DECODE(RN, 1, dateA, '')) AS dateA1,
MAX(DECODE(RN, 1, site, '')) AS site1,
MAX(DECODE(RN, 2, weight, '')) AS weight2,
MAX(DECODE(RN, 2, dateA, '')) AS dateA2,
MAX(DECODE(RN, 2, site, '')) AS site2
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CH ORDER BY dateA) AS RN,
TEST.*
FROM TEST)
GROUP BY CH===============================
1 20 10 A3 30 20 B3
2 45 30 C3 55 40 D3
3 60 50 E3