求反GROUP BY的写法

guostong

我也来一段：

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;

lastwinner

神作，精了！

dingjun123

方法基本全了，支持射精

gyhgood

非常有用～！MARK

jboracle1981

mark
兔子的方法学不会

〇〇

guostong 发表于 2011-10-27 03:04
我也来一段：

WITH data AS (

递归cte阿

〇〇

性能测试

newkid 2#
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
SELECT name
  FROM data
CONNECT BY name=PRIOR name AND LEVEL<=cnt AND PRIOR SYS_GUID() IS NOT NULL
);

         S
----------
    580000

Elapsed: 00:00:00.95

〇〇

hmxxyy
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
select a.name from data a,
(
select rownum rn from (select max(cnt) maxlevel from data) a connect by 1=1 and level <=maxlevel) b
where a.cnt >= rn
);

Elapsed: 00:00:04.82

〇〇

newkid #7
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
SELECT name
  FROM data
      ,TABLE( CAST( MULTISET( SELECT 1 FROM DUAL CONNECT BY LEVEL <= data.cnt ) AS SYS.ODCINUMBERLIST))
) ;

Elapsed: 00:00:00.43

〇〇

dingjun
WITH data AS (
SELECT  'A' name, 2e5 cnt FROM DUAL
UNION ALL SELECT 'BB', 4e4 FROM DUAL
UNION ALL SELECT 'CCC', 1e5 FROM DUAL
)
select sum(length(name))s from(
SELECT NAME, x.num AS num
FROM data,
        xmltable('1 to xs:integer($n)' passing cnt AS "n" columns num FOR
                  ordinality) x
);
Elapsed: 00:00:00.75