tengo 3 mesas Artists , SongArtists , Songs .
La canción puede tener múltiples artistas. Por lo tanto, tengo una mesa conjunta llamada SongArtists y la estructura de la mesa es:
ID | Name | URL | ViewCount | CreatedAt | UpdatedAt
ID | Name | URL | CreatedAt | UpdatedAt
ID | SongID | ArtistID
La tabla de canciones tiene una columna view_count para almacenar cuántas veces se ve la canción.
Quiero obtener los 10 mejores artistas según el número de vistas de sus canciones.
Intenté la siguiente consulta, pero el mismo artista se muestra dos veces. No puedo usar group by porque necesito songs.view_count para ordenar.
select artists.* from artists inner join song_artists on song_artists.artist_id = artists.id inner join songs on songs.id = song_artists.song_id order by songs.view_count desc limit 10;Por favor, muéstrame cómo puedo lograrlo.
Datos de ejemplo
Canciones
ID | Name | URL | ViewCount | CreatedAt | UpdatedAt 1 | Song A | song-a | 154 | 2021-12-11 15:34:21 | 2021-12-11 15:34:21 2 | Song B | song-b | 54 | 2021-12-13 12:23:12 | 2021-12-13 12:23:12 3 | Song C | song-c | 123 | 2021-12-13 13:12:56 | 2021-12-13 13:12:56 4 | Song D | song-d | 15 | 2021-12-13 14:01:15 | 2021-12-13 14:01:15 5 | Song E | song-e | 26 | 2021-12-14 12:12:03 | 2021-12-14 12:12:03 6 | Song F | song-f | 165 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 7 | Song G | song-g | 121 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 8 | Song H | song-h | 135 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 9 | Song I | song-i | 25 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 10 | Song J | song-j | 15 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 11 | Song K | song-k | 26 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 12 | Song L | song-l | 5 | 2021-12-14 13:54:23 | 2021-12-14 13:54:23Artistas
ID | Name | URL | CreatedAt | UpdatedAt 1 | Artist A | artist-a | 2021-12-11 15:34:21 | 2021-12-11 15:34:21 2 | Artist B | artist-b | 2021-12-13 12:23:12 | 2021-12-13 12:23:12 3 | Artist C | artist-c | 2021-12-13 13:12:56 | 2021-12-13 13:12:56 4 | Artist D | artist-d | 2021-12-13 14:01:15 | 2021-12-13 14:01:15 5 | Artist E | artist-e | 2021-12-14 12:12:03 | 2021-12-14 12:12:03 6 | Artist F | artist-f | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 7 | Artist G | artist-g | 2021-12-11 15:34:21 | 2021-12-11 15:34:21 8 | Artist H | artist-h | 2021-12-13 12:23:12 | 2021-12-13 12:23:12 9 | Artist I | artist-i | 2021-12-13 13:12:56 | 2021-12-13 13:12:56 10 | Artist J | artist-j | 2021-12-13 14:01:15 | 2021-12-13 14:01:15 11 | Artist K | artist-k | 2021-12-14 12:12:03 | 2021-12-14 12:12:03 12 | Artist L | artist-l | 2021-12-14 13:54:23 | 2021-12-14 13:54:23Artistas de canciones
ID | SongID | ArtistID 1 | 1 | 3 2 | 2 | 2 3 | 3 | 1 4 | 4 | 10 5 | 5 | 11 6 | 6 | 12 7 | 7 | 9 8 | 8 | 8 9 | 9 | 7 10 | 10 | 6 11 | 11 | 5 12 | 12 | 4 13 | 12 | 1Resultados esperados
ID | Name | URL | CreatedAt | UpdatedAt 12 | Artist L | artist-l | 2021-12-14 13:54:23 | 2021-12-14 13:54:23 3 | Artist C | artist-c | 2021-12-13 13:12:56 | 2021-12-13 13:12:56 8 | Artist H | artist-h | 2021-12-13 12:23:12 | 2021-12-13 12:23:12 1 | Artist A | artist-a | 2021-12-11 15:34:21 | 2021-12-11 15:34:21 9 | Artist I | artist-i | 2021-12-13 13:12:56 | 2021-12-13 13:12:56 2 | Artist B | artist-b | 2021-12-13 12:23:12 | 2021-12-13 12:23:12 11 | Artist K | artist-k | 2021-12-14 12:12:03 | 2021-12-14 12:12:03 5 | Artist E | artist-e | 2021-12-14 12:12:03 | 2021-12-14 12:12:03 7 | Artist G | artist-g | 2021-12-11 15:34:21 | 2021-12-11 15:34:21 10 | Artist J | artist-j | 2021-12-13 14:01:15 | 2021-12-13 14:01:15Todavía puede ordenar por MAX(view_count) del artista si usa agrupar por:
select artists.* from artists inner join song_artists on song_artists.artist_id = artists.id inner join songs on songs.id = song_artists.song_id group by artists.<primary key> order by MAX(songs.view_count) desc limit 10; Eso reducirá los resultados a una fila por artista y los ordenará por view_count de su canción más vista.
Usé <primary key> en el ejemplo, pero usaría id o cualquiera que sea la clave principal de su tabla. En versiones recientes de MySQL, esto es suficiente para indicar al analizador de consultas que las otras columnas de artists dependen funcionalmente de la(s) columna(s) de agrupación.
Podrías usar:
select artists.*,t1.view_count from artists inner join (select artists.name, songs.view_count from artists inner join song_artists on song_artists.artist_id = artists.id inner join songs on songs.id = song_artists.song_id order by songs.view_count desc limit 10 ) as t1 on t1.name=artists.name order by view_count desc ;Resultado:
id name url createdAt updatedAt view_count 12 Artist L artist-l 2021-12-14 13:54:23 2021-12-14 13:54:23 165 3 Artist C artist-c 2021-12-13 13:12:56 2021-12-13 13:12:56 154 8 Artist H artist-h 2021-12-13 12:23:12 2021-12-13 12:23:12 135 1 Artist A artist-a 2021-12-11 15:34:21 2021-12-11 15:34:21 123 9 Artist I artist-i 2021-12-13 13:12:56 2021-12-13 13:12:56 121 2 Artist B artist-b 2021-12-13 12:23:12 2021-12-13 12:23:12 54 5 Artist E artist-e 2021-12-14 12:12:03 2021-12-14 12:12:03 26 11 Artist K artist-k 2021-12-14 12:12:03 2021-12-14 12:12:03 26 7 Artist G artist-g 2021-12-11 15:34:21 2021-12-11 15:34:21 25 6 Artist F artist-f 2021-12-14 13:54:23 2021-12-14 13:54:23 15
Demostración: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1772083455984557edf0b6aa7d03b5a9
**
Editar: @Lwin Htoo Ko debajo de la respuesta dio el resultado deseado
**
select artists.* from artists inner join ( select artists.id, songs.view_count from artists inner join song_artists on song_artists.artist_id = artists.id inner join songs on songs.id = song_artists.song_id group by artists.id, songs.view_count order by songs.view_count desc ) as top_artists on top_artists.id = artists.id group by artists.id limit 10;