如下表： VIN 维修类型
car-1 A
car-1 A
car-1 B
car-1 C
car-2 A
car-2 C
car-2 C
意思就是每个车有多条维修记录，想取每个车的最多维修类型，最终得到结果 VIN 维修类型
car-1 A
car-2 C
求哪位大侠帮帮忙！非常感谢！
------解决方案--------------------
- SQL code
select col1,col2from(select col1,col2,count(1) as cnt from b group by col1,col2)twhere cnt=(select top count(1) from tb group by col1,col2 order by count(1) desc)
------解决方案--------------------
- SQL code
with cte as( select col1,col2,count(0) cts from b group by col1,col2)select col1,col2 from cte a where cts=(select max(cts) from cte where col1=a.col1)
------解决方案--------------------
- SQL code
; with f as(select col1,col2,COUNT(1) as num from tb group by col1,col2 )select col1,col2 from f t where num=(select MAX(num) from f where col1=t.col1) order by 1
------解决方案--------------------
- SQL code
select VIN,维修类型from tb agroup by VIN,维修类型havving count(*) >all (select count(*) from tb bwhere a.VIN=b.VIN group by VIN 维修类型)
------解决方案--------------------
- SQL code
select VIN,维修类型from tb agroup by VIN,维修类型having count(*) >all (select count(*) from tb bwhere a.VIN=b.VIN group by VIN 维修类型)
------解决方案--------------------
- SQL code
select VIN,维修类型from tb agroup by VIN,维修类型having count(*) >=all (select count(*) from tb bwhere a.VIN=b.VIN group by VIN 维修类型)
------解决方案--------------------
- SQL code
CREATE TABLE VIN(car VARCHAR(10),col CHAR(1))INSERT dbo.VINSELECT 'car-1', 'A' UNION ALLSELECT 'car-1', 'A' UNION ALLSELECT 'car-1', 'B' UNION ALLSELECT 'car-1', 'C' UNION ALLSELECT 'car-2', 'A' UNION ALLSELECT 'car-2', 'C' UNION ALLSELECT 'car-2', 'C';WITH cte AS ( SELECT car , col , COUNT(col) AS [count] FROM dbo.VIN GROUP BY car , col ) SELECT a.car , a.col FROM cte a WHERE NOT EXISTS ( SELECT 1 FROM cte WHERE car = a.car AND [count] > a.[count] )DROP TABLE dbo.VIN/*car col---------- ----car-1 Acar-2 C(2 行受影响)*/
------解决方案--------------------
- SQL code
if object_id('tempdb.dbo.#t') is not null drop table #tcreate table #t (VIN varchar(5),Cate varchar(1))insert into #tselect 'car-1','A' union allselect 'car-1','A' union allselect 'car-1','B' union allselect 'car-1','C' union allselect 'car-2','A' union allselect 'car-2','C' union allselect 'car-2','C'SELECT DISTINCT VIN,CATE FROM (SELECT VIN,CATE,MAXCS,MAX (MAXCS)OVER(PARTITION BY VIN) F1 FROM (SELECT *,COUNT (1) OVER (PARTITION BY VIN,CATE ) MAXCS FROM #t)A)GWHERE MAXCS=F1