有关查询序列问题
?
WITH T AS
?? ?(
?? ?SELECT 'C1' COL1 ,'A1' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C2' COL1 ,'B1' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C2' COL1 ,'B2' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C2' COL1 ,'B3' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C1' COL1 ,'A2' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C1' COL1 ,'A3' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C2' COL1 ,'B4' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C1' COL1 ,'A4' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C1' COL1 ,'A5' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C1' COL1 ,'A6' COL2 FROM DUAL UNION ALL
?? ?SELECT 'C2' COL1 ,'B5' COL2 FROM DUAL
?? ?)
?
?
-- Oracel
?
?ex1:
SET @ROWNUM:=0;SELECT MAX(CASE MOD(CEIL(ROWNUM / CEIL(CNT / 2)) - 1,2) + 1 WHEN 1 THEN COL2 END) C1, MAX(CASE MOD(CEIL(ROWNUM / CEIL(CNT / 2)) - 1,2) + 1 WHEN 2 THEN COL2 END) C2FROM (SELECT @ROWNUM:=@ROWNUM + 1 AS ROWNUM, COL1, COL2, (SELECT count(*) FROM T) cnt FROM T ORDER BY col1) T1 GROUP BY MOD (ROWNUM - 1,CEIL(CNT/2)) + 1 + CEIL(ROWNUM / (SELECT count(*) FROM T));?
?
?
?
?
