mysql - 需要我的表为每个 id 仅创建一行

Question

我需要连接两个表，它们是:

表 1

t1_id | t1_text | t1_date

表 2

t2_id | t2_text | t2_date | t1_id

我想要得到什么:

t1_text | t1_date | t2_text | t2_date

表必须显示最新的、唯一的 t1 行及其链接的 t2_text 及其 t2_date

这是我到目前为止得到的:

SELECT `table_1`.`t1_text` AS 'Text', `table_1`.`t1_date` AS 't1_date', `table_2`.`t2_text` AS 't2_Text', `table_2`.`t2_date` AS 'Date' 
FROM `table_1` 
LEFT JOIN `table_2` ON `table_1`.`t1_id`=`table_2`.`t1_id` 
ORDER BY `table_1`.`t1_date` DESC 
LIMIT 10

已经接近了，但没有雪茄。我仍然多次出现相同的 t1 行

Answer 1

我不确定我是否清楚地理解您想要的输出是什么。请尝试以下操作:

SELECT news.text AS 'News', news.date AS 'Publish date', 
comments.text AS 'Most Recent Comment', comments.date AS 'Comment Date' 
FROM news
JOIN (
    SELECT text, MAX(newsId) as newsId, date
    FROM comments 
    GROUP BY newsId
)comments ON news.newsId=comments.newsId
WHERE comments.text IS NOT NULL 
ORDER BY news.date DESC LIMIT 10