重复的列名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

所以，我得到错误：

列名"idCommentToArticle"重复

我找不到复制品在哪里？

Answer 1

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

select g.* from commenttoarticle g 

而不是

select * from commenttoarticle g

此外，您应该指定Join条件，根据您的意图将行限制为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

Answer 2

正如@RADAR所建议的，您的内部查询联接似乎并不完整。我从评论中看到，一旦放入JOIN条件，就会丢失所有数据。我认为这是因为两个子查询都没有做它们应该做的事情

这是我对整体解决方案的尝试（注意，如果没有数据集和表定义，我就无法显示它的工作原理）。好吧，所以你在这里再次提出了这个问题，并提供了一个SQL Fiddle，我已经更新了一个工作版本，但减去了额外的JOIN表，因为它们没有定义。

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 commenttoarticle c   -- one layer of subquery not required.
INNER JOIN (select  s.idCommentToArticle, s.CommentToArticlePID -- added both the id and the parent id
            FROM commenttoarticle s 
            WHERE s.CommentToArticleIdArticle = 11 -- moved to inner query, instead of outer query
            ORDER BY s.idCommentToArticle DESC limit 3) as gh 
        ON c.idcommenttoarticle = gh.idcommenttoarticle   -- add join condition
        OR c.idcommenttoarticle = gh.CommentToArticlePID  -- which matches id and parent id
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) 
GROUP BY c.idCommentToArticle 
ORDER BY c.idCommentToArticle DESC

但让我进一步解释一下，您最初查询的以下代码是选择前3个idCommentToArticlePID，

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

但由于没有指定CCD_ 1，因此将这3个记录连接到来自CCD_。这导致返回完整的数据集。

然后你指定了WHERE c.CommentToArticleIdArticle = 11，这将结果集再次过滤回看起来正确的值。

当您添加ON时（根据@RADAR的建议），内部查询不包含任何与WHERE c.CommentToArticleIdArticle = 11过滤器匹配的值，因此您丢失了所有结果。如果您将此过滤器移动到内部查询中，如上所示，那么这些过滤器将协同工作，而不会发生冲突。

在JOIN条件中，您指示您想要匹配的项目及其父项，因此我将两者都添加到内部查询的返回中，并在联接条件中检查其中一个。

此外，我认为整个g表引用是多余的，可以删除。您应该能够以c的身份直接访问此表。

我对GROUP BY和ON0也有一些担忧——这似乎有点奇怪，但我没有支持上下文（即数据示例），所以它们可能是正确的。如果您仍然有问题，我会对GROUP BY和COUNT语句进行注释，并进行测试，看看您得到了什么数据，然后再将它们添加回。