主页 / dba / 问题 / 250153
Accepted
Mark D
Asked: 2019-10-03 22:11:23 +0800 CST

SQL 查询 GROUP BY JOIN

  • 772

我有下表：

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');

我正在尝试获取包含最新视频的不同频道列表。

例如，对于频道 1，它将是“2019-10-03 01:55:55”的视频“D”，对于频道 2，它将是“2019-10-03 01:58:00”的视频“E” );

我试过像这样自我加入，

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

但我的视频 ID 与最新视频不匹配。即我最终会得到视频 ID：1，我想要视频 ID：4。

mysql mariadb-10.3

1 个回答

  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

    小提琴

    • 1

相关问题