在 SQL Server 2005 中使用多个计数

Question

select p.intprojectid, p.vcprojectname, md.intmoduleid, 
       md.vcmodulename, md.intscreensfunc, md.vcname 
from projects as p 
left join (select m.intprojectid, m.intmoduleid, m.vcmodulename, 
                  s.intscreensfunc, s.vcname 
           from modules as m 
           left join screens_func as s on m.intmoduleid = s.intmoduleid) md 
           on p.intprojectid = md.intprojectid

此查询将返回：

no |project-name|mod-id|mod-name  |  screen-id   |  screen-name
----------------------------------------------------------------
2   Project-1     4      mod-1          11            scr1
2   Project-1     4      mod-1          12            scr2
2   Project-1     4      mod-1          13            scr3
2   Project-1     4      mod-1          14            scr4
2   Project-1     8     Module-2       NULL           NULL

现在我想计算project-1 中的 no.of mod-name 和 no.of.screen-name 。即我希望查询返回

project-name     no.of.mod          no.of.screen
------------------------------------------------
Project-1          2                    4

Answer 1

绝对有可能返回多个计数。

换句话说，您的查询可以修改如下：

select p.vcprojectname, COUNT(DISTINCT md.intmoduleid) as no.of.mod, COUNT(md.intscreensfunc) as no.of.screen
from projects as p 
    left join (select m.intprojectid, m.intmoduleid, m.vcmodulename, s.intscreensfunc, s.vcname 
               from modules as m 
               left join screens_func as s 
                        on m.intmoduleid=s.intmoduleid)md 
           on p.intprojectid=md.intprojectid
GROUP BY p.vcprojectname

根据您的示例数据，我推断模块和屏幕之间存在一对多关系，因此您需要模块的 distinct 计数但屏幕不需要相同的要求（因为看起来一个屏幕不会在给定模块中多次出现）如果情况并非如此，您还可以将 distinct 添加到计数中屏幕数量。