SELECT SUM(NUM)AS 总和 FROM (SELECT COUNT(*)AS NUM FROM TABLEA UNION ALL SELECT COUNT(*) FROM TABLEB UNION ALL SELECT COUNT(*) FROM TABLEC UNION ALL SELECT COUNT(*) FROM TABLED)AS T
SELECT SUM([COUNT])[COUNT] FROM (SELECT COUNT(*)[COUNT] FROM TABLEA UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEB UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEC UNION ALL SELECT COUNT(*)[COUNT] FROM TABLED
)T
create procedure sp_CalcRecordCount as set nocount on declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int set @RecCount1=count(*) from TableA set @RecCount2=count(*) from TableB set @RecCount3=count(*) from TableC set @RecCount4=count(*) from TableD select @RecCount1+@RecCount2+@RecCount3+@RecCount4 TotalCount return GO
select (select count(*) from TableA)+ (select count(*) from TableB)+ (select count(*) from TableC)+ (select count(*) from TableD)
create proc test ( @a int output ) asselect @a=count(*) from ( select * from tableA union all select * from tableB union all select * from tableC union all select * from tableD
)m
SELECT SUM([COUNT]) as zhCOUNT FROM (SELECT COUNT(*)[COUNT] FROM TABLEA UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEB UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEC UNION ALL SELECT COUNT(*)[COUNT] FROM TABLED )T
求一个就是count(*),求四个就是四个count,怎么这么笨你
create procedure chinalagon_Count as set nocount on declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int select @RecCount1=count(1) from TableA select @RecCount2=count(1) from TableB select @RecCount3=count(1) from TableC select @RecCount4=count(1) from TableD select (@RecCount1+@RecCount2+@RecCount3+@RecCount4) as TotalCount return GO
create proc kkk as begin declare @n int,@n1 int,@n2 int,@n3 int,@n4 int select @n1=count(*) from TABLEA select @n2=count(*) from TABLEb select @n3=count(*) from TABLEc select @n4=count(*) from TABLEd set @n=@n1+@n2+@n3+@n4 print '四表的记录综合为'+convert(varchar(100),@n)
end
create proc aas set nocount on declare @a int set @a=0 select @a=@a+ COUNT(*) from TableA select @a=@a+ COUNT(*) from TableB select @a=@a+ COUNT(*) from TableC select @a=@a+ COUNT(*) from TableD print a return @a set nocount off exec a
create procedure chinalagon_Count(@TotalCount int output ) as set nocount on declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int select @RecCount1=count(1) from TableA select @RecCount2=count(1) from TableB select @RecCount3=count(1) from TableC select @RecCount4=count(1) from TableD select TotalCount = (@RecCount1+@RecCount2+@RecCount3+@RecCount4) return GO
方法太多了。 就是COUNT()呀。
create procedure sp_CalcRecordCount as declare @RecCount int select @RecCount=(select count(*) from TableA)+ (select count(*) from TableB)+ (select count(*) from TableC)+ (select count(*) from TableD)return @RecCount GO
create table tablea(a int) create table tableb(a int) create table tablec(a int) create table tabled(a int)insert tablea values(0) insert tablea values(1) insert tablea values(2) insert tableb values(0) insert tableb values(0) insert tableb values(0) insert tablec values(0) insert tablec values(0) insert tabled values(0) insert tabled values(0) insert tabled values(0) gocreate proc kkk as begin declare @n int,@n1 int,@n2 int,@n3 int,@n4 int select @n1=count(*) from TABLEA select @n2=count(*) from TABLEb select @n3=count(*) from TABLEc select @n4=count(*) from TABLEd set @n=@n1+@n2+@n3+@n4 print '四表的记录综合为'+convert(varchar(100),@n)
end go exec kkk /*--------- 四表的记录综合为11 ---------*/
select sum(rows) from sysindexes where indid in (0,1) and id in (object_id('ta'),object_id('tb'),object_id('tc'),object_id('td'))
create procedure GetRecordCount as begin declare @t1count int,@t2count int,@t3count int,@t4count int; select @t1count=COUNT(1) from TableA; select @t2count=COUNT(1) from TableB; select @t3count=COUNT(1) from TableC; select @t4count=COUNT(1) from TableD;
select @t1count as TableARows ,@t2count as TableBRows ,@t3count as TableCRows ,@t4count as TableDRows ,(@t1count+@t2count+@t3count+@t4count) as TableARows end
SELECT SUM([COUNT])[COUNT] FROM (SELECT COUNT(*)[COUNT] FROM TABLEA UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEB UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEC UNION ALL SELECT COUNT(*)[COUNT] FROM TABLED )T
1.在 northwind 中创建存贮过程如下:ALTER proc tmpprog @recount int output as declare @a1 int,@a2 int,@a3 int,@a4 int select @a1 =count(1) from dbo.Customers select @a2=count(1) from dbo.Employeesselect @a3=count(1) from dbo.Ordersselect @a4=count(1) from dbo.Productsset @recount=@a1+@a2+@a3+@a4 然后如下调用 --调用 declare @i int exec tmpprog @i output select @i
问题思路:就是加的问题,用集合函数 count
select [计数]=sum(id) from (select id=count(*) from ta union all select count(*) from tb union all select id=count(*) from tc union all select id=count(*) from td) a
create table TableA (a int) insert into TableA select 1 union all select 2 union all select 2 create table TableB (a int) insert into TableB select 1 union all select 2 union all select 2 create table TableC (a int) insert into TableC select 1 union all select 2 union all select 2 create table TableD (a int) insert into TableD select 1 union all select 2 union all select 2if object_id('aa') is not null drop proc aacreate proc aa as begin declare @aa int declare @bb int declare @cc int declare @dd int declare @countsum int select @aa=count(1) from TableA select @bb=count(1) from TableB select @cc=count(1) from TableC select @dd=count(1) from TableD set @countsum=@aa+@bb+@cc+@dd print @countsum end exec aa
还是这种方法好(*^__^*)...select sum(sumsum) as sumcount from ( select count(1) as sumsum from TABLEA union all select count(1) from TABLEB union all select count(1) from TABLEC union all select count(1) from TABLED ) as T
create procedure p_sumcount as select [count]=(select count(*) from tableA) +(select count(*) from tableB) +(select count(*) from tableC) +(select count(*) from tableD)
(SELECT COUNT(*)AS NUM FROM TABLEA
UNION ALL
SELECT COUNT(*) FROM TABLEB
UNION ALL
SELECT COUNT(*) FROM TABLEC
UNION ALL
SELECT COUNT(*) FROM TABLED)AS T
FROM
(SELECT COUNT(*)[COUNT] FROM TABLEA
UNION ALL
SELECT COUNT(*)[COUNT] FROM TABLEB
UNION ALL
SELECT COUNT(*)[COUNT] FROM TABLEC
UNION ALL
SELECT COUNT(*)[COUNT] FROM TABLED
)T
create procedure sp_CalcRecordCount
as
set nocount on
declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int
set @RecCount1=count(*) from TableA
set @RecCount2=count(*) from TableB
set @RecCount3=count(*) from TableC
set @RecCount4=count(*) from TableD
select @RecCount1+@RecCount2+@RecCount3+@RecCount4 TotalCount
return
GO
(select count(*) from TableB)+
(select count(*) from TableC)+
(select count(*) from TableD)
(
@a int output
)
asselect @a=count(*) from
(
select * from tableA
union all
select * from tableB
union all
select * from tableC
union all
select * from tableD
)m
(SELECT COUNT(*)[COUNT] FROM TABLEA UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEB
UNION ALL SELECT COUNT(*)[COUNT] FROM TABLEC UNION ALL SELECT COUNT(*)[COUNT] FROM TABLED )T
create procedure chinalagon_Count
as
set nocount on
declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int
select @RecCount1=count(1) from TableA
select @RecCount2=count(1) from TableB
select @RecCount3=count(1) from TableC
select @RecCount4=count(1) from TableD
select (@RecCount1+@RecCount2+@RecCount3+@RecCount4) as TotalCount
return
GO
as
begin
declare @n int,@n1 int,@n2 int,@n3 int,@n4 int
select @n1=count(*) from TABLEA
select @n2=count(*) from TABLEb
select @n3=count(*) from TABLEc
select @n4=count(*) from TABLEd
set @n=@n1+@n2+@n3+@n4
print '四表的记录综合为'+convert(varchar(100),@n)
end
create proc aas
set nocount on
declare @a int
set @a=0
select @a=@a+ COUNT(*) from TableA
select @a=@a+ COUNT(*) from TableB
select @a=@a+ COUNT(*) from TableC
select @a=@a+ COUNT(*) from TableD
print a
return @a
set nocount off
exec a
create procedure chinalagon_Count(@TotalCount int output )
as
set nocount on
declare @RecCount1 int, @RecCount2 int, @RecCount3 int, @RecCount4 int
select @RecCount1=count(1) from TableA
select @RecCount2=count(1) from TableB
select @RecCount3=count(1) from TableC
select @RecCount4=count(1) from TableD
select TotalCount = (@RecCount1+@RecCount2+@RecCount3+@RecCount4)
return
GO
就是COUNT()呀。
as
declare @RecCount int
select @RecCount=(select count(*) from TableA)+
(select count(*) from TableB)+
(select count(*) from TableC)+
(select count(*) from TableD)return @RecCount
GO
create table tableb(a int)
create table tablec(a int)
create table tabled(a int)insert tablea values(0)
insert tablea values(1)
insert tablea values(2)
insert tableb values(0)
insert tableb values(0)
insert tableb values(0)
insert tablec values(0)
insert tablec values(0)
insert tabled values(0)
insert tabled values(0)
insert tabled values(0)
gocreate proc kkk
as
begin
declare @n int,@n1 int,@n2 int,@n3 int,@n4 int
select @n1=count(*) from TABLEA
select @n2=count(*) from TABLEb
select @n3=count(*) from TABLEc
select @n4=count(*) from TABLEd
set @n=@n1+@n2+@n3+@n4
print '四表的记录综合为'+convert(varchar(100),@n)
end
go
exec kkk
/*---------
四表的记录综合为11
---------*/
where indid in (0,1)
and id in (object_id('ta'),object_id('tb'),object_id('tc'),object_id('td'))
as
begin
declare @t1count int,@t2count int,@t3count int,@t4count int;
select @t1count=COUNT(1) from TableA;
select @t2count=COUNT(1) from TableB;
select @t3count=COUNT(1) from TableC;
select @t4count=COUNT(1) from TableD;
select @t1count as TableARows
,@t2count as TableBRows
,@t3count as TableCRows
,@t4count as TableDRows
,(@t1count+@t2count+@t3count+@t4count) as TableARows
end
@recount int output
as declare @a1 int,@a2 int,@a3 int,@a4 int
select @a1 =count(1)
from dbo.Customers
select @a2=count(1)
from dbo.Employeesselect @a3=count(1)
from dbo.Ordersselect @a4=count(1)
from dbo.Productsset @recount=@a1+@a2+@a3+@a4
然后如下调用
--调用
declare @i int
exec tmpprog @i output
select @i
(select id=count(*) from ta
union all select count(*) from tb
union all select id=count(*) from tc
union all select id=count(*) from td) a
create table TableA
(a int)
insert into TableA select 1
union all select 2
union all select 2
create table TableB
(a int)
insert into TableB select 1
union all select 2
union all select 2
create table TableC
(a int)
insert into TableC select 1
union all select 2
union all select 2
create table TableD
(a int)
insert into TableD select 1
union all select 2
union all select 2if object_id('aa') is not null
drop proc aacreate proc aa
as
begin
declare @aa int
declare @bb int
declare @cc int
declare @dd int
declare @countsum int
select @aa=count(1) from TableA
select @bb=count(1) from TableB
select @cc=count(1) from TableC
select @dd=count(1) from TableD
set @countsum=@aa+@bb+@cc+@dd
print @countsum
end
exec aa
(
select count(1) as sumsum from TABLEA
union all select count(1) from TABLEB
union all select count(1) from TABLEC
union all select count(1) from TABLED
) as T
as
select [count]=(select count(*) from tableA)
+(select count(*) from tableB)
+(select count(*) from tableC)
+(select count(*) from tableD)