SQL 如何在不使用分析功能的情况下明智地获得第二高薪部门？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31243365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get second highest salary department wise without using analytical functions?
提问by sandeep reddy
Suppose we have 3 employees in each department.we have total 3 departments . Below is the sample source table
假设我们每个部门有 3 名员工。我们总共有 3 个部门。下面是示例源表
Emp deptno salary
A 10 1000
B 10 2000
C 10 3000
D 20 7000
E 20 9000
F 20 8000
G 30 17000
H 30 15000
I 30 30000
Output
输出
B 10 2000
F 20 8000
G 30 17000
With using analytic function dense_rank we can achive the second highest salary dept wise.
通过使用分析函数dense_rank，我们可以达到第二高的工资部门。
Can we achieve this without using ANY analytic function ???
我们可以在不使用任何分析函数的情况下实现这一点吗？？？
Is Max() is also analytic function ??
Max() 也是解析函数吗？？
回答by Gordon Linoff
It is a pain, but you can do it. The following query gets the second highest salary:
这是一种痛苦，但你可以做到。以下查询获得第二高的薪水：
select t.deptno, max(t.salary) as maxs
from table t
where t.salary < (select max(salary)
from table t2
where t2.deptno = t.deptno
)
group by t.deptno;
You can then use this to get the employee:
然后，您可以使用它来获取员工：
select t.*
from table t join
(select t.deptno, max(t.salary) as maxs
from table t
where t.salary < (select max(salary)
from table t2
where t2.deptno = t.deptno
)
group by t.deptno
) tt
on t.deptno = tt.deptno and t.salary = tt.maxs;
回答by Adarsh Kumar
This will give you 2nd highest salary in each department:
这将为您提供每个部门的第二高薪水：
SELECT a.Emp, a.deptno, a.salary
FROM Emp a
WHERE 1 = (SELECT COUNT(DISTINCT salary)
FROM Emp b
WHERE b.salary > a.salary AND a.deptno = b.deptno)
group by a.deptno
回答by Ashish Shukla
Create table and insert dummy data
创建表并插入虚拟数据
CREATE TABLE #Employee
(
Id Int,
Name NVARCHAR(10),
Sal int,
deptId int
)
INSERT INTO #Employee VALUES
(1, 'Ashish',1000,1),
(2,'Gayle',3000,1),
(3, 'Salman',2000,2),
(4,'Prem',44000,2)
Query to get result
查询以获取结果
;WITH cteRowNum AS (
SELECT *,
DENSE_RANK() OVER(PARTITION BY deptId ORDER BY Sal DESC) AS RowNum
FROM #Employee
)
SELECT *
FROM cteRowNum
WHERE RowNum = 2;
回答by mtk
回答by Somendra Joshi
On MySQL this how you can get second highest salary, given table name is salaries:
在 MySQL 上，你如何获得第二高的薪水，给定的表名是薪水：
By Nested Queries: (where you can change offset 0/1/2 for first, second and third place respectively)
通过嵌套查询：（您可以分别更改第一、第二和第三位的偏移量 0/1/2）
select
*
from
salaries as t1
where
t1.salary = (select
salary
from
salaries
where
salaries.deptno = t1.deptno ORDER by salary desc limit 1 offset 1);
or might be by creating rank: (where you can change rank= 1/2/3 for first, second and third place respectively)
或者可能是通过创建排名：（您可以分别将排名= 1/2/3 更改为第一、第二和第三名）
SET @prev_value = NULL;
SET @rank_count = 0;
select * from
(SELECT
s.*,
CASE
WHEN @prev_value = deptno THEN @rank_count := @rank_count + 1
WHEN @prev_value := deptno THEN @rank_count := 1
ELSE @rank_count := 1
END as rank
FROM salaries s
ORDER BY deptno, salary desc) as t
having t.rank = 2;
回答by Rudolf Yurgenson
Quite straightforward and declarative, but slow
非常直接和声明性，但速度较慢
select
t1.*
from
#tmp t1
inner join #tmp h1 on h1.dept = t1.dept and h1.emp <> t1.emp
left outer join #tmp h2 on h2.dept = h1.dept and h2.salary > h1.salary
left outer join #tmp t2 on t1.dept = t2.dept and t2.salary > t1.salary and t2.emp <> h1.emp
where
t2.emp is null and h2.emp is null
回答by mahesh sambanni
SQL Query:
SQL查询：
select TOP 2 max(salary),Emp from EMployee where deptno='your_detpno'
回答by npsingh
Very simple logic.
很简单的逻辑。
Please try:
请尝试：
SELECT dept as dd, ( SELECT ee.salary FROM `employees` as ee WHERE ee.dept=dd
ORDER BY ee.salary DESC LIMIT 1,1 ) as sndHigh
FROM `employees`
WHERE 1
GROUP BY dept
回答by Ninder Singh
select min(salary),deptno from (SELECT distinct top 2 salary,deptno from table ORDER BY salary DESC) as a group by deptno
select min(salary),deptno from (SELECT distinct top 2 salary,deptno from table ORDER BY salary DESC) 作为一个组 by deptno
回答by rohit sethi
CREATE TABLE Employee
([Name] varchar(1), [Dept] varchar(1), [Salary] int)
;
INSERT INTO Employee
([Name], [Dept], [Salary])
VALUES
('a', 'M', 20),
('b', 'M', 25),
('c', 'M', 30),
('d', 'C', 44),
('e', 'C', 45),
('f', 'C', 46),
('g', 'H', 20)
