那个有条件的随机取电影问题用一句SQL实现

nyfor

原帖由 caizhuoyi 于 2008-7-3 16:45 发表
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> with tmp as
  2  (select rownum rn, sum(cnt) over(order by rownum) path_cnt, path, cnt
  3      from (select sys_connect_by_path(filmname, ',') path, level cnt
  4               from t_film
  5              where connect_by_isleaf = 1
  6              start with substr(filmname, -1) = 'a'
  7                      or instr('abc', substr(filmname, -1)) = 0
  8             connect by length(filmname) = length(prior filmname)
  9                    and instr('abc', substr(filmname, -1)) > 0
10                    and instr('abc', substr(prior filmname, -1)) > 0
11                    and substr(filmname, 1, length(filmname) - 1) =
12                        substr(prior filmname, 1, length(prior filmname) - 1)
13                    and filmname > prior filmname
14              order by dbms_random.value)
15    )
16  select substr(replace(a.path, '|') || b.path, 2) filnmaes
17    from (select path_cnt, sys_connect_by_path(path, '|') path
18             from tmp
19            where connect_by_isleaf = 1
20            start with rn = 1
21           connect by rn = prior rn + 1
22                  and path_cnt < 8) a, tmp b
23  where a.path_cnt + b.cnt = 8
24     and a.path_cnt < b.path_cnt
25     and rownum = 1;

未选定行

SQL> ed
已写入 file afiedt.buf

  1  with tmp as
  2  (select rownum rn, sum(cnt) over(order by rownum) path_cnt, path, cnt
  3      from (select sys_connect_by_path(filmname, ',') path, level cnt
  4               from t_film
  5              where connect_by_isleaf = 1
  6              start with substr(filmname, -1) = 'a'
  7                      or instr('abc', substr(filmname, -1)) = 0
  8             connect by length(filmname) = length(prior filmname)
  9                    and instr('abc', substr(filmname, -1)) > 0
10                    and instr('abc', substr(prior filmname, -1)) > 0
11                    and substr(filmname, 1, length(filmname) - 1) =
12                        substr(prior filmname, 1, length(prior filmname) - 1)
13                    and filmname > prior filmname
14              order by dbms_random.value)
15    )
16  select substr(replace(a.path, '|') || b.path, 2) filnmaes
17    from (select path_cnt, sys_connect_by_path(path, '|') path
18             from tmp
19            where connect_by_isleaf = 1
20      --      start with rn = 1
21           connect by rn = prior rn + 1
22                  and path_cnt < 8) a, tmp b
23  where a.path_cnt + b.cnt = 8
24     and a.path_cnt < b.path_cnt
25*    and rownum = 1
SQL> /

FILNMAES
--------------------------------------------------------------------------------
别拿自己不当干部,三分钟先生,彩票也疯狂,嬉戏江湖,侏罗纪公园i,冰河世纪2a,冰河世纪2
b,神枪手智多星


SQL>

奇怪, 难道是因为Oracle的优化器错误的改进了SQL的执行计划?

nyfor

修改一下, 麻烦 caizhuoyi  再试试:
with tmp as
(select sum(cnt) over(order by dbms_random.value) path_cnt, path, cnt
    from (select sys_connect_by_path(filmname, ',') path, level cnt
             from t_film
            where connect_by_isleaf = 1
            start with substr(filmname, -1) = 'a'
                    or instr('abc', substr(filmname, -1)) = 0
           connect by length(filmname) = length(prior filmname)
                  and instr('abc', substr(filmname, -1)) > 0
                  and instr('abc', substr(prior filmname, -1)) > 0
                  and substr(filmname, 1, length(filmname) - 1) =
                      substr(prior filmname, 1, length(prior filmname) - 1)
                  and filmname > prior filmname)
  )
select substr(replace(a.path, '|') || b.path, 2) filnmaes
  from (select path_cnt, sys_connect_by_path(path, '|') path
           from tmp
          where connect_by_isleaf = 1
          start with path_cnt = cnt
         connect by path_cnt = prior path_cnt + cnt
                and path_cnt < 8) a, tmp b
where a.path_cnt + b.cnt = 8
   and a.path_cnt < b.path_cnt
   and rownum = 1;

nyfor

三思没有看原始贴, 本题是要随机抽取8条电影记录, 同一部电影可能存在上下集情况, 这时电影记录就是多条.
要求取出的8条记录中对于存在上下集的电影如果要取则必须该电影集的全部记录全部提取到.
三思的代码我将 10 改为 8 后运行结果不正确, 出现了 太极a, 却没有出现 太极b

SQL> /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园ii
侏罗纪公园iii
东方海盗传奇a
太极张三丰b
太极a
太极张三丰a
东方海盗传奇b

8 rows selected

谁

nyfor
牛人，偶像

jvkojvko

学习了，其实像三思那样的语句自己也写得出来，可是不知道怎么取随机，呵呵，
原来可以这样用
order by dbms_random.Value()

学习到了，

caizhuoyi

还是同样的问题，不知怎搞的oracle？
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

Cocuments and SettingsCaiZhiguo>sqlplus czg/czg

SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 7月 3 19:06:08 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> with tmp as
  2  (select sum(cnt) over(order by dbms_random.value) path_cnt, path, cnt
  3      from (select sys_connect_by_path(filmname, ',') path, level cnt
  4               from t_film
  5              where connect_by_isleaf = 1
  6              start with substr(filmname, -1) = 'a'
  7                      or instr('abc', substr(filmname, -1)) = 0
  8             connect by length(filmname) = length(prior filmname)
  9                    and instr('abc', substr(filmname, -1)) > 0
10                    and instr('abc', substr(prior filmname, -1)) > 0
11                    and substr(filmname, 1, length(filmname) - 1) =
12                        substr(prior filmname, 1, length(prior filmname) - 1)
13                    and filmname > prior filmname)
14    )
15  select substr(replace(a.path, '|') || b.path, 2) filnmaes
16    from (select path_cnt, sys_connect_by_path(path, '|') path
17             from tmp
18            where connect_by_isleaf = 1
19            start with path_cnt = cnt
20           connect by path_cnt = prior path_cnt + cnt
21                  and path_cnt < 8) a, tmp b
22  where a.path_cnt + b.cnt = 8
23     and a.path_cnt < b.path_cnt
24     and rownum = 1;

未选定行

SQL>

jvkojvko

原帖由 caizhuoyi 于 2008-7-3 19:07 发表
还是同样的问题，不知怎搞的oracle？
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

Cocuments and SettingsCaiZhiguo>sqlplus czg/czg

SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 7月 3 19:06:08 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> with tmp as
  2  (select sum(cnt) over(order by dbms_random.value) path_cnt, path, cnt
  3      from (select sys_connect_by_path(filmname, ',') path, level cnt
  4               from t_film
  5              where connect_by_isleaf = 1
  6              start with substr(filmname, -1) = 'a'
  7                      or instr('abc', substr(filmname, -1)) = 0
  8             connect by length(filmname) = length(prior filmname)
  9                    and instr('abc', substr(filmname, -1)) > 0
10                    and instr('abc', substr(prior filmname, -1)) > 0
11                    and substr(filmname, 1, length(filmname) - 1) =
12                        substr(prior filmname, 1, length(prior filmname) - 1)
13                    and filmname > prior filmname)
14    )
15  select substr(replace(a.path, '|') || b.path, 2) filnmaes
16    from (select path_cnt, sys_connect_by_path(path, '|') path
17             from tmp
18            where connect_by_isleaf = 1
19            start with path_cnt = cnt
20           connect by path_cnt = prior path_cnt + cnt
21                  and path_cnt < 8) a, tmp b
22  where a.path_cnt + b.cnt = 8
23     and a.path_cnt < b.path_cnt
24     and rownum = 1;

未选定行

SQL>

看不懂，哎

junsansi

原帖由 nyfor 于 2008-7-3 17:55 发表
三思没有看原始贴, 本题是要随机抽取8条电影记录, 同一部电影可能存在上下集情况, 这时电影记录就是多条.
要求取出的8条记录中对于存在上下集的电影如果要取则必须该电影集的全部记录全部提取到.
三思的代码我将 10 改为 8 后运行结果不正确, 出现了 太极a, 却没有出现 太极b

SQL> /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园ii
侏罗纪公园iii
东方海盗传奇a
太极张三丰b
太极a
太极张三丰a
东方海盗传奇b

8 rows selected

后来修改版的已经解决了这个问题~~

junsansi

o,本贴已经加精了吧~~

junsansi

原帖由 nyfor 于 2008-7-3 17:55 发表
三思没有看原始贴, 本题是要随机抽取8条电影记录, 同一部电影可能存在上下集情况, 这时电影记录就是多条.
要求取出的8条记录中对于存在上下集的电影如果要取则必须该电影集的全部记录全部提取到.
三思的代码我将 10 改为 8 后运行结果不正确, 出现了 太极a, 却没有出现 太极b

SQL> /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园ii
侏罗纪公园iii
东方海盗传奇a
太极张三丰b
太极a
太极张三丰a
东方海盗传奇b

8 rows selected

[php]
SQL> with tt as(
  2  select filmname ofm,
  3         rtrim(filmname, 'abi') nfm,
  4         count(rtrim(filmname, 'abi')) over(partition by rtrim(filmname, 'abi')) ct,
  5         row_number() over(partition by rtrim(filmname, 'abi') order by 1) rn
  6    from t_film t
  7  )
  8  select ofm from(
  9  select ofm from tt where nfm in(
10  select nfm from(
11  select a.*, sum(ct) over(order by rownum) sv
12    from (select * from tt where rn = 1 order by dbms_random.value) a
13           ) where sv <= 10
14  ) order by ct desc) where rownum<=8
15  /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园iii
侏罗纪公园ii
忍者兵b
忍者兵a
三分钟先生
爱情呼叫转移
破敌

8 rows selected

SQL> /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园iii
侏罗纪公园ii
兄弟
飞行者
宝贝计划
别拿自己不当干部
爱情呼叫转移

8 rows selected

SQL> /

OFM
--------------------------------------------------
侏罗纪公园i
侏罗纪公园iii
侏罗纪公园ii
天行者b
忍者兵b
忍者兵a
天行者a
爱情呼叫转移

8 rows selected

SQL> /

OFM
--------------------------------------------------
天行者b
天行者a
冰河世纪2a
冰河世纪2b
彩票也疯狂
宝贝计划
飞行者
戏王之王

8 rows selected

SQL> /

OFM
--------------------------------------------------
奇迹b
奇迹a
太极张三丰b
东方海盗传奇b
东方海盗传奇a
太极张三丰a
彩票也疯狂
音乐

8 rows selected

SQL>
--
[/php]