一、首先说一下我的需求,在数据表T_CERTI_EXELAW中求certicode字段前三位出现次数最多的三位（期望结果是207），数据表如下：
期望结果是207
二、两种sql语句比较（oracle数据库）
方法一：常规嵌套(耗时：0.031seconds)
SELECT
NAME
FROM (SELECT HEAD AS NAME, COUNT(HEAD) AS TIMES
FROM (SELECT SUBSTR(M.CERTICODE, 0, 3) AS
HEAD
FROM
T_CERTI_EXELAW M
WHERE M.MAKEDEPART
=
(SELECT MAKEDEPART
FROM T_CERTI_EXELAW
WHERE CERTICODE = ‘ccc20140310112954‘)) T
GROUP BY HEAD)
WHERE TIMES = (SELECT MAX(TIMES)
FROM (SELECT HEAD
AS NAME, COUNT(HEAD) AS TIMES
FROM (SELECT SUBSTR(M.CERTICODE, 0, 3) AS HEAD
FROM T_CERTI_EXELAW M
WHERE M.MAKEDEPART =
(SELECT MAKEDEPART
FROM
T_CERTI_EXELAW
WHERE CERTICODE =
‘ccc20140310112954‘)) T
GROUP BY HEAD))
方法二： 使用排序（耗时：0.016 seconds）
SELECT NAME FROM (SELECT HEAD AS NAME, COUNT(HEAD) AS TIMES
FROM (SELECT SUBSTR(M.CERTICODE, 0, 3) AS
HEAD
FROM T_CERTI_EXELAW M
WHERE M.MAKEDEPART =
(SELECT MAKEDEPART
FROM
T_CERTI_EXELAW
WHERE CERTICODE =
‘ccc20140310112954‘)) T
GROUP BY HEAD ORDER BY times DESC ) WHERE ROWNUM=‘1‘
总结：我感觉以上方法还不是不够高效，有好方法请指教。
求数据表中一字段的相同前缀出现次数最大的前缀,布布扣,bubuko.com
求数据表中一字段的相同前缀出现次数最大的前缀
原文：http://www.cnblogs.com/hsuchan/p/3591725.html
