我有下面的查询，它告诉我人们在给定时间段内开始和结束。
select UserId,
cast(sum(CommissionPerc)/100 as numeric(2,1)) as PlacementCount,
datename(mm,startdate) + ' ' + 'Start' as DataType
from PlacementConsultants
group by userid, datename(mm,StartDate)
union
select UserId,
cast(sum(CommissionPerc)/100 as numeric(2,1)) as PlacementCount,
datename(mm,EndDate) + ' ' + 'End' as DataType
from Placements
group by userid, datename(mm,EndDate)
order by datatype
输出如下：
791 4.0 May End
791 3.0 May Start
791 6.0 June End
791 2.0 June Start
791 1.0 July Start
但我也想有一列写为MonthName Change的列
例如
791 4.0 May End
791 3.0 May Start
791 -1.0 May Change
最好的方法是什么？
您可以将查询嵌入到CTE中：
;with CTE1 as (
select
UserId,
cast(sum(CommissionPerc)/100 as numeric(2,1)) as PlacementCount,
datename(mm,startdate) as Mon
from PlacementConsultants
group by userid, datename(mm, StartDate)
), CTE2 as (
select
UserId,
cast(sum(CommissionPerc)/100 as numeric(2,1)) as PlacementCount,
datename(mm,EndDate) as Mon
from Placements
group by userid, datename(mm, EndDate)
)
select C.UserId, C.PlacementCount, C.Mon + ' Start' as DataType
from CTE1 as C
union all
select C.UserId, C.PlacementCount, C.Mon + ' End' as DataType
from CTE2 as C
union all
select C1.UserId, C2.PlacementCount - C1.PlacementCount, C1.Mon + ' Change' as DataType
from CTE1 as C1
inner join CTE2 as C2 on C2.UserId = C1.UserId and C2.Mon = C1.Mon
order by DataType
我也将union更改为union all，因为union会尝试消除您没有的重复行
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句