Question

select语句，多张表的查询

select count(*) a1 from dat_sms_20090301;
select count(*) a2 from dat_sms_20090302;
select count(*) a3 from dat_sms_20090303;
..............
select count(*) a20 from dat_sms_20090320;

我想把每条查询语句查出来的count(*)加起来， 就是a1+a2+a3+....+a20
谁知道有什么好的办法？

Answer 1

表名是按日期生成时可用动态;

declare @s nvarchar(4000),@Date datetime
select @s='',@Date='20090301'
while @Date<='20090320'
select @s=@s+'+(select count(*) from -dat_sms_'+convert(varchar(10),@Date,112)+'])',@Date=@Date+1
set @s=stuff(@s,1,1,'select ')
exec(@s)

Answer 2

select a1.a1+a2.a2+a3.a3+……+a20.a20 from
(select count(*) a1 from dat_sms_20090301) a1,
(select count(*) a2 from dat_sms_20090302) a2,
(select count(*) a3 from dat_sms_20090303) a3,
..............
(select count(*) a20 from dat_sms_20090320) a20