在SQL中使用UNION时出现问题 [英] Problem while using UNION in SQL
本文介绍了在SQL中使用UNION时出现问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
MY Sql查询
选择 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 ) ' monthyear',
SUM(金额) as ' Amount1', 0 as ' Amount2'
来自 MYtable
其中 accountid = 4
group by CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
< span class =code-keyword> union
select CONVERT (< span class =code-keyword> CHAR ( 4 ),dateofentry, 100 ) + CONVERT ( CHAR ( 4 ), dateofentry, 120 ) as ' monthyear',
0 as < span class =code-stri ng>' Amount1',
SUM(金额) as ' Amount2'
来自 MYtable
其中 accountid = 3
group by CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
但是当我执行查询时我会以这种方式获取数据..
MonthYear Amount1 Amount2
Jan 2015 0 100
Ĵ 2015 100 0
2月 2015 200 0
2月 2015 0 300
Mar 2015 400 0
Mar 2015 0 500
但我想要这种格式的数据
MonthYear Amount1 Amount2
Jan 2015 100 100
2月 2015 200 300
Mar 2015 400 500
提前Thx ...
我的尝试:
选择 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 ) as ' monthyear',
SUM(金额) ) as ' Amount1', 0 as ' Amount2'
来自 MYtable
其中 accountid = 4
group 按 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
union
选择 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ < span class =code-keyword> CONVERT ( CHAR ( 4 ),dateofentry, 120 ) as ' monthyear',
0 as ' Amount1',
SUM(金额) as ' Amount2'
来自 MYtable
其中 accountid = 3
group 按 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
解决案
你不需要UNION
的!你必须使用CASE WHEN ... END
。
所以,最简单的方法是:
SELECT CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 ) as ' monthyear',
SUM( CASE WHEN accountid = 4 那么金额 ELSE 0 END ) as ' < span class =code-string> Amount1',
SUM( CASE WHEN accountid = 3 那么金额 ELSE 0 END ) as ' Amount2'
FROM MYtable
GROUP BY CONVERT ( CHAR ( 4 ), dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
>
正如彼得所说,UNION正在做它应该做的事情。解决这个问题的一个简单方法是将您拥有的内容包装在另一个SELECT语句中。类似于:
SELECT x.monthyear,SUM(x。 amount1) AS amount1,SUM(x.amount2) as amount2
FROM (
select CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry,< span class =code-digit> 120 ) as ' monthyear',
SUM(金额) as ' Amount1', 0 as ' Amount2'
来自 MYtable
其中 accountid = 4
group by CONVERT ( CHAR ( 4 ),dateofentry , 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
union
选择 CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 ) as ' monthyear',
0 as ' Amount1',
SUM(金额) as ' Amount2'
来自 MYtable
其中 accountid = 3
group by CONVERT ( CHAR ( 4 ),dateofentry, 100 )+ CONVERT ( CHAR ( 4 ),dateofentry, 120 )
)x
< span class =code-keywo rd> GROUP BY x.monthyear
MY Sql Query
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
SUM(amount) as 'Amount1',0 as 'Amount2'
from MYtable
where accountid=4
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
union
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
0 as 'Amount1',
SUM(amount) as 'Amount2'
from MYtable
where accountid=3
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
but when i execute query i am getting data in this way..
MonthYear Amount1 Amount2
Jan 2015 0 100
Jan 2015 100 0
Feb 2015 200 0
Feb 2015 0 300
Mar 2015 400 0
Mar 2015 0 500
But i want Data in this format
MonthYear Amount1 Amount2
Jan 2015 100 100
Feb 2015 200 300
Mar 2015 400 500
Thx in advance...
What I have tried:
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
SUM(amount) as 'Amount1',0 as 'Amount2'
from MYtable
where accountid=4
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
union
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
0 as 'Amount1',
SUM(amount) as 'Amount2'
from MYtable
where accountid=3
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
解决方案
You don't needUNION
's! You have to useCASE WHEN ... END
.
So, the simplest way is:
SELECT CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear', SUM(CASE WHEN accountid=4 THEN amount ELSE 0 END) as 'Amount1', SUM(CASE WHEN accountid=3 THEN amount ELSE 0 END) as 'Amount2' FROM MYtable GROUP BY CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
As Peter said, UNION is doing exactly what it is supposed to do. An easy way around this is to wrap what you have inside another SELECT statement. Something like:
SELECT x.monthyear, SUM(x.amount1) AS amount1, SUM(x.amount2) as amount2 FROM ( select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear', SUM(amount) as 'Amount1',0 as 'Amount2' from MYtable where accountid=4 group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) union select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear', 0 as 'Amount1', SUM(amount) as 'Amount2' from MYtable where accountid=3 group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) ) x GROUP BY x.monthyear
这篇关于在SQL中使用UNION时出现问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文