求反GROUP BY

转自 http://www.itpub.net/thread-1505196-1-1.html

比如有一张表TEST，GROUP BY后的数据如下：

select name ，count(*) as CNT from TEST

NAME   CNT
A         2
B         4
C         1
..        ..
CNT为出现的次数，现在要反过来写，需要得出数据
A
A
B
B
B
B
C

1.Connect BY  with Level.

WITH data AS (
SELECT  'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT name
  FROM data 
CONNECT BY name=PRIOR name AND LEVEL<=cnt AND PRIOR SYS_GUID() IS NOT NULL;

WITH data AS (
SELECT 'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT name,LEVEL,SYS_GUID(),SYS_GUID()
FROM data
CONNECT BY name=PRIOR name AND LEVEL<=cnt AND PRIOR dbms_random.value IS NOT NULL;

 

下面两行的作用是为了欺骗Oracle，让它认为前后两行的数据是不一样的，从而避免ORA-01436.

AND PRIOR SYS_GUID() IS NOT NULL;

AND PRIOR dbms_random.value IS NOT NULL;

 

Connect By是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。

在这里我们的数据并无父子关系，只是要让同一行数据重复出现，因此我们的连接的条件只用到了表的主键id=PRIOR id,

此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，

但是同一行数据再次出现，它就会报一个错：
ERROR:
ORA-01436: CONNECT BY loop in user data

为了欺骗它，这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。

 

疑问：
1.非要prior SYS_GUID而不是直接SYS_GUID

【一定要加上PRIOR，ORACLE才会比较前后两行；不加的话只是当前行。】

2.为何非要IS NOT NULL ，我试了下，写<>1之类其实也是可以的

【SYS_GUID()可能返回非数字，这时<>1就会报错了。】

 

2. XML TABLE

 WITH data AS (
    SELECT  'A' name, 2 cnt FROM DUAL
    UNION ALL SELECT 'B', 4 FROM DUAL
    UNION ALL SELECT 'C', 1 FROM DUAL
    )
 SELECT NAME, x.num AS num
      FROM data,xmltable('1 to xs:integer($n)' passing cnt AS "n" columns num FOR ordinality) x
 ORDER BY 1, 2;

 

3. MODEL

WITH data AS (
SELECT  'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT name FROM data
MODEL
PARTITION BY (name)
DIMENSION BY (1 n)
MEASURES (cnt)
RULES 
ITERATE (100) UNTIL (ITERATION_NUMBER>=cnt[1]-1)
  (
     cnt[ITERATION_NUMBER+1]=cnt[1]
  )
ORDER BY 1;

4.CTE

WITH 
data AS (
  SELECT  'A' name, 2 cnt FROM DUAL
  UNION ALL SELECT 'B', 4 FROM DUAL
  UNION ALL SELECT 'C', 1 FROM DUAL
  ),
rdata (name, cnt, lv) as
  (
    select name, cnt, 1 as lv from data
    union all
    select name, cnt, lv + 1 as lv from rdata where lv + 1 <= cnt
  )
select * from rdata order by name, lv;

5. 笛卡尔积

WITH data AS (
SELECT  'A' name, 5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4 FROM DUAL
UNION ALL SELECT 'CCC', 3 FROM DUAL
)
select a.name from data a,
(select level rn from dual connect by level <=(select max(cnt) maxlevel from data)) b
where a.cnt >= rn

WITH data AS (
SELECT  'A' name, 5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4 FROM DUAL
UNION ALL SELECT 'CCC', 3 FROM DUAL
)
select a.name from data a,
(select rownum rn from dual connect by rownum <=(select max(cnt) maxlevel from data) ) b
where a.cnt >= rn

 

6. Table 构造一个相关记录表

WITH data AS (
SELECT  'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT name  FROM data,TABLE(CAST(MULTISET( SELECT 1 FROM DUAL CONNECT BY LEVEL <= data.cnt) AS SYS.ODCINUMBERLIST)) b ;

SQL> WITH data AS (
  2  SELECT  'A' name, 2 cnt FROM DUAL
  3  UNION ALL SELECT 'B', 4 FROM DUAL
  4  UNION ALL SELECT 'C', 1 FROM DUAL
  5  )
  6  SELECT name ,b.* FROM data,TABLE(CAST(MULTISET( SELECT 1 FROM DUAL CONNECT BY LEVEL <= data.cnt) AS SYS.ODCINUMBERLIST)) b ;

NA COLUMN_VALUE
-- ------------
A             1
A             1
B             1
B             1
B             1
B             1
C             1

来自http://www.itpub.net/thread-1399606-1-1.html

我的原始需求是这样的：

select * from test

3 aa aaa
4 bb bbb

生成如下结果：

1 aa aaa
2 aa aaa
3 aa aaa
1 bb bbb
2 bb bbb
3 bb bbb
4 bb bbb

分析:这个可以看做是一个多列的group by,求反的时候要包括level.

SQL> with t as (
2 select 3 id, 'a' val,'aaa' val2 from dual union all
3 select 4 id, 'b' val,'aaa' val2 from dual )
4 SELECT lv, val,val2
5 FROM t, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10)
6 WHERE id >= lv
7 ORDER BY val, lv;

LV VA VAL2
---------- -- ------
1 a aaa
2 a aaa
3 a aaa
1 b aaa
2 b aaa
3 b aaa
4 b aaa

 

create table test (cnt number,c1 varchar2(10),c2 varchar2(10));

insert into test values (3,'aa', 'aaa');
insert into test values (4,'bb', 'bbb');

SELECT level, t.c1,t.c2 FROM test t CONNECT BY id=PRIOR id AND LEVEL<=cnt AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

SELECT t2.column_value,t.c1,t.c2 
FROM test t,
TABLE(CAST(MULTISET(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= cnt) AS SYS.ODCINUMBERLIST )) t2;

 

with data as (
select 'A' a, 'PQR' b, 2 c from dual union all
select 'B', 'BNM', 3 from dual union all
select 'C', 'XYZ', 4 from dual)
select x.a,x.b,x.c from (
                    select a, b, c, sum(c)over(order by rownum)-rownum+1 psum from data
                ) x
    connect by rownum <= psum
    /