如何用sum函数求两个表数据的和

[周淡]

//我要把当前表和历史表的数据相加求出来，我用了以下sql语句，但是发现求不出来。
SELECT count(pk1) as count1 FROM ftrans_info where user_id='2012050900000001' and tran_code in ('6110','6120','6130')
and tran_date between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1
union
SELECT count(pk1) as count2 FROM ftrans_info_hst where user_id='2012050900000001' and tran_code in ('6110','6120','6130') and tran_date
between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1


//以下是上述sql语句的输出结果

COUNT1
23
24

我如何把两个sql语句合并成一个输出，就是我的预想输出是
COUNT1
47

[forgetsam]

再套一层select count(*) from ()不就出来了。

[周淡]

是的，发完帖子我就发现自己2了。。。。


select sum(count1) from( SELECT count(pk1) as count1 FROM ftrans_info where user_id='2012050900000001' and tran_code in ('6110','6120','6130')
and tran_date between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1
union
SELECT count(pk1) as count2 FROM ftrans_info_hst where user_id='2012050900000001' and tran_code in ('6110','6120','6130') and tran_date
between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1)
就好了

[德薄才浅]

declare int @count1,@count2

SELECT @count1 = count(pk1) as count1 FROM ftrans_info where user_id='2012050900000001' and tran_code in ('6110','6120','6130')
and tran_date between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1
union
SELECT @count2 = count(pk1) as count2 FROM ftrans_info_hst where user_id='2012050900000001' and tran_code in ('6110','6120','6130') and tran_date
between '2012-01-01' and '2012-05-17' and chk_status = 1 and status = 1

select @count1 + @count2

[德薄才浅]

[Quote=引用 3 楼 的回复:]

declare @count1,@count2 int

SELECT @count1 = count(pk1) as count1 FROM ftrans_info where user_id='2012050900000001' and tran_code in ('6110','6120','6130')
and tran_date between '2012-01-01' and……
[/Quote]
刚刚写错了！！
declare @count1 int
declare @count2 int

[jaq2006213]

count 和sum 分清楚了吗？

[zdj624489750]

此贴有意思！！程序员也蒙圈啊！！！！

[周淡]

偶尔那个脑筋短路呗

[liqi_wj]

那就当楼主 心好 散分吧～～