由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档，一周内的贴子最多显示50字，超过一周显示500字 访问原贴
Database版 - 给大家贡献一个fb面试的sql问题
相关主题
● 问一道sql的面试题啊 自己实在是没想出来● 请教一个问题
● To get the 2nd, 3rd, 4th largest value● SQL combine two tables into one table and add a new column
● 如何在将学生成绩排序后,再加一个rank域● 请教oracle select top 10 from ... order by desc
● correlated subquery● MySQL语句请教
● aks a simple SQL question● 问个sql的问题吧,搞不出来了. (转载)
● 请教高手，包子谢● 这个问题可以用SQL 实现吗?
● 请问T-SQL中Group By之后怎么找到特定的record● query questions
● better solution for cross table query in sql?● sql面试题目求指点
相关话题的讨论汇总
话题: salary话题: select话题: where话题: count话题: join
进入Database版参与讨论
1 (共1页)
d***e
发帖数: 793
1
有个salary table：department id， employeeid，salary
问每个depart的top 3 salary的employee
不让用analytical methods，比如row_number啥的，mysql也没那东西。
反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
，不让用我只会用temp table然后delete。
这题是典型的group wise max problem，做过就会了。
这个stackoverflow的相关问题
http://stackoverflow.com/questions/755918/simple-query-to-grab-
B*****g
发帖数: 34098
2
贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
不让用partition by，咱还有传统大法self join:
SELECT s1.departmentid， s1.employeeid，s1.salary
FROM salary s1, salary s2
WHERE s1.departmentid = s2.departmentid
AND s1.employeeid != s2.employeeid
GROUP BY s1.departmentid， s1.employeeid，s1.salary
HAVING ? < N
N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
结论：
没有partition by，就用self join

over

【在 d***e 的大作中提到】
: 有个salary table：department id， employeeid，salary
: 问每个depart的top 3 salary的employee
: 不让用analytical methods，比如row_number啥的，mysql也没那东西。
: 反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
: ，不让用我只会用temp table然后delete。
: 这题是典型的group wise max problem，做过就会了。
: 这个stackoverflow的相关问题
: http://stackoverflow.com/questions/755918/simple-query-to-grab-
k*z
发帖数: 4704
3
sas 可以用
where department in (A B C F)
SQL怎么能简单的处理这些 难道只能用OR么？
Where department = A or Department = B .....

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
p********n
发帖数: 11
4
Assuming no tie in salary in each department:
The subquery finds how many ppl have higher salary, and the guy has most
salary will not have a record returned from the subquery.
select S.*
from Salary S
left join
(
select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
from Salary as S1 join Salary S2
on S1.DepartmentID = S2.DepartmentID
and S1.EmployeeID <> S2.EmployeeID
and S1.Salary < S2.Salary
group by S1.DepartmentID, S1.EmployeeID
) as Ranking
on S.DepartmentID = Ranking.DepartmentID
and S.EmployeeID = Ranking.EmployeeID
where isnull(Ranking.Cnt, 0) < 3
In general, use window functions whenever possible unless there is no window
function or performance degrades (the performance should be better with
window functions in most cases).
y****w
发帖数: 3747
5
~=
n年前考研必考，用非递归解决明显递归问题。大题10分。

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
B*****g
发帖数: 34098
6
我是达到了国内研究生入学水平吗？

【在 y****w 的大作中提到】
: ~=
: n年前考研必考，用非递归解决明显递归问题。大题10分。
y****w
发帖数: 3747
7
博导了，嗑

【在 B*****g 的大作中提到】
: 我是达到了国内研究生入学水平吗？
d***e
发帖数: 793
8
Nice. Thanks.

