Блог

Прежде всего, я прошу прощения, если мой вопрос неясен, но довольно сложно выразить его в одном коротком предложении. В принципе, у меня есть таблица, которая выглядит следующим образом:

  ----------- ------------- 
| BookName  | BookAuthor  |
 ----------- ------------- 
| A         | Arthur      |
 ----------- ------------- 
| A         | Will        |
 ----------- ------------- 
| B         | Jack        |
 ----------- ------------- 
| B         | Jack        |
 ----------- ------------- 
| B         | Charles     |
 ----------- ------------- 
| A         | Will        |
 ----------- ------------- 
| A         | John        |
 ----------- ------------- 
  

Я ищу SQL-запрос, который мог бы группировать одинаковые значения BookName и BookAuthor для возврата для каждого названия книги, с автором которого связано больше всего записей.

Итак, следуя моему примеру, в имени книги «A» в качестве автора книги было бы «Will», а в имени книги «B» было бы «Jack».

Я пробовал это: SELECT * FROM table GROUP BY BookName, BookAuthor HAVING BookName = "A" , но результат, который я получу, — «Артур».

Любая помощь приветствуется; большое спасибо.

Вы можете группировать по имени и автору. В HAVING предложении сравните количество с другой агрегацией по имени и автору в подзапросе, получая количество, но на этот раз отфильтрованное по имени и ограниченное строкой (a) с максимальным количеством.

 SELECT t1.bookname,
       t1.bookauthor
       FROM elbat t1
       GROUP BY t1.bookname,
                t1.bookauthor
       HAVING count(*) = (SELECT count(*)
                                 FROM elbat t2
                                 WHERE t2.bookname = t1.bookname
                                 GROUP BY t2.bookname,
                                          t2.bookauthor
                                 ORDER BY count(*) DESC
                                 LIMIT 1);
  

db<>скрипка

Однако это не нарушает связи. Но вы не упомянули, нужно ли вам это и каковы правила в таком случае.

Если вам нужно количество записей, вы можете использовать count() и group by

 select BookName, count(*)
from my_table  
group by BookName
order by count(*)
  

если вам нужен тот, у которого больше всего записей, вы можете использовать limit 1

 select BookName, count(*)
from my_table  
group by BookName
order by count(*)
limit 1
  

и для наиболее часто встречающегося имени в каждом bookname вы могли бы попробовать

 select BookName, BookAuthor  ,  count(*)
from my_table  
group by BookNane, BookAuthor  
order by count(*)
  

Select count(BookAuthor) as 'NoOfAuthAsso' from table group by BookName

Это не даст вам связанных авторов для каждой книги

 with cte_books
as
(
  select bookname,bookauthor
  ,row_number() over(partition by bookname,bookauthor order by bookname,bookauthor) as [NumOfBooks]
  from elbat
)

select a.bookname,a.bookauthor,a.NumOfBooks
from cte_books a
inner join (
             select bookname, max([NumOfBooks]) as [NumOfBooks] from cte_books group by bookname
           ) as b
on a.bookname = b.bookname
and a.[NumOfBooks] = b.[NumOfBooks]
  

В статистике это называется режимом. Один из относительно простых способов сделать это в MySQL — использовать два уровня агрегации:

 select bookname,
       substring_index(group_concat(bookauthor order by cnt desc), ',', 1) as mode_author
from (select bookname, bookauthor, count(*) as cnt
      from t
      group by bookname, bookauthor
     ) b
group by bookname;
  

В этом есть некоторые нюансы. Если у авторов могут быть запятые в их имени, тогда необходим другой разделитель. Кроме того, если список авторов превышает максимальную длину по умолчанию для group_concat() , то его необходимо расширить.

MySQL 8 , конечно, упрощает это, поддерживая оконные функции:

 select bookname, bookauthor 
from (select bookname, bookauthor, count(*) as cnt,
             row_number() over (partition by bookname order by count(*) desc) as seqnum
      from t
      group by bookname, bookauthor
     ) b
where seqnum = 1;
  

Вы не говорите, что делать в случае связей. Это возвращает одного произвольного лучшего автора. Но изменение row_number() на rank() возвращает их все.