求数据表中一字段的相同前缀出现次数最大的前缀
一、首先说一下我的需求,在数据表T_CERTI_EXELAW中求certicode字段前三位出现次数最多的三位(期望结果是207),数据表如下:
![](http://images0.cnblogs.com/i/297536/201403/101419145073872.png)
期望结果是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'
总结:我感觉以上方法还不是不够高效,有好方法请指教。