7,714
社区成员
发帖
与我相关
我的任务
分享
select a.*
from (
select X,count(*) as cnt from (
select num1 as X from test1
union all
select num2 from test1
union all
select num3 from test1
union all
select num4 from test1
) group by X
) a , (
select top 4 cnt from (
select count(*) as cnt from (
select num1 as X from test1
union all
select num2 from test1
union all
select num3 from test1
union all
select num4 from test1
) group by X
) group by cnt
order by 1 desc
) b
where a.cnt=b.cnt
X cnt
王五 5
李四 5
张三 4
刘六 4
李一 3
谢五 2