SQL语句中的union怎样将相同数据合并 ,急急急

w844638059 2011-04-22 11:03:41
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
...全文
600 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
WWWWA 2011-04-22
  • 打赏
  • 举报
回复
select 时间,max(AllOutput),max(AllAllowOutput),max(AllPreOutput) from
(你的SQL语句) a
group by 时间
w373210874 2011-04-22
  • 打赏
  • 举报
回复
不行啊
ACMAIN_CHM 2011-04-22
  • 打赏
  • 举报
回复
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
w373210874 2011-04-22
  • 打赏
  • 举报
回复
加一个ISNULL(column,0)看看
WWWWA 2011-04-22
  • 打赏
  • 举报
回复
select [quarter],max(AllOutput),max(AllAllowOutput),max(AllPreOutput) from
(你的SQL语句) a
group by [quarter]

2,598

社区成员

发帖
与我相关
我的任务
社区描述
Sybase相关技术讨论区
社区管理员
  • Sybase社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