如题,该如何解决

如题
如下表： 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