--> 测试数据: [ta] if object_id('[ta]') is not null drop table [ta] go create table [ta] ([A] int,[B] int,[C] int) insert into [ta] select 0,4,8 union all select 1,4,8 union all select 1,4,8 union all select 2,4,8 union all select 2,4,8 union all select 2,5,8 union all select 3,5,8 union all select 3,5,8 union all select 3,5,9 union all select 3,5,9 union all select 3,5,9 union all select 3,9,9 union all select 3,9,9 union all select 3,9,9;with wang as (select number from master..spt_values where type='p' and number<10), wang1 as(select a from ta union all select B from ta union all select c from ta), wang2 as (select A,cnt=COUNT(*) from wang1 group by A), wang3 as (select number,cnt from wang left join wang2 on wang.number=wang2.A)select number,cnt,ID=case when cnt is not null then ROW_NUMBER() over ( order by cnt desc) else (select COUNT(*) from wang3 where cnt is not null)+1 end from wang3 order by number 0 1 8 1 2 7 2 3 6 3 8 2 4 5 5 5 6 4 6 NULL 9 7 NULL 9 8 8 3 9 9 1
改一下select number,cnt,ID=RANK() over(order by cnt desc) from wang3 order by number
if object_id('[ta]') is not null
drop table [ta]
go
create table [ta] ([A] int,[B] int,[C] int)
insert into [ta]
select 0,4,8 union all
select 1,4,8 union all
select 1,4,8 union all
select 2,4,8 union all
select 2,4,8 union all
select 2,5,8 union all
select 3,5,8 union all
select 3,5,8 union all
select 3,5,9 union all
select 3,5,9 union all
select 3,5,9 union all
select 3,9,9 union all
select 3,9,9 union all
select 3,9,9;with
wang as (select number from master..spt_values where type='p' and number<10),
wang1 as(select a from ta union all select B from ta union all select c from ta),
wang2 as (select A,cnt=COUNT(*) from wang1 group by A),
wang3 as (select number,cnt from wang left join wang2 on wang.number=wang2.A)select number,cnt,ID=case when cnt is not null then ROW_NUMBER() over ( order by cnt desc)
else (select COUNT(*) from wang3 where cnt is not null)+1 end
from wang3
order by number
0 1 8
1 2 7
2 3 6
3 8 2
4 5 5
5 6 4
6 NULL 9
7 NULL 9
8 8 3
9 9 1
from wang3
order by number