比如说查询多个表的所有记录条数之和?比如A表有10条记录,B表有20条,就会查询出30条记录
我现在有这种方法:
DECLARE @临时表 TABLE
(
number int
)
INSERT @临时表
SELECT COUNT(*) FROM 表A
UNION ALL
SELECT COUNT(*) FROM 表B
SELECT SUM(number) FROM @临时表就是要用到临时表,效率会不会很低?
有没有更好的方法?
我现在有这种方法:
DECLARE @临时表 TABLE
(
number int
)
INSERT @临时表
SELECT COUNT(*) FROM 表A
UNION ALL
SELECT COUNT(*) FROM 表B
SELECT SUM(number) FROM @临时表就是要用到临时表,效率会不会很低?
有没有更好的方法?
(
select count(*) cnt from A
union all
select count(*) cnt from B
) t
insert into a select 1,'a'
insert into a select 2,'b'
insert into a select 3,'c'
create table b(id int,name varchar(10))
insert into b select 4,'d'
insert into b select 5,'e'
insert into b select 6,'f'
insert into b select 7,'g'declare @sql varchar(max)
select @sql=isnull(@sql+' union all ','')+' select count(*) as [con] from ['+name+']'
from (select name from sysobjects where xtype='U' and (id=object_id('a') or id=object_id('b')))tp
exec( 'select sum(con) as ''行数'' from ('+@sql+')tp')7
我试过了,列名无效(SQL Server 2000)
select sum(cnt)
(
select count(*) cnt from tb4
union all --cnt 列名无效 或者有语法错误
select count(*) cnt from tb5
)
create table a(id int,name varchar(10))
insert into a select 1,'a'
insert into a select 2,'b'
insert into a select 3,'c'
create table b(id int,name varchar(10))
insert into b select 4,'d'
insert into b select 5,'e'
insert into b select 6,'f'
insert into b select 7,'g'select 记录总数 = sum(cnt) from
(
select count(*) cnt from A
union all
select count(*) cnt from B
) tdrop table A , B/*
记录总数
-----------
7(所影响的行数为 1 行)*/
Select Sum(cnt) From
(
Select Count(*) as cnt from tb1
union all
Select Count(*) as cnt from Tb2
)A
(
select count(*) cnt from A
union all -- 去掉from列名(cnt)无并效,不去')'附近有语法错误
select count(*) cnt from B
)
我上面的是在sql server 2000测试后的结果.
use
test
go
select '记录总数' = sum(cnt) from
(
select count(*) cnt from tb1
union all
select count(*) cnt from tb2
)
--服务器: 消息 170,级别 15,状态 1,行 6
--第 6 行: ')' 附近有语法错误。
--我想办法搞张图上来
select '记录总数' = sum(cnt) from
(
select count(*) cnt from tb1
union all
select count(*) cnt from tb2
) t搞什么啊?少个t表别名.........