SQL查询某个机台下维修次数最多零件

Question

现在要从维修工单中筛选出每个机台维修次数最多的部件信息，
表名为MT_OVERHAUL_RECORD，要求筛选每台机器的最大维修次数零件

Answer 1

select * from (
select count(*) as num ,machine_id,device_id,device_name from tablename group by machine_id,device_id,device_name) b
where (b.num,machine_id) in(
select max(num),machine_id from (
select count(*) as num ,machine_id,device_id,device_name from tablename group by machine_id,device_id,device_name) a
group by machine_id
)

sqlserver下的写法。不知道对不对，但是思路应该是没错的。因为没装sqlserver，没法验证