一个统计问题,谢谢了
表A
ID NAME
1 A
2 B
3 C
表B
TABLE_ID DATA
1 XXXXX
1 XXXXX
1 XXXXX
3 YYYYY
3 YYYYY
希望返回
ID NAME COUNTS
1 A 3
3 C 2
没有B,因为表B中没有B的数据
或者
ID NAME COUNTS
1 A 3
2 B 0
3 C 2
2个的存储过程,谢谢了
[解决办法]
select a.id,a.name,count(1) counts from a Inner Join b On a.id=b.id group by a.id,a.name
[解决办法]
一、
select 表A.id as id,表A.name as name,count(*) as counts
from 表A,表B
where 表A.id=表B.TABLE_ID
group by 表A.id,表A.name
order by 表A.id
二、
select 表A.id as id,表A.name as name,isnull(表C.counts,0) as counts
from 表A left join (
select TABLE_ID,count(*) as counts from 表B group by TABLE_ID
) 表C on 表A.id=表C.TABLE_ID
[解决办法]
select a.id,a.name,count(*) counts from a ,b where a.id=b.id group by a.id,a.name
[解决办法]
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(ID int,NAME varchar(10))
insert into A(ID,NAME) values(1, 'A ' )
insert into A(ID,NAME) values(2, 'B ')
insert into A(ID,NAME) values(3, 'C ')
go
if object_id( 'pubs..B ') is not null
drop table B
go
create table B(TABLE_ID int,DATA varchar(10))
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(3, 'YYYYY ')
insert into B(TABLE_ID,DATA) values(3, 'YYYYY ')
go
select A.id , A.name , t.counts from A,
(select table_id , count(*) counts from b group by table_id) t
where a.id = t.table_id
select A.id , A.name , isnull(t.counts,0) counts from A
left join
(select table_id , count(*) counts from b group by table_id) t
on a.id = t.table_id
drop table A,B
/*
id name counts
----------- ---------- -----------
1 A 3
3 C 2
（所影响的行数为 2 行）
id name counts
----------- ---------- -----------
1 A 3
2 B 0
3 C 2
（所影响的行数为 3 行）
*/
[解决办法]
这完全不需要存储过程的,一条语句可以搞定
select a.*,b.COUNTS from A a
left join
(
select TABLE_ID,count(*)as COUNTS from B group by TABLE_ID
) B b
on a.id=b.TABLE_ID
