代码之家 › 专栏 › 技术社区 › Andres SK

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

join mysql

0

Andres SK · 技术社区 · 5 年前

我有三张桌子:

新闻资讯( 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 给出了表格的结构和一些示例数据。谢谢你的建议。

0 回复 | 直到 5 年前

1

2

Nick SamSmith1986 5 年前

这是一个 greatest-n-per-group 问题;你需要找到的最小值 o 对于每个值 id_news 然后 JOIN news_pic 对自己在 o 值匹配该最小值以获得第一张图片。请注意,您还有其他几个错误( 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

Demo on SQLFiddle