有关查询序列有关问题

有关查询序列问题

?

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));

?

?

?

?