两条sql语句如何整合到一起？实现 以uid和projectID分组为条件 取出相应 “总本地有效对话量” 和“总外地有效对话量 ”？

[ximomomoxinei39]

下面这条sql语句的意思是->"全天的数据"没有则取下午的数据,"下午的数据"没有则取"上午的数据",如下

select isnull([上午本地有效对话量],0)+isnull([下午本地有效对话量],0)+isnull([全天本地有效对话量],0) as 总本地有效对话量,
isnull([上午外地有效对话量],0)+isnull([下午外地有效对话量],0)+isnull([全天外地有效对话量],0) as 总外地有效对话量 
from 
(Select SUM(LocalValidCount) as 全天本地有效对话量,SUM(NoLocalValidCount) as 全天外地有效对话量  from DialogueQuantity where  AllDayStatusDQ='True') as a,
(Select SUM(MorningLocalValidCount) as 上午本地有效对话量,SUM(MorningNoLocalValidCount) as 上午外地有效对话量 from DialogueQuantity where  (AllDayStatusDQ is null and AfternoonStatusDQ is null and MorningStatusDQ='True')) as b,
(Select SUM(AfternoonLocalValidCount) as 下午本地有效对话量,SUM(AfternoonNoLocalValidCount) as 下午外地有效对话量 from DialogueQuantity where (AllDayStatusDQ is null and AfternoonStatusDQ = 'True')) as c

下面的这条sql语句是 按UID和ProjectID进行分组的情况下汇总“全天的数据”.如下：

SELECT UID,ProjectID,sum([LocalValidCount]) as 总本地有效对话量,sum([NoLocalValidCount]) as 总外地有效对话量
 FROM DialogueQuantity group by UID,ProjectID

但是我想达到的目标是按UID和ProjectID分组 取出上面那条sql中的 “总本地有效对话量” 和“总外地有效对话量 ”
两条sql应该怎么整合到一起？

[AcHerat]

;with ct1 as
(
  --你的第一段SQL
),ct2 as
(
  --你的第二段SQL
)

select *
from ct1 a join ct2 b on ...  -- left join   cross join  etc...

--SQL2005及以上版本

[AcHerat]

--sql2000

--1

select *
from (
  -- 第一段SQL 
) a join (       -- left join   cross join    etc...
  -- 第二段SQL
) b on ....

--2

select ...
 into #t1
from ...
where ...


select ...
 into #t2
from ...
where ...

select *
from #t1 a join #t2 b on a.[] = b.[]  -- left join    cross join    etc...

drop table #t1,#t2

[ximomomoxinei39]

[Quote=引用 1 楼 acherat 的回复:]
SQL code

;with ct1 as
(
--你的第一段SQL
),ct2 as
(
--你的第二段SQL
)

select *
from ct1 a join ct2 b on ... -- left join cross join etc...

--SQL2005及以上版本
[/Quote]
如果jion的话，那第一条sql中应该添加按 uid 和projectID分组，应该添加在哪呢 我是初学者 希望能详细点

[ximomomoxinei39]

这样做对么？ 接下来应该怎么做？

select isnull([上午本地有效对话量],0)+isnull([下午本地有效对话量],0)+isnull([全天本地有效对话量],0) as 总本地有效对话量,
isnull([上午外地有效对话量],0)+isnull([下午外地有效对话量],0)+isnull([全天外地有效对话量],0) as 总外地有效对话量 
from 
(Select UID,ProjectID,SUM(LocalValidCount) as 全天本地有效对话量,SUM(NoLocalValidCount) as 全天外地有效对话量  from DialogueQuantity where  AllDayStatusDQ='True' group by UID,ProjectID) as a,
(Select UID,ProjectID,SUM(MorningLocalValidCount) as 上午本地有效对话量,SUM(MorningNoLocalValidCount) as 上午外地有效对话量 from DialogueQuantity where  (AllDayStatusDQ is null and AfternoonStatusDQ is null and MorningStatusDQ='True') group by UID,ProjectID) as b,
(Select UID,ProjectID,SUM(AfternoonLocalValidCount) as 下午本地有效对话量,SUM(AfternoonNoLocalValidCount) as 下午外地有效对话量 from DialogueQuantity where (AllDayStatusDQ is null and AfternoonStatusDQ = 'True') group by UID,ProjectID) as c

[AcHerat]

select UID,ProjectID,
	sum(case when AllDayStatusDQ='True' then LocalValidCount
			 when MorningStatusDQ='True' then MorningLocalValidCount
			 when AfternoonStatusDQ = 'True' then AfternoonLocalValidCount else 0 end) 总本地有效对话量,
	sum(case when AllDayStatusDQ='True' then NoLocalValidCount
			 when MorningStatusDQ='True' then MorningNoLocalValidCount
			 when AfternoonStatusDQ = 'True' then AfternoonNoLocalValidCount else 0 end)总外地有效对话量
FROM DialogueQuantity 
group by UID,ProjectID

[ximomomoxinei39]

[Quote=引用 5 楼 acherat 的回复:]
SQL code

select UID,ProjectID,
sum(case when AllDayStatusDQ='True' then LocalValidCount
when MorningStatusDQ='True' then MorningLocalValidCount
when AfternoonStatus……
[/Quote]
谢谢谢谢！！非常感谢！！
结贴了 就是这个 呵呵