【在 p********n 的大作中提到】
: Assuming no tie in salary in each department:
: The subquery finds how many ppl have higher salary, and the guy has most
: salary will not have a record returned from the subquery.
: select S.*
: from Salary S
: left join
: (
: select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
: from Salary as S1 join Salary S2
: on S1.DepartmentID = S2.DepartmentID
m*********e
发帖数: 533
9
select rownumber over (partition by department id order by salary),
employeeid, salary from salary
whhere rownumber over (partition by department id order by salary) < = 3

over

【在 d***e 的大作中提到】
: 有个salary table：department id， employeeid，salary
: 问每个depart的top 3 salary的employee
: 不让用analytical methods，比如row_number啥的，mysql也没那东西。
: 反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
: ，不让用我只会用temp table然后delete。
: 这题是典型的group wise max problem，做过就会了。
: 这个stackoverflow的相关问题
: http://stackoverflow.com/questions/755918/simple-query-to-grab-
c*****d
发帖数: 6045
10
不让用analytical methods

【在 m*********e 的大作中提到】
: select rownumber over (partition by department id order by salary),
: employeeid, salary from salary
: whhere rownumber over (partition by department id order by salary) < = 3
:
: over
相关主题
● 请教高手，包子谢● 请教一个问题
● 请问T-SQL中Group By之后怎么找到特定的record● SQL combine two tables into one table and add a new column
● better solution for cross table query in sql?● 请教oracle select top 10 from ... order by desc
进入Database版参与讨论
c*****d
发帖数: 6045
11
思路对头，细节有问题
Oracle可以这么写
select * from (
SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
FROM scott.emp s1, scott.emp s2
WHERE s1.deptno(+) = s2.deptno
AND s1.sal >= s2.sal
GROUP BY s1.deptno， s1.empno，s1.sal)
where ranks <= 3
order by deptno;

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
c*******r
发帖数: 3289
12
楼上
WHERE s1.deptno(+) = s2.deptno
是什么冬冬?
B*****g
发帖数: 34098
13
事实证明DBA不会写SQL的人很多

【在 c*******r 的大作中提到】
: 楼上
: WHERE s1.deptno(+) = s2.deptno
: 是什么冬冬?
c*****d
发帖数: 6045
14
oracle out join

【在 c*******r 的大作中提到】
: 楼上
: WHERE s1.deptno(+) = s2.deptno
: 是什么冬冬?
c*****d
发帖数: 6045
15
还好，我算会写sql的dba, LOL

【在 B*****g 的大作中提到】
: 事实证明DBA不会写SQL的人很多
c*******r
发帖数: 3289
16

如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
如果有现成的table，我倒是想test一下。

【在 c*****d 的大作中提到】
: 思路对头，细节有问题
: Oracle可以这么写
: select * from (
: SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
: FROM scott.emp s1, scott.emp s2
: WHERE s1.deptno(+) = s2.deptno
: AND s1.sal >= s2.sal
: GROUP BY s1.deptno， s1.empno，s1.sal)
: where ranks <= 3
: order by deptno;
l******b
发帖数: 39
17

sal
对？
You are right. The above code returns the bottom 3 instead of top 3.
However, if you want to solve the problem by thinking like rank>=(total-3),
you're going to a dead end.
Simply changing the above code
AND s1.sal >= s2.sal
to
AND s1.sal <= s2.sal
will give you the top 3.
I changed the above code a litter bit(I think it's better put s1.id <>s2.id
condition, coz a row doen't have to compare with itself)
I ran the query against the sample table EMPLOYEES
on Oracle's sample schema HR and here's the result.
SELECT * FROM(
SELECT e1.department_id, e1.employee_id, e1.salary,
count(e2.department_id) AS rnk
-- count(e2.department_id) instead of count(1), if there's none
-- else bigger than its own, its salary ranks as 0
FROM employees e1
LEFT JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.salary < e2.salary AND
e1.employee_id <>e2.employee_id
-- no comparison with itself
GROUP BY e1.department_id, e1.employee_id, e1.salary )
WHERE rnk <= 2 -- ranks as 0, 1, 2
ORDER BY department_id, salary DESC ;
-------------------------------------------------
10 200 4400 0
20 201 13000 0
20 202 6000 1
30 114 11000 0
30 269 9000 1
30 115 3100 2
40 203 6500 0
50 121 8200 0
50 120 8000 1
50 122 7900 2
60 103 9000 0
60 104 6000 1
60 105 4800 2
60 106 4800 2
70 204 10000 0
80 145 14000 0
80 146 13500 1
80 147 12000 2
90 100 24000 0
90 101 17000 1
90 102 17000 1
100 108 12008 0
100 109 9000 1
100 110 8200 2
110 205 12008 0
110 206 8300 1
(null) 178 7000 0

【在 c*******r 的大作中提到】
:
: 如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
: 〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
: 如果有现成的table，我倒是想test一下。
y*****g
发帖数: 677
18
这道题，是不是隐含employee id is unique?
不会有一个employee id 和 不同的department id 配对吧，
一个人不会发两次工资，在两个系里。
我这不理解对不对？
B*****g
发帖数: 34098
19
这道题的关键就是fb用mysql没有partition by

【在 y*****g 的大作中提到】
: 这道题，是不是隐含employee id is unique?
: 不会有一个employee id 和 不同的department id 配对吧，
: 一个人不会发两次工资，在两个系里。
: 我这不理解对不对？
y*****g
发帖数: 677
20
我这不是在琢磨嘛， 这里有没有mysql 的解法？
相关主题
● MySQL语句请教● query questions
● 问个sql的问题吧,搞不出来了. (转载)● sql面试题目求指点
● 这个问题可以用SQL 实现吗?● MySQL怎样查询各科前三名的分数？
进入Database版参与讨论
B*****g
发帖数: 34098
21
self join

【在 y*****g 的大作中提到】
: 我这不是在琢磨嘛， 这里有没有mysql 的解法？
y*****g
发帖数: 677
22
self join 怎么给出前三个呀？
最大最小可以，
B*****g
发帖数: 34098
23
count比自己小的

【在 y*****g 的大作中提到】
: self join 怎么给出前三个呀？
: 最大最小可以，
m*******g
发帖数: 3044
24
我写的这个SELF JOIN 能出来结果吗?
select departmentid， employeeid，salary from S1
where ( select count(*) from S1 as f
where f. departmentid = S1.departmentid and f.salary > S1.salary)<=3
B*****g
发帖数: 34098
25
yes

【在 m*******g 的大作中提到】
: 我写的这个SELF JOIN 能出来结果吗?
: select departmentid， employeeid，salary from S1
: where ( select count(*) from S1 as f
: where f. departmentid = S1.departmentid and f.salary > S1.salary)<=3
y*****g
发帖数: 677
26
高人啊。 我回头拿数据试试。
y*****g
发帖数: 677
27
我觉得给不出结果， 只是给了任意的三个互相比的大的工资， 而不是最大的三个工资
。 我明天试试再说
c*****d
发帖数: 6045
28
肯定可以选出最大的三个工资

【在 y*****g 的大作中提到】
: 我觉得给不出结果， 只是给了任意的三个互相比的大的工资， 而不是最大的三个工资
: 。 我明天试试再说
c*****d
发帖数: 6045
29
恩，我写反了
不用total - 3
从s1.sal >= s2.sal改成s1.sal < s2.sal就可以了
就是查询比自己工资高的有几个人
工资最高的人，count(1) -- 0
工资次高的人，count(1) -- 1
最后的语句是
select * from (
SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
FROM scott.emp s1, scott.emp s2
WHERE s1.deptno(+) = s2.deptno
AND s1.sal >= s2.sal
GROUP BY s1.deptno， s1.empno，s1.sal)
where ranks < 3
order by deptno;

sal
对？

【在 c*******r 的大作中提到】
:
: 如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
: 〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
: 如果有现成的table，我倒是想test一下。
B*****g
发帖数: 34098
30
亲，有一种东西叫having好吧

【在 c*****d 的大作中提到】
: 恩，我写反了
: 不用total - 3
: 从s1.sal >= s2.sal改成s1.sal < s2.sal就可以了
: 就是查询比自己工资高的有几个人
: 工资最高的人，count(1) -- 0
: 工资次高的人，count(1) -- 1
: 最后的语句是
: select * from (
: SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
: FROM scott.emp s1, scott.emp s2
相关主题
● 一个SQL的问题(Groupby和Having的问题）● To get the 2nd, 3rd, 4th largest value
● 请教一个sql query● 如何在将学生成绩排序后,再加一个rank域
● 问一道sql的面试题啊 自己实在是没想出来● correlated subquery
进入Database版参与讨论
c*****d
发帖数: 6045
31
en，having count(1) < 3
写程序的时候很容易一根筋

【在 B*****g 的大作中提到】
: 亲，有一种东西叫having好吧
y*****g
发帖数: 677
32
Credit goes to phoebusmen (Apollo),
最简单得解法，　对阿波罗的秘籍作了一些改动：
用left join　，　计数器不存在　ＮＵＬＬ，
外层不许要再次 ＪＯＩＮ，
终极解法:
select dep_id,emp_id,salary
from
(select s1.dep_id, s1.emp_id, s1.salary, count(*) as rank
from salary s1 left join salary s2
on s1.dep_id=s2.dep_id and s1.emp_id <> s2.emp_id and s1.salary <
s2.salary group by 1,2,3 having count(*) < 3
order by 1,3 desc
) as sal;
--------+--------+--------+
| dep_id | emp_id | salary |
+--------+--------+--------+
| 1 | 3 | 10660 |
| 1 | 4 | 1000 |
| 1 | 2 | 200 |
| 2 | 5 | 7700 |
| 2 | 7 | 700 |
| 2 | 6 | 300 |
| 3 | 9 | 10660 |
| 3 | 10 | 10000 |
| 3 | 11 | 1660 |
+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from salary order by 1,3 desc;
+--------+--------+--------+
| dep_id | emp_id | salary |
+--------+--------+--------+
| 1 | 3 | 10660 |
| 1 | 4 | 1000 |
| 1 | 2 | 200 |
| 1 | 1 | 100 |
| 2 | 5 | 7700 |
| 2 | 7 | 700 |
| 2 | 6 | 300 |
| 2 | 8 | 0 |
| 3 | 9 | 10660 |
| 3 | 10 | 10000 |
| 3 | 11 | 1660 |
| 3 | 12 | 100 |
+--------+--------+--------+
12 rows in set (0.00 sec)
coolbid and bj , 你们思路都对，:-)
ET
发帖数: 10701
33
zan self join.

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
d***e
发帖数: 793
34
有个salary table：department id， employeeid，salary
问每个depart的top 3 salary的employee
不让用analytical methods，比如row_number啥的，mysql也没那东西。
反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
，不让用我只会用temp table然后delete。
这题是典型的group wise max problem，做过就会了。
这个stackoverflow的相关问题
http://stackoverflow.com/questions/755918/simple-query-to-grab-
B*****g
发帖数: 34098
35
贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
不让用partition by，咱还有传统大法self join:
SELECT s1.departmentid， s1.employeeid，s1.salary
FROM salary s1, salary s2
WHERE s1.departmentid = s2.departmentid
AND s1.employeeid != s2.employeeid
GROUP BY s1.departmentid， s1.employeeid，s1.salary
HAVING ? < N
N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
结论：
没有partition by，就用self join

over

【在 d***e 的大作中提到】
: 有个salary table：department id， employeeid，salary
: 问每个depart的top 3 salary的employee
: 不让用analytical methods，比如row_number啥的，mysql也没那东西。
: 反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
: ，不让用我只会用temp table然后delete。
: 这题是典型的group wise max problem，做过就会了。
: 这个stackoverflow的相关问题
: http://stackoverflow.com/questions/755918/simple-query-to-grab-
k*z
发帖数: 4704
36
sas 可以用
where department in (A B C F)
SQL怎么能简单的处理这些 难道只能用OR么？
Where department = A or Department = B .....

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
p********n
发帖数: 11
37
Assuming no tie in salary in each department:
The subquery finds how many ppl have higher salary, and the guy has most
salary will not have a record returned from the subquery.
select S.*
from Salary S
left join
(
select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
from Salary as S1 join Salary S2
on S1.DepartmentID = S2.DepartmentID
and S1.EmployeeID <> S2.EmployeeID
and S1.Salary < S2.Salary
group by S1.DepartmentID, S1.EmployeeID
) as Ranking
on S.DepartmentID = Ranking.DepartmentID
and S.EmployeeID = Ranking.EmployeeID
where isnull(Ranking.Cnt, 0) < 3
In general, use window functions whenever possible unless there is no window
function or performance degrades (the performance should be better with
window functions in most cases).
y****w
发帖数: 3747
38
~=
n年前考研必考，用非递归解决明显递归问题。大题10分。

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
B*****g
发帖数: 34098
39
我是达到了国内研究生入学水平吗？

【在 y****w 的大作中提到】
: ~=
: n年前考研必考，用非递归解决明显递归问题。大题10分。
y****w
发帖数: 3747
40
博导了，嗑

【在 B*****g 的大作中提到】
: 我是达到了国内研究生入学水平吗？
相关主题
● correlated subquery● 请问T-SQL中Group By之后怎么找到特定的record
● aks a simple SQL question● better solution for cross table query in sql?
● 请教高手，包子谢● 请教一个问题
进入Database版参与讨论
d***e
发帖数: 793
41
Nice. Thanks.

【在 p********n 的大作中提到】
: Assuming no tie in salary in each department:
: The subquery finds how many ppl have higher salary, and the guy has most
: salary will not have a record returned from the subquery.
: select S.*
: from Salary S
: left join
: (
: select S1.DepartmentID, S1.EmployeeID, count(*) as Cnt
: from Salary as S1 join Salary S2
: on S1.DepartmentID = S2.DepartmentID
m*********e
发帖数: 533
42
select rownumber over (partition by department id order by salary),
employeeid, salary from salary
whhere rownumber over (partition by department id order by salary) < = 3

over

【在 d***e 的大作中提到】
: 有个salary table：department id， employeeid，salary
: 问每个depart的top 3 salary的employee
: 不让用analytical methods，比如row_number啥的，mysql也没那东西。
: 反正我没答出来，当然我属于比较菜的。以前用sqlserver，我都是用row_number over
: ，不让用我只会用temp table然后delete。
: 这题是典型的group wise max problem，做过就会了。
: 这个stackoverflow的相关问题
: http://stackoverflow.com/questions/755918/simple-query-to-grab-
c*****d
发帖数: 6045
43
不让用analytical methods

【在 m*********e 的大作中提到】
: select rownumber over (partition by department id order by salary),
: employeeid, salary from salary
: whhere rownumber over (partition by department id order by salary) < = 3
:
: over
c*****d
发帖数: 6045
44
思路对头，细节有问题
Oracle可以这么写
select * from (
SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
FROM scott.emp s1, scott.emp s2
WHERE s1.deptno(+) = s2.deptno
AND s1.sal >= s2.sal
GROUP BY s1.deptno， s1.empno，s1.sal)
where ranks <= 3
order by deptno;

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
c*******r
发帖数: 3289
45
楼上
WHERE s1.deptno(+) = s2.deptno
是什么冬冬?
B*****g
发帖数: 34098
46
事实证明DBA不会写SQL的人很多

【在 c*******r 的大作中提到】
: 楼上
: WHERE s1.deptno(+) = s2.deptno
: 是什么冬冬?
c*****d
发帖数: 6045
47
oracle out join

【在 c*******r 的大作中提到】
: 楼上
: WHERE s1.deptno(+) = s2.deptno
: 是什么冬冬?
c*****d
发帖数: 6045
48
还好，我算会写sql的dba, LOL

【在 B*****g 的大作中提到】
: 事实证明DBA不会写SQL的人很多
c*******r
发帖数: 3289
49

如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
如果有现成的table，我倒是想test一下。

【在 c*****d 的大作中提到】
: 思路对头，细节有问题
: Oracle可以这么写
: select * from (
: SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
: FROM scott.emp s1, scott.emp s2
: WHERE s1.deptno(+) = s2.deptno
: AND s1.sal >= s2.sal
: GROUP BY s1.deptno， s1.empno，s1.sal)
: where ranks <= 3
: order by deptno;
l******b
发帖数: 39
50

sal
对？
You are right. The above code returns the bottom 3 instead of top 3.
However, if you want to solve the problem by thinking like rank>=(total-3),
you're going to a dead end.
Simply changing the above code
AND s1.sal >= s2.sal
to
AND s1.sal <= s2.sal
will give you the top 3.
I changed the above code a litter bit(I think it's better put s1.id <>s2.id
condition, coz a row doen't have to compare with itself)
I ran the query against the sample table EMPLOYEES
on Oracle's sample schema HR and here's the result.
SELECT * FROM(
SELECT e1.department_id, e1.employee_id, e1.salary,
count(e2.department_id) AS rnk
-- count(e2.department_id) instead of count(1), if there's none
-- else bigger than its own, its salary ranks as 0
FROM employees e1
LEFT JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.salary < e2.salary AND
e1.employee_id <>e2.employee_id
-- no comparison with itself
GROUP BY e1.department_id, e1.employee_id, e1.salary )
WHERE rnk <= 2 -- ranks as 0, 1, 2
ORDER BY department_id, salary DESC ;
-------------------------------------------------
10 200 4400 0
20 201 13000 0
20 202 6000 1
30 114 11000 0
30 269 9000 1
30 115 3100 2
40 203 6500 0
50 121 8200 0
50 120 8000 1
50 122 7900 2
60 103 9000 0
60 104 6000 1
60 105 4800 2
60 106 4800 2
70 204 10000 0
80 145 14000 0
80 146 13500 1
80 147 12000 2
90 100 24000 0
90 101 17000 1
90 102 17000 1
100 108 12008 0
100 109 9000 1
100 110 8200 2
110 205 12008 0
110 206 8300 1
(null) 178 7000 0

【在 c*******r 的大作中提到】
:
: 如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
: 〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
: 如果有现成的table，我倒是想test一下。
相关主题
● SQL combine two tables into one table and add a new column● 问个sql的问题吧,搞不出来了. (转载)
● 请教oracle select top 10 from ... order by desc● 这个问题可以用SQL 实现吗?
● MySQL语句请教● query questions
进入Database版参与讨论
y*****g
发帖数: 677
51
这道题，是不是隐含employee id is unique?
不会有一个employee id 和 不同的department id 配对吧，
一个人不会发两次工资，在两个系里。
我这不理解对不对？
B*****g
发帖数: 34098
52
这道题的关键就是fb用mysql没有partition by

【在 y*****g 的大作中提到】
: 这道题，是不是隐含employee id is unique?
: 不会有一个employee id 和 不同的department id 配对吧，
: 一个人不会发两次工资，在两个系里。
: 我这不理解对不对？
y*****g
发帖数: 677
53
我这不是在琢磨嘛， 这里有没有mysql 的解法？
B*****g
发帖数: 34098
54
self join

【在 y*****g 的大作中提到】
: 我这不是在琢磨嘛， 这里有没有mysql 的解法？
y*****g
发帖数: 677
55
self join 怎么给出前三个呀？
最大最小可以，
B*****g
发帖数: 34098
56
count比自己小的

【在 y*****g 的大作中提到】
: self join 怎么给出前三个呀？
: 最大最小可以，
m*******g
发帖数: 3044
57
我写的这个SELF JOIN 能出来结果吗?
select departmentid， employeeid，salary from S1
where ( select count(*) from S1 as f
where f. departmentid = S1.departmentid and f.salary > S1.salary)<=3
B*****g
发帖数: 34098
58
yes

【在 m*******g 的大作中提到】
: 我写的这个SELF JOIN 能出来结果吗?
: select departmentid， employeeid，salary from S1
: where ( select count(*) from S1 as f
: where f. departmentid = S1.departmentid and f.salary > S1.salary)<=3
y*****g
发帖数: 677
59
高人啊。 我回头拿数据试试。
y*****g
发帖数: 677
60
我觉得给不出结果， 只是给了任意的三个互相比的大的工资， 而不是最大的三个工资
。 我明天试试再说
相关主题
● sql面试题目求指点● 请教一个sql query
● MySQL怎样查询各科前三名的分数？● 问一道sql的面试题啊 自己实在是没想出来
● 一个SQL的问题(Groupby和Having的问题）● To get the 2nd, 3rd, 4th largest value
进入Database版参与讨论
c*****d
发帖数: 6045
61
肯定可以选出最大的三个工资

【在 y*****g 的大作中提到】
: 我觉得给不出结果， 只是给了任意的三个互相比的大的工资， 而不是最大的三个工资
: 。 我明天试试再说
c*****d
发帖数: 6045
62
恩，我写反了
不用total - 3
从s1.sal >= s2.sal改成s1.sal < s2.sal就可以了
就是查询比自己工资高的有几个人
工资最高的人，count(1) -- 0
工资次高的人，count(1) -- 1
最后的语句是
select * from (
SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
FROM scott.emp s1, scott.emp s2
WHERE s1.deptno(+) = s2.deptno
AND s1.sal >= s2.sal
GROUP BY s1.deptno， s1.empno，s1.sal)
where ranks < 3
order by deptno;

sal
对？

【在 c*******r 的大作中提到】
:
: 如果rank是s1.sal>=s2.sal的计数器，rank<=3的意义是只有3个以下的记录存在s1.sal
: 〉=s2.sal的情况，这就变成工资最低的3位了。所以应该rank>= (total - 3),对不对？
: 如果有现成的table，我倒是想test一下。
B*****g
发帖数: 34098
63
亲，有一种东西叫having好吧

【在 c*****d 的大作中提到】
: 恩，我写反了
: 不用total - 3
: 从s1.sal >= s2.sal改成s1.sal < s2.sal就可以了
: 就是查询比自己工资高的有几个人
: 工资最高的人，count(1) -- 0
: 工资次高的人，count(1) -- 1
: 最后的语句是
: select * from (
: SELECT s1.deptno， s1.empno，s1. sal, count(1) ranks
: FROM scott.emp s1, scott.emp s2
c*****d
发帖数: 6045
64
en，having count(1) < 3
写程序的时候很容易一根筋

【在 B*****g 的大作中提到】
: 亲，有一种东西叫having好吧
y*****g
发帖数: 677
65
Credit goes to phoebusmen (Apollo),
最简单得解法，　对阿波罗的秘籍作了一些改动：
用left join　，　计数器不存在　ＮＵＬＬ，
外层不许要再次 ＪＯＩＮ，
终极解法:
select dep_id,emp_id,salary
from
(select s1.dep_id, s1.emp_id, s1.salary, count(*) as rank
from salary s1 left join salary s2
on s1.dep_id=s2.dep_id and s1.emp_id <> s2.emp_id and s1.salary <
s2.salary group by 1,2,3 having count(*) < 3
order by 1,3 desc
) as sal;
--------+--------+--------+
| dep_id | emp_id | salary |
+--------+--------+--------+
| 1 | 3 | 10660 |
| 1 | 4 | 1000 |
| 1 | 2 | 200 |
| 2 | 5 | 7700 |
| 2 | 7 | 700 |
| 2 | 6 | 300 |
| 3 | 9 | 10660 |
| 3 | 10 | 10000 |
| 3 | 11 | 1660 |
+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from salary order by 1,3 desc;
+--------+--------+--------+
| dep_id | emp_id | salary |
+--------+--------+--------+
| 1 | 3 | 10660 |
| 1 | 4 | 1000 |
| 1 | 2 | 200 |
| 1 | 1 | 100 |
| 2 | 5 | 7700 |
| 2 | 7 | 700 |
| 2 | 6 | 300 |
| 2 | 8 | 0 |
| 3 | 9 | 10660 |
| 3 | 10 | 10000 |
| 3 | 11 | 1660 |
| 3 | 12 | 100 |
+--------+--------+--------+
12 rows in set (0.00 sec)
coolbid and bj , 你们思路都对，:-)
ET
发帖数: 10701
66
zan self join.

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
d*******n
发帖数: 109
67
self join 有双手互搏的感觉，用得好的话，天下无敌。
f*****8
发帖数: 2977
68

****************************************************************************
这是我用传统方法做的， 但没法测试：
select a1.deptid, a1.emplid, a1.salary,a2.deptid, a2.emplid, a2.salary,a3.
deptid, a3.emplid, a3.salary
from ps_salary a1, ps_salary a2, ps_salary a3
where a1.salary =(select max(b1.salary) from ps_salary b1 where b1.deptid=a1
.deptid)
and a1.deptid=a2.deptid
and a2.salary =(select max(b2.salary) from ps_salary b2 where b2.deptid=
a2.deptid and b2.salary and a3.deptid=a1.deptid
and a3.salary =(select max(b3.salary) from ps_salary b3 where b3.deptid=
a3.deptid and b3.salary order by a1.deptid
*********************************************************************

【在 B*****g 的大作中提到】
: 贬低FB家程序员SQL知识水平低下的话就不说了，下面说一下咱的解题思路：
: 不让用partition by，咱还有传统大法self join:
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary s1, salary s2
: WHERE s1.departmentid = s2.departmentid
: AND s1.employeeid != s2.employeeid
: GROUP BY s1.departmentid， s1.employeeid，s1.salary
: HAVING ? < N
: N在本题里就是3，？就是数有多上个s2.salary比s1.salary大(如要考虑同salary，也
: 可通过变化？解决）。？可用sum + case解决。没法测试，大家轻拍
l******b
发帖数: 39
69
这题这么久了还讨论啊。呵。
给大家提供一个新思路， 可用于任何DB的ANSI SQL
select e1.employeeid, e1.department_id, e1.salary
from employee e1
where 3 >= (select count(*) from employee e2
where e1.salary < e2.salary) ;
上面排序包括同等薪水并列的， 比如如果有2个都是17000， 并列第二，
都会选出来。
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
145 John Russell 14000
如果不想并列的， 就 改成 where e1.salary <= e2.salary
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
a******t
发帖数: 125
70
新手来凑个热闹， 恳请大师们批判
T-SQL写的, 用subquery
SELECT s1.departmentid， s1.employeeid，s1.salary
FROM salary AS s1
WHERE s1.salary IN
(SELECT TOP (3) s2.salary
FROM salary AS s2
WHERE s1.departmentid= s2.departmentid);
相关主题
● To get the 2nd, 3rd, 4th largest value● aks a simple SQL question
● 如何在将学生成绩排序后,再加一个rank域● 请教高手，包子谢
● correlated subquery● 请问T-SQL中Group By之后怎么找到特定的record
进入Database版参与讨论
B*****g
发帖数: 34098
71
要join，不要用in

【在 a******t 的大作中提到】
: 新手来凑个热闹， 恳请大师们批判
: T-SQL写的, 用subquery
: SELECT s1.departmentid， s1.employeeid，s1.salary
: FROM salary AS s1
: WHERE s1.salary IN
: (SELECT TOP (3) s2.salary
: FROM salary AS s2
: WHERE s1.departmentid= s2.departmentid);
a******t
发帖数: 125
72
subquery和JOIN应该是可以互相换的，我一时还没想起来。
先请问为啥不用IN呢？

【在 B*****g 的大作中提到】
: 要join，不要用in
1 (共1页)
进入Database版参与讨论
相关主题
● sql面试题目求指点● aks a simple SQL question
● MySQL怎样查询各科前三名的分数？● 请教高手，包子谢
● 一个SQL的问题(Groupby和Having的问题）● 请问T-SQL中Group By之后怎么找到特定的record
● 请教一个sql query● better solution for cross table query in sql?
● 问一道sql的面试题啊 自己实在是没想出来● 请教一个问题
● To get the 2nd, 3rd, 4th largest value● SQL combine two tables into one table and add a new column
● 如何在将学生成绩排序后,再加一个rank域● 请教oracle select top 10 from ... order by desc
● correlated subquery● MySQL语句请教
相关话题的讨论汇总
话题: salary话题: select话题: where话题: count话题: join

未名新帖统计// 7月16日

#版面帖数(主题数)
-全站4871 (796)
1Military3777 (569)
2Stock341 (51)
3Joke117 (17)
4History116 (3)
5Automobile100 (9)
6USANews55 (9)
7Midlife45 (1)
8Headline41 (41)
9Dreamer33 (13)
10FleaMarket32 (20)
11Living30 (7)

* 这里只显示发帖超过25的版面，努力灌水吧:-)

历史上的今天

  1. faintcat妹妹看进来～～ 发表于12年前.
  2. NSC, PD 1/7/2007, EB2, ... 发表于11年前.
  3. [FBA求购]MJVE2 758 MJVM2 ... 发表于6年前.
  4. 老生常谈,归与不归 发表于10年前.
  5. 【申请】Seattle西雅图 版版主——申请人... 发表于9年前.
  6. 宝宝出生，头骨骨折，求祝福 发表于9年前.
  7. 求推荐舒缓优美的古典音乐 发表于11年前.
  8. 百分之一的北京人上北大 中国网友愤怒(转载） 发表于10年前.
  9. 新人带狗狗Bailey来报道 发表于12年前.
  10. 全世界最有价值的运动队 发表于10年前.
  11. 请问大切诺基的质量如何 发表于6年前.
  12. TNND，军版全是BKC 发表于15年前.
  13. Inception 发表于12年前.
  14. 微软的有些家属可真恶心，为了卖保险脸都不要了 发表于10年前.
  15. 每周坐高铁的苦逼来说说感受吧!! 发表于9年前.
除非另有声明，本站内容采用Creative Commons BY-NC-SA 3.0协议进行许可，转载请注明来自未名观察 - 隐私政策2011-07-24 10:06:12由admin编辑