Início / dba / Perguntas / 250153
Accepted
Mark D
Asked: 2019-10-03 22:11:23 +0800 CST

Consulta SQL GROUP BY JOIN

  • 772

Tenho as seguintes tabelas:

CREATE TABLE `Channels` (
   `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `title` VARCHAR(255) NOT NULL,
   `views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;

INSERT INTO Channels (title, views) 
VALUES ('Movies', 50030), 
       ('TV shows', 51201), 
       ('Howtos', 18601);

CREATE TABLE `Videos` (
   `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
   `channel_id` INT(11),
   `name` VARCHAR(255) NOT NULL,
   `description` TEXT,
   `views` INT(11) NOT NULL DEFAULT 0,
   `created_at` DATETIME NOT NULL,
   `updated_at` DATETIME,
   CONSTRAINT videos_channel_id_fk
       FOREIGN KEY ( channel_id ) 
           REFERENCES Channels ( id )
) ENGINE=InnoDB;

INSERT INTO Videos (`channel_id`, `name`, `created_at`) 
VALUES (1, 'A', '2019-10-03 00:00:12'), (1, 'B', '2019-03-01 00:12:12'), 
       (1, 'C', '2019-10-01 19:12:12'), (1, 'D', '2019-10-03 01:55:55'),
       (2, 'E', '2019-10-03 01:58:00'), (2, 'F', '2019-10-01 15:11:11'),
       (2, 'G', '2019-09-09 11:11:11'), (2, 'H', '2019-08-01 12:53:10');

Estou tentando obter uma lista distinta de canais com seus vídeos mais recentes.

Por exemplo, para o Canal 1, seria o Vídeo 'D' em '2019-10-03 01:55:55', para o Canal 2 seria o Vídeo 'E' em '2019-10-03 01:58:00' );

Eu tentei fazer uma auto-junção assim,

SELECT v1.id, v1.channel_id, v1.name FROM Videos AS v1
LEFT JOIN Videos AS v2 ON v1.channel_id = v2.channel_id AND v1.created_at>v2.created_at 
WHERE v1.created_at IS NULL

mas meu ID de vídeo nunca corresponde ao vídeo mais recente. ou seja, vou acabar com o ID do vídeo: 1 onde quero ID do vídeo: 4.

mysql mariadb-10.3

1 respostas

  1. Best Answer
    WITH cte AS (SELECT *, 
                    ROW_NUMBER() OVER (PARTITION BY channel_id 
                                       ORDER BY created_at DESC) rn
             FROM Videos)
SELECT *
FROM Channels c
LEFT JOIN cte ON c.id = cte.channel_id AND cte.rn = 1

    violino

    • 1

relate perguntas