MySQL:使用 JOIN 获取仅包含最新子行的父行

Question

我有 3 个表：

• 新闻 (news别名tn(
• 新闻类别 (news_cat别名tc(
• 新闻图片(news_pic别名tp(

我需要获取最新的新闻，每个新闻都有他们的类别，并且只有该特定帖子的第一张照片，使用o作为订单字段。

这是我当前的sql查询：

SELECT
tn.date_news AS date_news,
tn.title AS title,
tn.text AS text,
tn.url AS url,
tc.name AS cat,
tp.file AS file
FROM news AS tn
JOIN news_cat AS tc ON tc.id_cat = tn.id_cat
JOIN (
SELECT file FROM news_pic WHERE news_pic.id_news = tn.id_news ORDER BY temp.o LIMIT 1
) AS tp ON tp.id_news = tn.id_news
WHERE
tn.flg_featured = 1
ORDER BY tc.date_news DESC LIMIT 6

我收到此错误消息：

Column not found: 1054 Unknown column 'tn.id_news' in 'where clause'

这是包含表结构和一些示例数据的 sqlfiddle。感谢您的任何建议。

Answer 1

这是一个greatest-n-per-group问题;您需要为每个值找到id_news的最小值o，然后在与该最小值匹配的o值上JOINnews_pic自身，以获得第一张图片。请注意，您还有其他一些错误(tc.flg_featured应该tn.flg_featured，tc.date_news应该tn.date_news(。这应该会给你你想要的结果：

SELECT
tn.date_news AS date_news,
tn.title AS title,
tn.text AS text,
tn.url AS url,
tc.name AS cat,
tp.file AS file
FROM news AS tn
JOIN news_cat AS tc ON tc.id_cat = tn.id_cat
JOIN news_pic tp ON tp.id_news = tn.id_news
JOIN (
SELECT id_news, MIN(o) AS o
FROM news_pic
GROUP BY id_news
) AS tpm ON tpm.id_news = tn.id_news AND tpm.o = tp.o
WHERE tn.flg_featured = 1
ORDER BY tn.date_news DESC
LIMIT 6

关于SQLFiddle的演示