-- 你这太长了,写了个简单的例子 WITH a AS (SELECT 1 c1, 'a' c2, 1 c3 FROM dual UNION ALL SELECT 1 c1, 'b' c2, 2 c3 FROM dual UNION ALL SELECT 2 c1, 'c' c2, 1 c3 FROM dual UNION ALL SELECT 2 c1, 'd' c2, 3 c3 FROM dual) SELECT c1, max(CASE WHEN rnum = 1 THEN c2 END ) c2_1, max(CASE WHEN rnum = 2 THEN c2 END ) c2_2, max(CASE WHEN rnum = 1 THEN c3 END ) c3_1, max(CASE WHEN rnum = 2 THEN c3 END ) c3_2 FROM ( SELECT a.*, row_number() OVER(PARTITION BY c1 ORDER BY c2) rnum FROM a ) a1 GROUP BY c1; C1 C2_1 C2_2 C3_1 C3_2 ---------- ---- ---- ---------- ---------- 1 a b 1 2 2 c d 1 3[/code]
这样,很难理解我的问题吗
SELECT R1. station_id,R1.device_id ,R1.box_type_name ,R1.box_id,R1.tick_num1,R2.tick_num1......
(SELECT * FROM (
SELECT T1.*,ROW_NUMBER()OVER(PATITION BY station_id , device_id , box_type_name ,box_id ) RN FROM T1) WHERE RN=1) R1 JOIN
SELECT * FROM (
SELECT T1.*,ROW_NUMBER()OVER(PATITION BY station_id , device_id , box_type_name ,box_id ) RN FROM T1) WHERE RN=2) R2
ON ......
SQL文件地址http://w698-1251478258.costj.myqcloud.com/%E5%A4%9A%E8%A1%8C%E5%90%88%E5%B9%B6%E9%87%8D%E5%A4%8D%E5%80%BC%E8%BD%AC%E5%88%97.sqlExcel文件地址http://w698-1251478258.costj.myqcloud.com/12.xlsx
-- 你这太长了,写了个简单的例子
WITH a AS
(SELECT 1 c1, 'a' c2, 1 c3 FROM dual
UNION ALL
SELECT 1 c1, 'b' c2, 2 c3 FROM dual
UNION ALL
SELECT 2 c1, 'c' c2, 1 c3 FROM dual
UNION ALL
SELECT 2 c1, 'd' c2, 3 c3 FROM dual)
SELECT
c1,
max(CASE WHEN rnum = 1 THEN c2 END ) c2_1,
max(CASE WHEN rnum = 2 THEN c2 END ) c2_2,
max(CASE WHEN rnum = 1 THEN c3 END ) c3_1,
max(CASE WHEN rnum = 2 THEN c3 END ) c3_2 FROM
(
SELECT a.*, row_number() OVER(PARTITION BY c1 ORDER BY c2) rnum FROM a
) a1
GROUP BY c1; C1 C2_1 C2_2 C3_1 C3_2
---------- ---- ---- ---------- ----------
1 a b 1 2
2 c d 1 3[/code]