|
|
原帖由 addm 于 2009-12-31 10:59 发表 ![]()
newkid的写法，每个类的排列并不是全排列，得到的记录数比全排列的纪录数量要多。
另外，在我的机器上，在sqlplus里和pl/sql developer里执行newkid的语句，结果都为空：未选定行
不知为什么，难道是人品问题。
我在newkid的基础上，改了一下，每个类为全排列。
Einstein Script646105
我用的CONNECT BY NOCYCLE确实是全排列呀？如果你看一下子查询结果，就会发现有5!=120行。
至于你看不到结果，应该是字符集问题，我为了省事就先用RPAD对齐再用SUBSTR取出。改用INSTR或REGEXP_SUBSTR来解析应该可以。或者去掉汉字改用英文：
WITH class_nationality as (
SELECT 'Englishman' as s FROM dual
union all
SELECT 'Swede' FROM dual
union all
SELECT 'Dane' FROM dual
union all
SELECT 'Norwegian' FROM dual
union all
SELECT 'German' FROM dual
),
class_house as(
SELECT 'red' as s FROM dual
union all
SELECT 'white' FROM dual
union all
SELECT 'green' FROM dual
union all
SELECT 'yellow' FROM dual
union all
SELECT 'blue' FROM dual
),
class_beverages as(
SELECT 'tea' as s FROM dual
union all
SELECT 'coffee' FROM dual
union all
SELECT 'milk' FROM dual
union all
SELECT 'beer' FROM dual
union all
SELECT 'water' FROM dual
),
class_cigarettes as(
SELECT 'pall mall' as s FROM dual
union all
SELECT 'dunhill' FROM dual
union all
SELECT 'blends' FROM dual
union all
SELECT 'blue master' FROM dual
union all
SELECT 'prince' FROM dual
),
class_pets as(
SELECT 'dog' as s FROM dual
union all
SELECT 'bird' FROM dual
union all
SELECT 'cat' FROM dual
union all
SELECT 'horse' FROM dual
union all
SELECT 'fish' FROM dual
)
,nationality AS (
SELECT TRIM(SUBSTR(p,2 ,19)) AS s1
,TRIM(SUBSTR(p,22,19)) AS s2
,TRIM(SUBSTR(p,42,19)) AS s3
,TRIM(SUBSTR(p,62,19)) AS s4
,TRIM(SUBSTR(p,82,19)) AS s5
FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
FROM class_nationality
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
)
)
,house AS (
SELECT TRIM(SUBSTR(p,2 ,19)) AS s1
,TRIM(SUBSTR(p,22,19)) AS s2
,TRIM(SUBSTR(p,42,19)) AS s3
,TRIM(SUBSTR(p,62,19)) AS s4
,TRIM(SUBSTR(p,82,19)) AS s5
FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
FROM class_house
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
)
)
,beverages AS (
SELECT TRIM(SUBSTR(p,2 ,19)) AS s1
,TRIM(SUBSTR(p,22,19)) AS s2
,TRIM(SUBSTR(p,42,19)) AS s3
,TRIM(SUBSTR(p,62,19)) AS s4
,TRIM(SUBSTR(p,82,19)) AS s5
FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
FROM class_beverages
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
)
)
,cigarettes AS (
SELECT TRIM(SUBSTR(p,2 ,19)) AS s1
,TRIM(SUBSTR(p,22,19)) AS s2
,TRIM(SUBSTR(p,42,19)) AS s3
,TRIM(SUBSTR(p,62,19)) AS s4
,TRIM(SUBSTR(p,82,19)) AS s5
FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
FROM class_cigarettes
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
)
)
,pets AS (
SELECT TRIM(SUBSTR(p,2 ,19)) AS s1
,TRIM(SUBSTR(p,22,19)) AS s2
,TRIM(SUBSTR(p,42,19)) AS s3
,TRIM(SUBSTR(p,62,19)) AS s4
,TRIM(SUBSTR(p,82,19)) AS s5
FROM (SELECT SYS_CONNECT_BY_PATH(RPAD(s,19),'\') AS p
FROM class_pets
WHERE LEVEL=5
CONNECT BY NOCYCLE LEVEL<=5 AND s<>PRIOR s
)
)
SELECT RPAD(nationality.s1,16)||RPAD(house.s1,16)||RPAD(beverages.s1,16)||RPAD(cigarettes.s1,16)||RPAD(pets.s1,16)||CHR(10)||
RPAD(nationality.s2,16)||RPAD(house.s2,16)||RPAD(beverages.s2,16)||RPAD(cigarettes.s2,16)||RPAD(pets.s2,16)||CHR(10)||
RPAD(nationality.s3,16)||RPAD(house.s3,16)||RPAD(beverages.s3,16)||RPAD(cigarettes.s3,16)||RPAD(pets.s3,16)||CHR(10)||
RPAD(nationality.s4,16)||RPAD(house.s4,16)||RPAD(beverages.s4,16)||RPAD(cigarettes.s4,16)||RPAD(pets.s4,16)||CHR(10)||
RPAD(nationality.s5,16)||RPAD(house.s5,16)||RPAD(beverages.s5,16)||RPAD(cigarettes.s5,16)||RPAD(pets.s5,16) AS RESULT
FROM nationality
,house
,beverages
,cigarettes
,pets
WHERE ('Englishman','red') IN ((nationality.s1,house.s1),(nationality.s2,house.s2),(nationality.s3,house.s3),(nationality.s4,house.s4),(nationality.s5,house.s5))
AND ('Swede','dog') IN ((nationality.s1,pets.s1),(nationality.s2,pets.s2),(nationality.s3,pets.s3),(nationality.s4,pets.s4),(nationality.s5,pets.s5))
AND ('Dane','tea') IN ((nationality.s1,beverages.s1),(nationality.s2,beverages.s2),(nationality.s3,beverages.s3),(nationality.s4,beverages.s4),(nationality.s5,beverages.s5))
AND ('green','white') IN ((house.s1,house.s2),(house.s2,house.s3),(house.s3,house.s4),(house.s4,house.s5))
AND ('green','coffee') IN ((house.s1,beverages.s1),(house.s2,beverages.s2),(house.s3,beverages.s3),(house.s4,beverages.s4),(house.s5,beverages.s5))
AND ('pall mall','bird') IN ((cigarettes.s1,pets.s1),(cigarettes.s2,pets.s2),(cigarettes.s3,pets.s3),(cigarettes.s4,pets.s4),(cigarettes.s5,pets.s5))
AND ('yellow','dunhill') IN ((house.s1,cigarettes.s1),(house.s2,cigarettes.s2),(house.s3,cigarettes.s3),(house.s4,cigarettes.s4),(house.s5,cigarettes.s5))
AND 'milk' = beverages.s3
AND 'Norwegian' = nationality.s1
AND ('blends','cat') IN ((cigarettes.s1,pets.s2),(cigarettes.s2,pets.s3),(cigarettes.s3,pets.s4),(cigarettes.s4,pets.s5),(cigarettes.s2,pets.s1),(cigarettes.s3,pets.s2),(cigarettes.s4,pets.s3),(cigarettes.s5,pets.s4))
AND ('dunhill','horse') IN ((cigarettes.s1,pets.s2),(cigarettes.s2,pets.s3),(cigarettes.s3,pets.s4),(cigarettes.s4,pets.s5),(cigarettes.s2,pets.s1),(cigarettes.s3,pets.s2),(cigarettes.s4,pets.s3),(cigarettes.s5,pets.s4))
AND ('blue master','beer') IN ((cigarettes.s1,beverages.s1),(cigarettes.s2,beverages.s2),(cigarettes.s3,beverages.s3),(cigarettes.s4,beverages.s4),(cigarettes.s5,beverages.s5))
AND ('German','prince') IN ((nationality.s1,cigarettes.s1),(nationality.s2,cigarettes.s2),(nationality.s3,cigarettes.s3),(nationality.s4,cigarettes.s4),(nationality.s5,cigarettes.s5))
AND 'blue' = house.s2
AND ('blends','water') IN ((cigarettes.s1,beverages.s2),(cigarettes.s2,beverages.s3),(cigarettes.s3,beverages.s4),(cigarettes.s4,beverages.s5),(cigarettes.s2,beverages.s1),(cigarettes.s3,beverages.s2),(cigarettes.s4,beverages.s3),(cigarettes.s5,beverages.s4))
RESULT
--------------------------------------------------------------------------------------
Norwegian yellow water dunhill cat
Dane blue tea blends horse
Englishman red milk pall mall bird
German green coffee prince fish
Swede white beer blue master dog
你用的全排列方法和jackywood差不多，我改用CONNECT BY就是为了避免写那么多不等连接条件。
|
|
楼主: qingyun
IP卡
狗仔卡
发表于 2009-12-31 10:59
显身卡
