我该如何执行这个荒谬的 SQL 多重连接语句？

Question

好吧，我正在废弃旧的，因为代码已经改变了很多。这是我现在所拥有的：

SELECT
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate
FROM
   users
LEFT JOIN clickTrack
 ON users.userId = clickTrack.clickUserId
LEFT JOIN article
 ON clickTrack.clickDocumentId = article.articleId
LEFT JOIN users author
 ON author.userId = article.articleAuthorId
LEFT JOIN industry artIndustry
 ON artIndustry.industryId =    substring(article.articleIndustryId,0,charindex(',',article.articleIndustryId)-1)
WHERE ((clickTrack.clickDocumentTable = 'breaking')
     OR (clickTrack.clickDocumentTable = 'article'))
  AND article.articleCreateDate > '1/1/2008 0:00:00 AM'
  AND ((users.userindustryId = '1') 
    OR (users.userindustryId LIKE '%,1') 
    OR (users.userindustryId LIKE '%,1,%') 
    OR (users.userindustryId LIKE '1,%'))
GROUP BY
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate
Order By article.articleId

返回的数据如下所示：

8332，理想情况，突发新闻，NULL，NULL，NULL，Prod，2011-07-25 14:48:01.203，2011-08-09 07:41:29.373

我添加了逗号。 3 个空字段是行业名称、用户的名字和用户的姓氏。 （是的，有人将表字段命名为错误。）我非常累，所以我不知道这是否是足够的信息。如果您需要更多，请告诉我。

编辑

它现在正在工作。我必须从左连接子句中去掉“-1”。哦，是的，我只是废弃名称字段。那还是不行。 >。<但我已经完成了。

Answer 1

作者列表和用户列表真的是同一张表吗？您想查询什么？

一个问题是您试图返回不在您的分组依据中的字段。

试试这个：

SELECT
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate,
FROM
   users
LEFT JOIN clickTrack
 ON users.userId = clickTrack.clickUserId
LEFT JOIN article
 ON clickTrack.clickDocumentId = article.articleId
LEFT JOIN users author
 ON author.userId = article.articleAuthorId
LEFT JOIN industry artIndustry
 ON artIndustry.industryId = article.articleIndustryId
WHERE ((clickTrack.clickDocumentTable = 'breaking')
     OR (clickTrack.clickDocumentTable = 'article'))
  AND article.articleCreateDate > '1/1/2008 0:00:00 AM'
  AND users.industryId = 1
GROUP BY
   article.articleId,
   article.articleName,
   article.articleStoryTypeId,
   artIndustry.industryName,
   author.userPrefix,
   author.userMiddleName,
   article.articleStatus,
   article.articleCreateDate,
   article.articleModifyDate,
Order By article.articleId

Answer 2

啊，好吧，试试这个：

SELECT 
  article.articleId,
  article.articleName, 
  article.articleStoryTypeId, 
  a.industryName, 
  a.userPrefix, 
  a.userMiddleName, 
  article.articleStatus, 
  article.articleCreateDate, 
  article.articleModifyDate,
  count(*)
FROM (select * from users
      INNER JOIN industry ON industry.industryId = users.userIndustryId 
                          AND industry.industryId = 1) a
LEFT OUTER JOIN clickTrack ON users.userId = clickTrack.clickUserId 
                     AND clickTrack.clickDocumentTable IN ('breaking','article')
LEFT OUTER JOIN article ON clickTrack.clickDocumentId = article.articleId 
                  AND article.articleCreateDate > '1/1/2008 0:00:00 AM' 
GROUP BY 
  article.articleId,
  article.articleName, 
  article.articleStoryTypeId, 
  a.industryName, 
  a.userPrefix, 
  a.userMiddleName, 
  article.articleStatus, 
  article.articleCreateDate, 
  article.articleModifyDate
ORDER BY 
  article.articleId