#sql #oracle
Вопрос:
У меня есть следующая простая таблица (Таблица 1), где каждая строка-идентификатор студента и его имя, и у каждого студента есть один или несколько выигрышей (выигрышей). Я хотел бы вывести: Идентификатор студента, Имя студента, количество побед, отсортированных по количеству побед (по убыванию), а затем идентификатор студента (по возрастанию), исключая тех студентов, у которых одинаковое количество побед меньше максимального количества побед (т. е. 5). Другими словами, у Лиззи и Марка одинаковое количество побед, а 3 меньше 5, поэтому в выводе будут исключены два студента, Лиззи и Марк.
Из комментариев: «Бетти, Дэвид и Кэти также должны быть исключены».
Таблица 1:
student_id | имя студента | Победы |
---|---|---|
1 | Джон | ДА |
1 | Джон | ДА |
1 | Джон | ДА |
1 | Джон | ДА |
1 | Джон | ДА |
2 | Брэндон | ДА |
2 | Брэндон | ДА |
2 | Брэндон | ДА |
2 | Брэндон | ДА |
2 | Брэндон | ДА |
3 | Лиззи | ДА |
3 | Лиззи | ДА |
3 | Лиззи | ДА |
4 | Марк | ДА |
4 | Марк | ДА |
4 | Марк | ДА |
5 | Бетти | ДА |
6 | Дэвид | ДА |
7 | Кэтти | ДА |
8 | Джо | ДА |
8 | Джо | ДА |
Желаемый результат:
student_id | имя студента | cnt_wins |
---|---|---|
1 | Джон | 5 |
2 | Брэндон | 5 |
8 | Джо | 2 |
Вот мой SQL в Oracle. Я не могу понять, что пошло не так. В журнале говорится: «(ВЫБЕРИТЕ b.cnt_wins, количество(b.student_id) имеет слишком много значений».
WITH st_cte AS
(SELECT student_id, student_name, count(wins) cnt_wins
FROM Table1
GROUP BY student_id, student_name
ORDER BY count(wins) DESC, student_id)
SELECT *
FROM st_cte a
WHERE a.cnt_wins not in
(SELECT b.cnt_wins, count(b.student_id)
FROM st_cte b
WHERE b.cnt_wins <
(SELECT max(c.cnt_wins) FROM st_cte c)
GROUP BY b.cnt_wins
HAVING count(b.student_id) > 1);
Комментарии:
1. Отметьте свой вопрос в базе данных, которую вы используете.
2. Непонятно, почему бы вам также не исключить Бетти, Дэвида и Кэти, поскольку у них тоже одинаковое количество побед и с количеством меньше максимального. Поэтому я просто предположил, что вы хотели, чтобы в результате был кто-то с одной победой.
3. Ты прав. Извините, Бетти, Дэвида и Кэти следует исключить.
Ответ №1:
В поле » в » выбрано слишком много значений:
WHERE a.cnt_wins -- 1 value
not in
(SELECT b.cnt_wins, count(b.student_id) -- 2 values
FROM st_cte b
вы должны либо сделать :
WHERE a.cnt_wins not in
(SELECT b.cnt_wins
FROM st_cte ...
или
WHERE (a.cnt_wins, count(something)) not in
(SELECT b.cnt_wins, count(b.student_id)
FROM st_cte ...
Ответ №2:
Обновлено на основе обновленных требований…
Требование было неоднозначным в том, что Бетти, Дэвид и Кэти, по-видимому, также соответствуют критериям, которые должны быть удалены из результата. Это требование было уточнено, и эти строки должны были быть удалены.
Логика была добавлена, чтобы разрешить только все max_cnt
строки, а также любые учащиеся с уникальным количеством.
Также обратите внимание, что если wins
может быть любое другое ненулевое значение, COUNT(wins)
это неверно.
Учитывая все это, возможно, что-то вроде этого является отправной точкой:
WITH cte AS (
SELECT student_id, student_name
, COUNT(wins) cnt_wins
, MAX(COUNT(wins)) OVER () AS max_cnt
FROM Table1
GROUP BY student_id, student_name
)
, cte2 AS (
SELECT cte.*
, COUNT(*) OVER (PARTITION BY cnt_wins) AS cnt_students
FROM cte
)
SELECT student_id, student_name, cnt_wins
FROM cte2
WHERE max_cnt = cnt_wins
OR cnt_students = 1
ORDER BY cnt_wins DESC, student_id
;
и для обработки wins
этого могут быть другие ненулевые значения:
WITH cte AS (
SELECT student_id, student_name
, COUNT(CASE WHEN wins = 'YES' THEN 1 END) cnt_wins
, MAX(COUNT(CASE WHEN wins = 'YES' THEN 1 END)) OVER () AS max_cnt
FROM Table1
GROUP BY student_id, student_name
)
, cte2 AS (
SELECT cte.*
, COUNT(*) OVER (PARTITION BY cnt_wins) AS cnt_students
FROM cte
)
SELECT student_id, student_name, cnt_wins
FROM cte2
WHERE max_cnt = cnt_wins
OR cnt_students = 1
ORDER BY cnt_wins DESC, student_id
;
Результат (с данными для проверки нового требования, один студент (Джо) с уникальным количеством (2)):
ИДЕНТИФИКАТОР СТУДЕНТА | ИМЯ СТУДЕНТА | CNT_WINS |
---|---|---|
1 | Джон | 5 |
2 | Брэндон | 5 |
8 | Джо | 2 |
Установка:
CREATE TABLE table1 (
Student_ID int
, Student_Name VARCHAR2(20)
, Wins VARCHAR2(10)
);
BEGIN
-- Assume only wins are stored.
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');
INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');
INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');
INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');
INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');
INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');
INSERT INTO table1 VALUES ( 5, 'Betty', 'YES');
INSERT INTO table1 VALUES ( 6, 'David', 'YES');
INSERT INTO table1 VALUES ( 7, 'Cathy', 'YES');
INSERT INTO table1 VALUES ( 8, 'Joe', 'YES');
INSERT INTO table1 VALUES ( 8, 'Joe', 'YES');
END;
/
Исправление к исходному запросу в вопросе:
WITH st_cte AS
(SELECT student_id, student_name, count(wins) cnt_wins
FROM Table1
GROUP BY student_id, student_name
ORDER BY count(wins) DESC, student_id
)
SELECT *
FROM st_cte a
WHERE a.cnt_wins not in
(SELECT b.cnt_wins
FROM st_cte b
WHERE b.cnt_wins < (SELECT max(c.cnt_wins) FROM st_cte c)
GROUP BY b.cnt_wins
HAVING count(b.student_id) > 1
)
;
Комментарии:
1. Ты прав. Извините, Бетти, Дэвида и Кэти следует исключить.
2. Почему вы сказали: «если выигрыши могут быть любым другим ненулевым значением, подсчет(выигрышей) неверен»? @Джон Армстронг
3. Предположим, что у Бетти, Дэвида и Кэти разное количество выигрышей, которые в конечном итоге должны быть включены, как мне изменить ваши коды? Спасибо!
4. @SilverSpringbb, потому
COUNT
что будут учитываться любые ненулевые значения, а не только те, которые «ДА». Второй запрос, который я показал, обрабатывает подсчет только тех строк, которые являются «ДА». Удалениеcnt_wins = 1
приведет к удалению других неправильных строк.5. Логика @SilverSpringbb была обновлена. Смотрите второй запрос, чтобы также правильно обрабатывать подсчет только строк «ДА». Учитывая текущие данные (все строки «ДА»), два запроса дают один и тот же результат.