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唯一,否则继续讨论,赫赫。
说两个表的关系先,光说a.id=b.id不行,一对一,不用说,求一个就行了。 一对多,(a是唯一):select count(distinct a.a),count(distinct b.a) from a inner join b on a.id = b.id 多对多?没有试,
上面写得都不对create view v1 asselect 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
有这么复杂么?select count(*) from ( select a.id from a,b where a.id = b.id group by a.id) as table1
create view v1 as select t1=(select count(*) from table1),t2=(select count(*) from table2)分别求两个表的行数吗?应该用这个,他们写的都是求两个表有相同关键字的行数
full join b
on a.id = b.id group by a.id,b.id
order by new前提是按ID分组后,字段a唯一,否则继续讨论,赫赫。
一对多,(a是唯一):select count(distinct a.a),count(distinct b.a) from a inner join b on a.id = b.id
多对多?没有试,
asselect
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
(
select a.id from a,b
where a.id = b.id group by a.id) as table1
as
select t1=(select count(*) from table1),t2=(select count(*) from table2)分别求两个表的行数吗?应该用这个,他们写的都是求两个表有相同关键字的行数