исключая дубликаты, которые ниже максимальных значений в SQL

#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 была обновлена. Смотрите второй запрос, чтобы также правильно обрабатывать подсчет только строк «ДА». Учитывая текущие данные (все строки «ДА»), два запроса дают один и тот же результат.