为SQL Server 2008中的每个主题选择最新的主题

vyclarks 发表于 Dev

维克拉克

我有一个关于编程论坛的项目。

我在数据库中有2个表：

SUBJECT （idSUB，titleSUB）；
TOPIC （idTOP，titleTOP，contentTOP，idSUB，idUser [用户创建主题]，时间）；

我想要的是：

+ select COUNT(*) from TOPIC as numTOPIC group by idSUB--> as Table A
+select TOP 1 titleTOP order by Time desc->  as newestTOP group by idSUB---> as Table B
+ Then JOIN 3 table A,B,SUBJECT--> C(idSUB,titleSUB,numTOPIC,newestTOP, idUser (who created the newest topic))

我已经找到了通往-->的方法LEFT JOIN A，但是我真的不知道正确的语法来联接上面的3个表。SUBJECTC(idSUB,titleSUB,numTOPIC)

SELECT  
    a.idSUB, a.titleSUB,
    COUNT(b.idSUB) numTOPIC 
FROM
    SUBJECT a
LEFT JOIN 
    TOPIC b ON a.idSUB = b.idSUB
GROUP BY 
    a.idSUB, a.titleSUB

我只想在一个查询中执行此操作。救命！

更新：

通过@John Bingham下面的代码，输出表无法显示SUBJECT没有任何内容的TOPIC。我希望所有这些TOPIC都可以显示。

SELECT 
    s.idSUB, s.titleSUB, a.numTOPIC, 
    isnull(b.newestTOP, '') as [Newest Topic], 
    isnull(b.idUser, '')
FROM 
    Subject s 
INNER JOIN 
    (SELECT 
         IDSub, Count(*) as NumTopic 
     FROM 
         Topic 
     GROUP BY IDSub) a ON s.IDSub = a.IDSub
LEFT JOIN 
    (SELECT 
         t.IDSub, t.titleTop as newestTop, t.idUser as [idUser] 
     FROM 
         Topic t 
     INNER JOIN 
         (SELECT IDSub, Max([Time]) as tm 
          FROM Topic 
          GROUP BY IDSub) x ON t.IDSub = x.IDSub
     WHERE t.[Time] = x.tm) b ON s.IDSub = b.IDSub

这是正确的查询，但我需要更准确的帮助！

约翰·宾汉

我的意愿是将需求（a），（b）和（c）中的每一个转换为将虚拟表传递到您的查询的子查询，而不是尝试组合基本表以一次命中来交付需求。所以-

SELECT s.idSUB, s.titleSUB, a.numTOPIC, isnull(b.newestTOP, '') as [Newest Topic], 
  isnull(b.idUser, '')
FROM Subject s 
  INNER JOIN (SELECT IDSub, Count(*) as NumTopic FROM Topic GROUP BY IDSub) a 
    ON s.IDSub = a.IDSub
  LEFT JOIN (
    SELECT t.IDSub, t.titleTop as newestTop, t.idUser as [idUser] 
    FROM Topic t 
      INNER JOIN (
        SELECT IDSub, Max([Time]) as tm FROM Topic GROUP BY IDSub
      ) x ON t.IDSub = x.IDSub
    WHERE t.[Time] = x.tm
  ) b ON s.IDSub = b.IDSub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。