我有两张桌子。与文章编号相关的图像。还有一张照片上有文章编号
我想要做的是列出第一个表中的所有图像（navn）（它是一个特定的文章编号(art_id)）和第二个表中的最多5个图像（其中文章编号（artid）出现在照片（fotostation）上）。
（我现在不知道该怎么做：）
这不起作用
SELECT i.* FROM ecs_article_images AS i
LEFT JOIN
( SELECT * FROM ecs_article_images_inneholder WHERE art_id = i.art_id LIMIT 0,5 ORDER BY dato DESC) AS inn ON inn.fotostation=i.fotostation
WHERE i.art_id='MS174868' ORDER BY nr, id DESC
表格：
CREATE TABLE `ecs_article_images` (
`id` int(11) NOT NULL,
`navn` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr` int(11) NOT NULL,
`art_id` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`fotostation` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`dato` datetime NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `ecs_article_images_inneholder` (
`id` int(11) NOT NULL,
`fotostation` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`artid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`dato` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这些表与fotostation字段相关。要在“ECS_article_images_inneholder”中找到具有artid的文件的名称，我必须在“ECS_article_images”中查找具有相同FOTOSTATION的navn。
可以使用row_number()枚举行。我不确定这两张表有什么关系。下面假设它同时在文章和foto站上：
SELECT i.*
FROM ecs_article_images i LEFT JOIN
(SELECT inn.*,
ROW_NUMBER() OVER (PARTITION BY inn.art_id, inn.fotostation ORDER BY dato DESC) as seqnum
FROM ecs_article_images_inneholder inn
WHERE LIMIT 0,5 ORDER BY dato DESC) AS inn ON
) inn
ON inn.art_id = i.art_id AND
inn.fotostation = i.fotostation AND
inn.seqnum <= 5
WHERE i.art_id = 'MS174868'
ORDER BY nr, id DESC;
关键是partition by键需要匹配join键。
