一个表employee 字段有(部门,姓名,次数) Dept_name empl_name cs
现在问题是如何写SQL取得每个部门次数前3位的姓名及次数?Dept_name empl_name cs
A T1 11
A T2 2
A T3 3
A T4 4
B W1 1
B W2 2
B W3 3
B W4 4
C Q2 23
C Q3 31
C Q4 45 如何得到如下结果?
Dept_name empl_name cs
A T1 11
A T4 4
A T3 3
B W4 4
B W3 3
B W2 2
C Q4 45
C Q3 31
C Q2 23谢谢!
现在问题是如何写SQL取得每个部门次数前3位的姓名及次数?Dept_name empl_name cs
A T1 11
A T2 2
A T3 3
A T4 4
B W1 1
B W2 2
B W3 3
B W4 4
C Q2 23
C Q3 31
C Q4 45 如何得到如下结果?
Dept_name empl_name cs
A T1 11
A T4 4
A T3 3
B W4 4
B W3 3
B W2 2
C Q4 45
C Q3 31
C Q2 23谢谢!
--try
select * into #temp from tablename
alter tabel #temp add id int identity(1,1)
select * from #temp a where id in (select top 3 id from #temp where Dept_name=a.Dept_name order by cs )
Select * From employee A
Where (Select Count(*) From employee Where Dept_name= A.Dept_name And cs> A.cs) < 3
Order By Dept_name, cs--方法二:
Select * From employee A
Where Exists (Select Count(*) From employee Where Dept_name= A.Dept_name And cs> A.cs Having Count(*) < 3)
Order By Dept_name, cs--方法三:
Select * From employee A
Where AddDate In (Select TOP 3 cs From employee Where Dept_name= A.Dept_name Order By cs Desc)
Order By Dept_name , cs
是 cs
Where (Select Count(*) From employee Where Dept_name= A.Dept_name And cs< A.cs) < 3