mysql - 重复的列名 SQL - 需要更改别名吗？

Question

我已经用 INNER JOIN 和子查询编写了 SQL 查询:

SELECT c.*, 
       ar.ArticleName, 
       ar.idArticle, 
       du.DetailToUsersName, 
       du.DetailToUsersPhoto, 
       COUNT(c.idCommentToArticle) AS CNT, 
       CASE WHEN d.Count IS NULL THEN 0 ELSE d.Count END AS CountLikes 
from (select * 
      from commenttoarticle g 
      inner join (select distinct(s.idCommentToArticle) 
                  from commenttoarticle s 
                  order by s.CommentToArticlePID limit 3) as gh) as c 

LEFT JOIN article ar ON c.CommentToArticleIdArticle = ar.idArticle 
LEFT JOIN detailtousers du ON du.idDetailToUsers = c.CommentToArticleIdUser 
LEFT JOIN `likes` d ON (d.IdNote = c.idCommentToArticle AND d.LikeType = 6) 
WHERE c.CommentToArticleIdArticle = 11 
GROUP BY c.idCommentToArticle 
ORDER BY c.idCommentToArticle DESC

所以，我收到错误:

Duplicate column name 'idCommentToArticle'

我找不到重复的地方？

Answer 1

可以在别名表中指定查询c

select g.* from commenttoarticle g 

而不是

select * from commenttoarticle g

此外，您还应该根据您的意图指定连接条件以将行数限制为 3，如果没有 ON 子句，它将类似于交叉连接。

select g.* from commenttoarticle g 
inner join (select distinct(s.idCommentToArticle) from commenttoarticle s order by s.CommentToArticlePID limit 3) as gh
     on g.idcommenttoarticle = gh.idcommenttoarticle