关于count(*)的问题

[jw79]

有a,b两个表，并且a.id=b.id
如何用SQL语句来同时在一个视图内得到两个表的行数。

[newly_ignorant]

select a.id,count(a.*),count(b.*) from a,b
where a.id = b.id group by a.id

有我这么写的吗，搞笑

[Kick_hotdog]

看不懂。。。

[newly_ignorant]

select isnull(a.id,b.id) new,count(distinct a.a),count(distinct b.a) from a
full join b
on a.id = b.id group by a.id,b.id
order by new

前提是按ID分组后，字段a唯一，否则继续讨论，赫赫。

[cleo]

说两个表的关系先，

光说a.id=b.id不行，

一对一，不用说，求一个就行了。
一对多，（a是唯一）:select count(distinct a.a),count(distinct b.a) from a inner join b on a.id = b.id
多对多？没有试，

[swordmanli]

上面写得都不对

create view v1
as

select
isnull((select count(*) from a as a2 where a2.id = a1.id), 0),
isnull((select count(*) from b as b2 where b2.id = b2.id), 0)
from a as a1 inner join b as b1 on a1.id = b1.id

[w18ily]

学习ing.......

[奇遇]

study中....... ^_^

[qiubolecn]

有这么复杂么？

select count(*) from
(
select a.id from a,b
where a.id = b.id group by a.id) as table1

[metalwing]

我觉得还是要搞清楚两表间的关系及你到底要什么行数先。

[ayzwd]

create view v1
as
select t1=(select count(*) from table1),t2=(select count(*) from table2)

分别求两个表的行数吗？应该用这个，他们写的都是求两个表有相同关键字的行数