SQL列最大重复项代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL列最大重复项

作者:东昌 / 发布于2016/2/2/ 836

SELECT 1 AS co1, 'a' AS co2
INTO #a 
UNION
SELECT  2, 'a'
UNION
SELECT  11,'a'
UNION
SELECT 12, 'a'
UNION
SELECT 13, 'a'
UNION
SELECT 14, 'a'
UNION
SELECT 15, 'a'
UNION
SELECT 3, 'b'
UNION
SELECT 7, 'b'
UNION
SELECT 8, 'b'
UNION
SELECT  9, 'b'
UNION
SELECT  10, 'b'
UNION
SELECT 4, 'c'
UNION
SELECT 5, 'c'
UNION
SELECT 6, 'c'
 
SELECT * FROM #a
  --第二步：操作
  SELECT co2,MAX(counts) counts FROM 
(
 SELECT rowid,co2,SUM(co1) counts FROM 
 (
  SELECT co1*-1 co1,co2,ROW_NUMBER() OVER (ORDER BY co1) rowid  FROM #a a
  WHERE NOT EXISTS (SELECT * FROM #a WHERE co1=a.co1-1 AND co2=a.co2)
  UNION ALL
  SELECT co1+1,co2,ROW_NUMBER() OVER (ORDER BY co1) rowid FROM #a a
  WHERE NOT EXISTS (SELECT * FROM #a WHERE co1=a.co1+1 AND co2=a.co2)
  ) p 
 GROUP BY rowid,co2 
) p1
GROUP BY co2
 
DROP TABLE #a

试试其它关键字

　最大重复项　

同语言下

可能有用的

东昌贡献的其它代码(28)