create table tb
(
id int identity(1, 1),
uid int,
fuid int
)
insert into tb(uid, fuid)
select 1, 1
union all
select 1, 2
union all
select 2, 3
union all
select 1, 4
union all
select 3, 5
select * from tbSELECT
number = DENSE_RANK() OVER(ORDER BY MC DESC),
uid,
MC
FROM (SELECT uid, MC = COUNT(1) FROM tb GROUP BY uid) A比如我只想查uid为2的排名,请问我该怎么写?
但我只想查询指定的uid排名是第几
select *
from
(
SELECT
number = DENSE_RANK() OVER(ORDER BY MC DESC),
uid,
MC
FROM (SELECT uid, MC = COUNT(1) FROM #tb GROUP BY uid) A
)t
where uid = 2
(
id int identity(1, 1),
uid int,
fuid int
)
insert into tb(uid, fuid)
select 1, 1
union all
select 1, 2
union all
select 2, 3
union all
select 1, 4
union all
select 3, 5select * from tb
declare @query int set @query = 2declare @sorted table (rid int identity(1,1), uid int, cnt int)
insert into @sorted select uid, count(1) as cnt from tb group by uidselect *
from tb a
where exists (select 1 from @sorted b
where b.uid = a.uid and b.rid = @query)
declare @query int set @query = 2declare @sorted table (rid int identity(1,1), uid int, cnt int)
insert into @sorted select uid, count(1) as cnt from tb group by uid order by cnt descselect *
from tb a
where exists (select 1 from @sorted b
where b.uid = a.uid and b.rid = @query)
(SELECT
number = DENSE_RANK() OVER(ORDER BY MC DESC),
uid,
MC
FROM (SELECT uid, MC = COUNT(1) FROM tb GROUP BY uid) A)b
where uid=2