select col1,col2
from
(select col1,col2,count(1) as cnt from b group by col1,col2)t
where 
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 a
group by VIN,维修类型
havving count(*) >all 
(
select  count(*) 
from tb b
where a.VIN=b.VIN 
group by VIN 维修类型
)

------解决方案--------------------
SQL code
select  VIN,维修类型
from tb a
group by VIN,维修类型
having count(*) >all 
(
select  count(*) 
from tb b
where a.VIN=b.VIN 
group by VIN 维修类型
)

------解决方案--------------------
SQL code
select  VIN,维修类型
from tb a
group by VIN,维修类型
having count(*) >=all 
(
select  count(*) 
from tb b
where a.VIN=b.VIN 
group by VIN 维修类型
)

------解决方案--------------------
SQL code
CREATE TABLE VIN(car VARCHAR(10),col CHAR(1))
INSERT dbo.VIN
SELECT  'car-1', 'A' UNION ALL
SELECT  'car-1', 'A' UNION ALL
SELECT  'car-1', 'B' UNION ALL
SELECT  'car-1', 'C' UNION ALL
SELECT  'car-2', 'A' UNION ALL
SELECT  'car-2', 'C' UNION ALL
SELECT  '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      A
car-2      C

(2 行受影响)

*/

------解决方案--------------------
SQL code
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (VIN varchar(5),Cate varchar(1))
insert into #t
select 'car-1','A' union all
select 'car-1','A' union all
select 'car-1','B' union all
select 'car-1','C' union all
select 'car-2','A' union all
select 'car-2','C' union all
select '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)G
WHERE MAXCS=F1