select Numbers,sum(isnull(Amount,0)) as Amount from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers union all select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers 这样不行吗?
select * from ( select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)) aa full join ( select Numbers,sum(isnull(Amount,0)) from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) bb on aa.number=bb.number full join ( select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers) cc on aa.number=cc.number这样?第一个表的字段情况不了解,不知道关系字段是什么。
select * from ( select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)) aa left join ( select Numbers,sum(isnull(Amount,0)) from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) bb on aa.number=bb.number left join ( select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers) cc on aa.number=cc.number第一个表为主表,left join就可以了。
--try select * from ( select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID) ) ta left join ( select Numbers,sum(isnull(Amount,0)) from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) tb on ta.Numbers=tb.Numbers left join ( select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) tc on ta.Numbers=tc.Numbers
;with 数据集 as ( select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID) ) ,maco1 as (select Numbers,sum(isnull(Amount,0)) from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers) ,maco2 as (select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers)select * from 数据集 a left join maco1 b on a.number=b.number left join maco2 c on a.number=c.number这样是不是显得更直观一些...
--try select * from ( select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID) ) ta left join ( select Numbers,sum(isnull(Amount,0)) as Amount from Penalty where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) tb on ta.Numbers=tb.Numbers left join ( select Numbers,sum(isnull(PersonalAmount,0)) as PersonalAmount from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers ) tc on ta.Numbers=tc.Numbers
select Numbers,sum(isnull(Amount,0)) as Amount from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
union all
select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
这样不行吗?
连接条件是用row_number()出现的id号,还是用number这个字段呀?
后面两个查询条件要以第一个数据集为主表
数据集,与后面两个查询都有一个number字段
select * from (
select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)) aa
full join (
select Numbers,sum(isnull(Amount,0)) from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers ) bb on aa.number=bb.number
full join (
select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers) cc on aa.number=cc.number这样?第一个表的字段情况不了解,不知道关系字段是什么。
select * from (
select * from TempWorkingAge t where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)) aa
left join (
select Numbers,sum(isnull(Amount,0)) from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers ) bb on aa.number=bb.number
left join (
select Numbers,sum(isnull(PersonalAmount,0)) from Reward where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1 and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers) cc on aa.number=cc.number第一个表为主表,left join就可以了。
select *
from
(
select *
from TempWorkingAge t
where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)
) ta
left join
(
select Numbers,sum(isnull(Amount,0))
from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
) tb
on ta.Numbers=tb.Numbers
left join
(
select Numbers,sum(isnull(PersonalAmount,0))
from Reward
where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
) tc
on ta.Numbers=tc.Numbers
;with 数据集 as
(
select * from TempWorkingAge t
where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)
)
,maco1 as (select Numbers,sum(isnull(Amount,0)) from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers)
,maco2 as (select Numbers,sum(isnull(PersonalAmount,0)) from Reward
where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1 group by Numbers)select * from 数据集 a
left join maco1 b on a.number=b.number
left join maco2 c on a.number=c.number这样是不是显得更直观一些...
你们的思路是把三个查询看作一个表,然用用left join把他们连接起来怎么我用left join方法为什么会提示消息 8155,级别 16,状态 2,第 10 行
没有为 'cc' 的列 2 指定任何列。
select *
from
(
select *
from TempWorkingAge t
where rid = (select max(rid) from TempWorkingAge where PostID = t.PostID)
) ta
left join
(
select Numbers,sum(isnull(Amount,0)) as Amount
from Penalty
where PenaltyDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
) tb
on ta.Numbers=tb.Numbers
left join
(
select Numbers,sum(isnull(PersonalAmount,0)) as PersonalAmount
from Reward
where RewardDate between DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0)-1
and DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0))-1
group by Numbers
) tc
on ta.Numbers=tc.Numbers
我上面的with as有六个.再加这三个的话就有9个了,这样过多会不会有什么影响响
建议你看一下这篇文章:
在SQL Server中,关于with as使用介绍 一.WITH AS的含义