SQL将多个列按不同顺序排序后选择奇数行

Question

我有一个student表(id, score, department)和departments表(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与最小的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,11

http://sqlfiddle.com/!17/248eb8/2

Answer 1

我认为在您的查询中存在连接问题(加入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 Fiddle.

Answer 2

由于School_dept是系的父表，所以join将使School_dept左加入student。因为一个系可能没有学生，所以左加入是完美的。但是如果考虑那些有学生的院系，则使用INNER JOIN。

-- 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;

请从url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=abf4b40f692d085b98054aa9c9a1dcc7查看

Answer 3

我认为下面的查询应该可以工作

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 < 5