部门明智的前2名最高薪水，其中部门可能是动态文本 [英] Department wise top 2 highest salary, where department might be dynamic text

问题描述

CREATE TABLE #emp
(
	Dept varchar(100),
	Emp varchar(100),
	Sal numeric(18,2)
)
INSERT INTO #Emp values(''Comp'',''A'',6000)
INSERT INTO #Emp values(''Comp'',''B'',7000)
INSERT INTO #Emp values(''Comp'',''C'',10000)
INSERT INTO #Emp values(''Comp'',''D'',9000)
INSERT INTO #Emp values(''Elec'',''P'',2000)
INSERT INTO #Emp values(''Elec'',''Q'',10000)
INSERT INTO #Emp values(''Elec'',''R'',11000)
INSERT INTO #Emp values(''Eng'',''AA'',15000)
INSERT INTO #Emp values(''Eng'',''BB'',2000)

/*我想要输出(部门明智的前2名最高薪水，部门可能是动态文本)
部门Emp Sal
----------------------------
Comp C 10000
Comp D 9000
Elec R 11000
Elec Q 10000
英制AA 15000
英制BB 2000
*/

解决方案

  SELECT  * 选择 RANK() OVER (> PARTITION   BY  DEPT  ORDER   BY  SAL  DESC ) AS  '  FROM  EMP
) AS 一个 WHERE  RANK< = 2 

  WITH  Temp_Table
 AS 
(
选择 ROW_NUMBER()在(分区  by 部门顺序  by  sal)rownum，* 来自 #emp
)
选择 * 来自 Temp_Table 其中 rownum<  3  

如果有帮助，请 投票 和 接受答案 .

然后使用

AddWithValue("@DEPT", departmentText);

CREATE TABLE #emp
(
	Dept varchar(100),
	Emp varchar(100),
	Sal numeric(18,2)
)
INSERT INTO #Emp values(''Comp'',''A'',6000)
INSERT INTO #Emp values(''Comp'',''B'',7000)
INSERT INTO #Emp values(''Comp'',''C'',10000)
INSERT INTO #Emp values(''Comp'',''D'',9000)
INSERT INTO #Emp values(''Elec'',''P'',2000)
INSERT INTO #Emp values(''Elec'',''Q'',10000)
INSERT INTO #Emp values(''Elec'',''R'',11000)
INSERT INTO #Emp values(''Eng'',''AA'',15000)
INSERT INTO #Emp values(''Eng'',''BB'',2000)

/* I want the output (Department wise top 2 highest salary, where department might be dynamic text)
Dept Emp Sal
----------------------------
Comp C 10000
Comp D 9000
Elec R 11000
Elec Q 10000
Eng AA 15000
Eng BB 2000
*/

解决方案

SELECT * FROM
(
SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC) AS 'Rank', *
FROM EMP
) AS A WHERE RANK < =2

WITH Temp_Table
AS
(
select ROW_NUMBER() over (Partition by dept order by sal) rownum,* from #emp
)
select * from Temp_Table where rownum < 3

Please vote and Accept Answer if it Helped.

SELECT * FROM #emp WHERE Dept=@DEPT LIMIT 0, 2 ORDER BY Sal ASC

Then use

AddWithValue("@DEPT", departmentText);

这篇关于部门明智的前2名最高薪水，其中部门可能是动态文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！