发现一位专门用SQL解各种谜题的高人！

zhangweicai74 · 发表于 2010-7-23 16:50

感觉00的数学很强。

Coast_lichao · 发表于 2010-7-25 21:35

高人啊经典！

gkl0818 · 发表于 2010-7-29 09:10

newkid How to find the Longest Common Subsequence (LCS) in SQL这个方案你给出了with递归检查
但是在我的ORA11GR2环境跑的时候出现ORA-01489错误你是怎么决绝的？？

newkid · 发表于 2010-7-29 23:37

原帖由 gkl0818 于 2010-7-29 09:10 发表
newkid How to find the Longest Common Subsequence (LCS) in SQL这个方案你给出了with递归检查
但是在我的ORA11GR2环境跑的时候出现ORA-01489错误你是怎么决绝的？？

用递归拼接出来的列有时候会出这个错，你可以加个CAST函数，告诉ORACLE这个列有多宽：

WITH d AS (
SELECT dt.*,COUNT(DISTINCT id) OVER(PARTITION BY grp_id,c) cnt_c
   FROM (SELECT dt.*,SUBSTR(in_str,rn,1) c,rn
            FROM (SELECT data.*,COUNT(*) OVER (PARTITION BY grp_id) cnt FROM data) dt
               ,(SELECT ROWNUM rn FROM (SELECT MAX(LENGTH(in_str)) len FROM data) CONNECT BY ROWNUM<=len)
         WHERE rn<=LENGTH(in_str)
         ) dt
)
,t(id,grp_id,in_str,rn,w,cnt) AS (
SELECT id,grp_id,in_str,rn,CAST(c AS VARCHAR2(1000)),cnt FROM d
UNION ALL
SELECT d.id,d.grp_id,d.in_str,d.rn,t.w||d.c,t.cnt
   FROM t,d
   WHERE t.id = d.id AND t.rn<d.rn
)
SELECT grp_id,w
  FROM (SELECT grp_id,w,rank() OVER(PARTITION BY grp_id ORDER BY LENGTH(w) DESC) rnk
      FROM t
      GROUP BY grp_id,w
      HAVING COUNT(DISTINCT id) = MAX(cnt)
      )
WHERE rnk=1
;