MySQL:使用 JOIN 仅获取具有最新子行的父行

Question

我有 3 张 table :

• 新闻(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'

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

Answer 1

这是一个每组最大问题；您需要为 id_news 的每个值找到 o 的最小值，然后在o 值与该最小值匹配以获得第一张图片。请注意，您还有一些其他错误(tc.flg_featured 应为 tn.flg_featured 且 tc.date_news 应为 tn。日期新闻)。这应该会给你你想要的结果:

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

Demo on SQLFiddle