SQL在按不同顺序按多列排序后选择奇数行
SQL在按不同顺序按多列排序后选择奇数行
提问于 2021-09-09 00:32:05
我有一个学生表(id，成绩，系)和部门表(id，name)。我试图在查询中添加某些条件。
School_dept
+---------+-------------+
| dept_id | name |
+---------+-------------+
| 1 | Admin |
+---------+-------------+
| 2 | Chemistry |
+---------+-------------+
| 3 | Physics |
+---------+-------------+
| 4 | Biology |
+---------+-------------+
| 5 | Mathematics |
+---------+-------------+学生
+------------+-------+------------+
| student_id | score | department |
+------------+-------+------------+
| 26 | 11 | 4 |
+------------+-------+------------+
| 34 | 11 | 3 |
+------------+-------+------------+
| 76 | 11 | 2 |
+------------+-------+------------+
| 49 | 11 | 1 |
+------------+-------+------------+
| 38 | 11 | 5 |
+------------+-------+------------+- 选择少于5名学生的所有school_depts
- 按学生总分降序对学校各部门进行排序。如果有一个领带，那么得到最高数量的学生在部门将是第一；如果仍然有一个平分，那么最小的dept_id应该是第一。
- 只考虑奇数行，排除偶数行。
我的尝试
SELECT * FROM (
SELECT * , ROW_NUMBER() OVER() AS ROW_ID FROM (
SELECT dept_id, name, count(E.student_id) as total_students, SUM(score) as total_score
from Students E
LEFT JOIN school_dept D on dept_id=student_id
group by 1,2
) as O
WHERE total_students<3
order by total_score desc, total_students desc, dept_id asc
) as U
WHERE ROW_ID %2 <>0预期产出为
name, total_students, total_score
Admin,1,11
Physics1,11
Mathematics,1,11http://sqlfiddle.com/#!17/248eb8/2
回答 3
Stack Overflow用户
发布于 2021-09-09 04:09:29
我认为下面的查询应该有效
select *,
Rank() over (order by Score desc,TotalStudent desc,dept_id asc) as Serial
from
(
select d.dept_id,d.Name,COUNT(student_id) TotalStudent,sum(Score) Score
from Students s
left join School_dept d on s.department=d.dept_id
where d.dept_id % 2 <> 0
group by d.Name,d.dept_id
) a where TotalStudent < 5Stack Overflow用户
发布于 2021-09-09 05:30:06
我认为您的查询中存在一个联接问题(加入student_id和dept_id)，并且您正在为少于的三名学生进行选择(但要求少于5学生)。
请试用以下方法：
select department as "name", total_students, total_score
from
(
select *, row_number() over (order by total_score desc, total_students desc, dept_id) row_id
from
(
select sum(score) total_score, count(student_id) total_students, sd.name department, sd.dept_id
from students s
join school_dept sd on s.department = sd.dept_id
group by 3,4
having count(student_id) < 5
)t
)tt
where row_id%2 <> 0请参阅SQL 这里。
Stack Overflow用户
发布于 2021-09-09 05:39:38
由于School_dept是系的父表，所以School_dept离开将加入学生。因为一个系可能没有学生，所以剩下的加入是完美的。但是如果考虑那些有学生的部门，那么就使用内部连接。
-- SQL SERVER (v2017)
SELECT p.dept_name "Name", p.total_students, p.total_score
FROM (SELECT *
, ROW_NUMBER() OVER (ORDER BY t.total_score DESC, t.total_students DESC, t.dept_id) row_num
FROM (SELECT sd.dept_id
, MAX(sd.name) dept_name
, COUNT(s.student_id) total_students
, SUM(s.score) total_score
FROM School_dept sd
LEFT JOIN Students s
ON sd.dept_id = s.department
GROUP BY sd.dept_id
HAVING COUNT(s.student_id) < 5) t) p
WHERE (p.row_num % 2) != 0;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69110964
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号