如下表: 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
求哪位大侠帮帮忙!非常感谢!
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
求哪位大侠帮帮忙!非常感谢!
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)
(
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)
(
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
from tb a
group by VIN,维修类型
havving count(*) >all
(
select count(*)
from tb b
where a.VIN=b.VIN
group by VIN 维修类型
)
from tb a
group by VIN,维修类型
having count(*) >all
(
select count(*)
from tb b
where a.VIN=b.VIN
group by VIN 维修类型
)
from tb a
group by VIN,维修类型
having count(*) >=all
(
select count(*)
from tb b
where a.VIN=b.VIN
group by VIN 维修类型
)
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 行受影响)*/
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=F1VIN CATE
car-1 A
car-2 C