代码正常运行
SELECT
c.id_article,
a.titre AS title_article
FROM comments AS c
LEFT JOIN articles AS a
ON a.id = c.id_article
WHERE c.hide = 0
GROUP BY c.id_article
ORDER BY MAX(c.date) DESC
LIMIT 0, 6
但是我需要文章中最后评论的用户名。
I try this
SELECT
c.id_article,
a.titre AS title_article,
u.nom_utilisateur AS user_name
FROM comments AS c
LEFT JOIN articles AS a
ON a.id = c.id_article
LEFT JOIN membres AS u
ON c.id_user = u.id
WHERE c.hide = 0
GROUP BY c.id_article
ORDER BY MAX(c.date) DESC
LIMIT 0, 6
和not working…
有什么帮助吗?
(抱歉我的英语不好…)
如果没有错误消息或描述什么是错误的,很难说,但无论如何,您没有在GROUP BY
语句中包含u.nom_utilisateur
。
MySQL通常允许这样做而不会抛出错误,但它可能导致意想不到的结果。
SELECT
c.id_article,
a.titre AS title_article,
u.nom_utilisateur AS user_name
FROM comments AS c
LEFT JOIN articles AS a
ON a.id = c.id_article
LEFT JOIN membres AS u
ON c.id_user = u.id
WHERE c.hide = 0
GROUP BY c.id_article, u.nom_utilisateur
ORDER BY MAX(c.date) DESC
LIMIT 0, 6
我有一个部分的解决方案。
我使用以下代码从文章中检索最后的注释:
SELECT
c.id_article,
MAX(c.id) AS last_id_comment,
a.titre AS title_article
FROM comments AS c
LEFT JOIN articles AS a
ON a.id = c.id_article
WHERE c.hide = 0
GROUP BY c.id_article
ORDER BY MAX(c.date) DESC
LIMIT 0, 6
在while语句之后,对于第一次SELECT,我使用以下代码从最后一条注释中检索用户名:
SELECT
u.nom_utilisateur AS username
FROM comments AS c
LEFT JOIN membres AS u
ON u.id = c.id_user
WHERE c.id = :last_id_comment