SQL 2012使用Joins连接多个表中的多个查询，并将其计数到单个表中

Question

我有一个情况，我不知道这是否有可能做到。

我有两个桌子我需要加入和使用计数。

表1= "EPM_ProjectUserview“

[ProjectUID], [ProjectName], [EnterpriseProjectTypeUID](used as a filter)

Table2 = "EpmTask_UserView“

[ProjectUID],[Task_Significance], [TaskPercentCompleted] then 3 count fields 
[total Count], [Count 100% complete], Count <1005]

这个会给我一张像这样的桌子

注意:有第一列不适合项目名称

|Apps|Overall% Comp| Totl Count|100% Compl|Not 100% Comp|Overall Health|

接口的同一行为真。

总体%完成，总体健康来自总结任务。

(this is project server) 

ProjectUID是连接的一个关键字段。

因此，我有5个不同的查询，所有的工作，但，当然，我需要他们在一个表。

查询1-这是项目名称projectUID和EnterpriseprojecttypeUID

SELECT [ProjectName]
   ,[ProjectUID]
   ,[EnterpriseProjectTypeUID]
  FROM [dbo].[MSP_EpmProject_UserView]
 where [projectUID] not like '0000CF75-FB12-4FFC-A404-AEC4F3258A9C'
 And
 [EnterpriseProjectTypeUID] like '76194144-3CDB-E611-9419-00215A9259E8'
 order by  [ProjectName] 

查询2给出了每种类型的总数。

SELECT p.[ProjectName]
  ,T.[Task_Significance]
 -- ,T.[TaskPercentCompleted]
  ,count (*) as [Total Count]
  FROM [psmado].[dbo].[MSP_EpmProject_UserView] as P
  join   [PSMADO].[dbo].[MSP_EpmTask_UserView] as T
  On T.[projectUID] = P.[projectUID]
  where 
  ([Task_Significance] like 'App'
   Or
   [Task_Significance] like 'Interface')
   Group by [ProjectName], [Task_Significance]

查询3-下一个查询给我100%完整的列

         SELECT p.[ProjectName]
  ,T.[Task_Significance]
 -- ,T.[TaskPercentCompleted]
  ,count (*) as [Count]
 FROM [psmado].[dbo].[MSP_EpmProject_UserView] as P
join   [PSMADO].[dbo].[MSP_EpmTask_UserView] as T
 On T.[projectUID] = P.[projectUID]
  where T.[TaskPercentCompleted] >= 100
  and
 ([Task_Significance] like 'App'
 Or
 [Task_Significance] like 'Interface')
   Group by [ProjectName], [Task_Significance]

查询4-下一个查询给出的是未完成100%的查询。

SELECT p.[ProjectName]
  ,T.[Task_Significance]
 -- ,T.[TaskPercentCompleted]
  ,count (*) as [Count]
 FROM [psmado].[dbo].[MSP_EpmProject_UserView] as P
 join   [PSMADO].[dbo].[MSP_EpmTask_UserView] as T
 On T.[projectUID] = P.[projectUID]
 where T.[TaskPercentCompleted] < 100
 and
 ([Task_Significance] like 'App'
 Or
 [Task_Significance] like 'Interface')
 Group by [ProjectName], [Task_Significance]

查询5最后，整个任务运行状况和%完成来自表中的不同行

SELECT [dbo].[MSP_EpmProject_UserView].[ProjectName]
   ,[dbo].[MSP_EpmTask_UserView].[ProjectUID]
  ,[dbo].[MSP_EpmTask_UserView].[TaskName] as [Name]
  ,[dbo].[MSP_EpmTask_UserView].[TaskPercentCompleted] as [% Complete]
  ,[dbo].[MSP_EpmTask_UserView].[Task Health] as [Health]
  ,[dbo].[MSP_EpmTask_UserView].[Task_Significance]
  ,[dbo].[MSP_EpmTask_UserView].[TaskOutlineNumber]
  FROM [dbo].[MSP_EpmTask_UserView]
  inner join [PSMADO].[dbo].[MSP_EpmProject_UserView] 
  on [dbo].[MSP_EpmTask_UserView].[projectuid] = [PSMADO].[dbo].[MSP_EpmProject_UserView].[ProjectUID]
  where [dbo].[MSP_EpmTask_UserView].[projectUID] not like '0000CF75-FB12-4FFC-A404-AEC4F3258A9C' -- the timesheet project null
  and [Task_Significance]  like 'App Summary'
 or [Task_Significance]  like 'Interface Summary'
 order by  [ProjectName], [TaskOutlineNumber]

这些是否可以放在一张桌子上(工会不是为我工作的)，如果是的话，怎么做呢？

谢谢你的帮助

杰克斯的回答非常接近。这就是我需要的：

​

这就是我要得到的。你可以看到，它只是重复了每一节。(我砍掉了一部分，但有36排。)

​

​

Answer 1

对你来说最好也是最简单的方法就是..。使用公共表表达式..。它不允许我将所有代码粘贴到这个表达式中，但是您可以直接使用以下语法来完成它。

使用t1 as (在这些括号中粘贴第一个查询完整)、t2 as (在这些括号中粘贴第二个查询完整)、t3 as (在这些括号中粘贴第三个查询完整)、t4 as (在这些括号中粘贴第4个查询完整)、t5 as (在这些括号中粘贴第5个查询完整)。

现在，在完成之后，编写一个查询并提取您想要的任何数据。例如

从t1、t2、t3、t4、t5中选择t1.abc、t2.abc、t3.xyz、t4.lmn、t5.xyz

希望这能有所帮助。

Answer 2

谢谢大家的意见和帮助。这是一个如此伟大的社区！所以我最终走向了一个不同的方向，因为杰克斯的想法使我走上了一条道路。

SELECT  pr.[ProjectName]
 ,T.[Task_Significance]

     , sum (case when T.[TaskPercentCompleted] is not null and T.[Task_Significance] ='App' Or T.[TaskPercentCompleted] is not null and T.[Task_Significance] = 'Interface'  then 1 else 0 end) as [Total]
 , sum (case when T.[TaskPercentCompleted] < 30 and T.[Task_Significance] ='App' Or T.[TaskPercentCompleted] < 30 and T.[Task_Significance] = 'Interface'  then 1 else 0 end) as [Incomplete]
 , sum (case when T.[TaskPercentCompleted] >= 30 and T.[Task_Significance] ='App' Or T.[TaskPercentCompleted] >= 30 and T.[Task_Significance] = 'Interface'  then 1 else 0 end) as [Complete]
   FROM [psmado].[dbo].[MSP_EpmProject_UserView] as Pr
 join   [PSMADO].[dbo].[MSP_EpmTask_UserView] as T
 On T.[projectUID] = Pr.[projectUID]
 Where
 T.[Task_Significance] like 'App'
 Or
 T.[Task_Significance] like 'Interface'

 Group by pr.[ProjectName], T.[Task_Significance]