SQL排名问题

[q520525745]

SELECT COUNT(*) AS MC FROM table WHERE uid=变量 ORDER BY COUNT(*) DESC

表里的字段 id uid fuid
我想查询当前uid的名次排名的根据是fuid数量越多排名就越高
谁能帮我在上面这句的基础上修改下

[q520525745]

[Quote=引用 4 楼 wwwwgou 的回复:]
SQL code
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
……
[/Quote]
我只想查一个uid的排名该怎么写 不是全部排名

[blftest1]

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 uid , COUNT(*) AS MC FROM tb where uid=变量 group by uid ORDER BY mc DESC

[王向飞]

给出测试数据和你希望得到的结果

[百年树人]

SELECT COUNT(*) AS MC,
排名=rank() over(order by count(*) desc) 
FROM table WHERE uid=变量 ORDER BY COUNT(*) DESC

[dawugui]

SELECT uid , COUNT(*) AS MC FROM table group by uid ORDER BY mc DESC

[Shawn]

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 tb

SELECT 
	number = DENSE_RANK() OVER(ORDER BY MC DESC),
	uid, 
	MC
FROM (SELECT uid, MC = COUNT(1) FROM tb GROUP BY uid) A

[飘零一叶]

楼主参考

IF OBJECT_ID('Sales') IS NOT NULL DROP TABLE Sales
GO
CREATE TABLE Sales
(
empid varchar(10) primary key not null,
mgrid varchar(10) not null,
qty int not null
)
insert into Sales
select 'A','Z',300 union all
select 'B','X',100 union all
select 'C','X',200 union all
select 'D','Y',200 union all
select 'E','Z',250 union all
select 'F','Z',300 union all
select 'G','X',100 union all
select 'H','Y',150 union all
select 'I','X',250 union all
select 'J','Z',100 union all
select 'K','Y',200 

CREATE NONCLUSTERED INDEX IDX_GTY_EMPID ON Sales(qty,empid)
CREATE NONCLUSTERED INDEX IDX_GTY_EMPID ON Sales(MGRID,qty,empid)

--1.
SELECT empid,qty,ROW_NUMBER() OVER(ORDER BY qty) AS NUMS FROM Sales
--2.
SELECT empid
,qty
,ROW_NUMBER() OVER(ORDER BY qty)AS NUMS1
,ROW_NUMBER() OVER(ORDER BY qty,EMPID) AS NUMS2
FROM SALES

--3.
SELECT empid
,qty
,MGRID
,ROW_NUMBER() OVER(PARTITION BY MGRID ORDER BY qty,EMPID) AS NUMS2
FROM SALES

--4.用游标计算行号
DECLARE @SalesRN TABLE(empid VARCHAR(10),qty INT,rn INT)
DECLARE @empid VARCHAR(10),@qty INT,@rn INT
BEGIN TRAN 
DECLARE rncursor CURSOR FAST_FORWARD FOR 
      SELECT empid,qty FROM Sales ORDER BY qty,empid 
OPEN rncursor
SET @rn=0
FETCH NEXT FROM rncursor INTO @empid,@qty
WHILE @@FETCH_STATUS=0
BEGIN 
   SET @rn=@rn+1
   INSERT INTO @SalesRN(empid,qty,rn) VALUES(@empid,@qty,@rn)
   FETCH NEXT FROM rncursor INTO @empid,@qty
END
CLOSE rncursor
DEALLOCATE rncursor
COMMIT TRAN 
SELECT * FROM @SalesRN

--5.IDENTITY
SELECT empid,qty,identity(int,1,1) as rn into SalesRN FROM Sales ORDER BY qty,empid  
select * from SalesRN

--6.RANK() OVER(ORDER BY ())  DENSE_RANK() OVER (ORDER BY ())
SELECT empid
,qty
,RANK() OVER(ORDER BY qty)AS NUMS1
,ROW_NUMBER() OVER(ORDER BY qty,EMPID) AS NUMS2
FROM SALES

SELECT empid
,qty
,DENSE_RANK() OVER(ORDER BY qty)AS NUMS1
,ROW_NUMBER() OVER(ORDER BY qty,EMPID) AS NUMS2
FROM SALES