我想写一个查询,返回三个表t1, t2, t3的记录数,就是类似于select (select count(*) from t1) as count1,
(select count(*) from t2) as count2,
(select count(*) from t3) as count3但是很遗憾,上面的SQL语句无法执行。请问有什么方法能完成上面语句的功能吗
(select count(*) from t2) as count2,
(select count(*) from t3) as count3但是很遗憾,上面的SQL语句无法执行。请问有什么方法能完成上面语句的功能吗
(
select 1 from count1
union all
select 1 from count2
union all
select 1 from count3
)tmp
select count(*) from
(
select col=1 from count1
union all
select 1 from count2
union all
select 1 from count3
)tmp
declare @j smallint
declare @k smallint
set @i=(select count(*)from Customers1)
set @j=(select count(*) from Customers2)
set @k=(select count(*) from Customers3)
print @i,@j,@k
你的方法是返回三行,而不是同一行里的三列fa_ge() :
我希望是一条sql语句。因为希望能用到其它数据库上,比如ACCESS
insert T1 select 1
insert T1 select 2
go
create table t2(id int)
go
create table t3(id int)
insert T3 select 1
go--一行三列
select
count1=(select count(*) from t1),
count2=(select count(*) from t2),
count3=(select count(*) from t3)
--result
count1 count2 count3
----------- ----------- -----------
2 0 1(1 row(s) affected)
insert T1 select 1
insert T1 select 2
go
create table t2(id int)
go
create table t3(id int)
insert T3 select 1
go--一行一列, 三个表的记录数
select [count]=count(*)
from
(
select col=1 from t1
union all
select 1 from t2
union all
select 1 from t3
)tmp
--result
count
-----------
3(1 row(s) affected)
create table t1(id int)
insert T1 select 1
insert T1 select 2
go
create table t2(id int)
go
create table t3(id int)
insert T3 select 1
go--三行两列
select tbName='t1',[count]=count(*) from t1
union all
select tbName='t2',[count]=count(*) from t2
union all
select tbName='t3',[count]=count(*) from t3
--result
tbName count
------ -----------
t1 2
t2 0
t3 1(3 row(s) affected)