union语句急解决思路

2012-02-13

union语句急急急select 时间 Case when month(CreateDate) in (1,2,3) then 1季度 when month(CreateD

union语句急急急
select 时间 = Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'
when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end ,
sum(AllOutput) as 削减量,sum(AllAllowOutput) as 排放量,sum(AllPreOutput) as 批复量
from DirtyOutputControl where AliasName='化学需氧量' and Year(CreateDate) = Year(getdate())
group by
Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'
when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end
union
select '1季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
union
select '2季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
union
select '3季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
union
select '4季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput

查询出的数据是这样的子的

1季度 NULL 2.00000 2.00000
1季度 0.00000 0.00000 0.00000
2季度 NULL NULL NULL
2季度 0.00000 0.00000 0.00000
3季度 0.00000 0.00000 0.00000
4季度 0.00000 0.00000 0.00000

我想查询出的效果是这样子的
1季度 0.00000 2.00000 2.00000
2季度 0.00000 0.00000 0.00000
3季度 0.00000 0.00000 0.00000
4季度 0.00000 0.00000 0.00000

[解决办法]

SQL code

select 时间,sum(削减量),sum(排放量),sum(批复量)from (    select 时间 = Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'    when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end ,    sum(AllOutput) as 削减量,sum(AllAllowOutput) as 排放量,sum(AllPreOutput) as 批复量    from DirtyOutputControl where AliasName='化学需氧量' and Year(CreateDate) = Year(getdate())    group by     Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'    when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end    union    select '1季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput    union     select '2季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput    union     select '3季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput    union     select '4季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput) xgroup by 时间
[解决办法]
select 时间,sum(削减量),sum(排放量),sum(批复量)
from (

   select 时间 = Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'
   when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end ,
   sum(AllOutput) as 削减量,sum(AllAllowOutput) as 排放量,sum(AllPreOutput) as 批复量
   from DirtyOutputControl where AliasName='化学需氧量' and Year(CreateDate) = Year(getdate())
   group by  
   Case when month(CreateDate) in (1,2,3) then '1季度' when month(CreateDate) in (4,5,6) then '2季度'
   when month(CreateDate) in (7,8,9) then '3季度' when month(CreateDate) in (10,11,12) then '4季度' end
   union
   select '1季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
   union  
   select '2季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
   union  
   select '3季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
   union  
   select '4季度' as [quarter],0 as AllOutput,0 as AllAllowOutput,0 as AllPreOutput
) x
group by 时间

热点排行

SQL Server

union语句急解决思路