mysql - 仅选择与另一个注册表相关的最后插入的项目

Question

为了更容易解释，我建模了一个小型数据库:

CREATE TABLE bands (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE albums (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  band_id INTEGER UNSIGNED NOT NULL,
  album_name VARCHAR(120) NULL,
  rating INTEGER UNSIGNED NULL,
  insertion_date TIMESTAMP NULL,
  PRIMARY KEY(id),
  INDEX albums_FKIndex1(band_id),
  FOREIGN KEY(band_id)
    REFERENCES bands(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

现在，假设我们已经在各自的表中注册了一些乐队和许多专辑，我只想从每个注册乐队中选择最后插入的专辑。

PS:我必须使用“album.insertion_date”字段来确定最后插入的专辑。

Answer 1

尝试连接两个表并按 insert_date 和 band 进行过滤:

SELECT al.*
FROM albums al
INNER JOIN bands b ON al.band_id=b.id
WHERE al.insertion_date=(
    SELECT max(insertion_date)
    FROM albums
    WHERE band_id=b.id
)