两张数据表联合查询的问题

[homking]

假设有表A
a_id    a_name
1    test1
2    test2
3    test3


表B
b_id    a_id
1    1
2    2
3    3
4    1
5    2
6    1

现在想要查询结果是列出表A中所有项目在表B中出现的次条数
显示如下
a_id    a_name    a_count
1    test1    3
2    test2    2
3    test3    1

[开着拖拉机泡妞]

看着好晕

[David8977]

select a_id,count(a_id) as a_count from a group by a_id
 

[homking]

假设有表A
a_id a_name
1 test1
2 test2
3 test3


表B
b_id a_id
1 1
2 2
3 3
4 1
5 2
6 1

现在想要查询结果是列出表A中所有项目在表B中出现的次条数
显示如下
a_id a_name a_count
1 test1 3
2 test2 2
3 test3 1

[叶子]

--> 测试数据: @表A
declare @表A table (a_id int,a_name varchar(5))
insert into @表A
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3'

--> 测试数据: @表B
declare @表B table (b_id int,a_id int)
insert into @表B
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,1 union all
select 5,2 union all
select 6,1

select a.a_id ,a.a_name,count(1) as cnt from @表A a left join @表B b
on a.a_id=b.a_id group by a.a_id ,a.a_name
/*
a_id        a_name cnt
----------- ------ -----------
1           test1  3
2           test2  2
3           test3  1
*/

[叶子]

select a.a_id ,a.a_name,count(1) as a_count 
from 表A a left join 表B b
on a.a_id=b.a_id group by a.a_id ,a.a_name

[开着拖拉机泡妞]

select a.*,c.* from a
inner join
(select b.*,count(a_id) as a_count from b group by a_id    a_name    )c
on a.aid=c.aid

[勿勿]

眼睛花了。

[nightgoblin]

[Quote=引用 3 楼 homking 的回复:]
假设有表A
a_id a_name
1 test1
2 test2
3 test3


表B
b_id a_id
1 1
2 2
3 3
4 1
5 2
6 1

现在想要查询结果是列出表A中所有项目在表B中出现的次条数
显示如下
a_id a_name a_count
1 test1 3
2 test2 2
3 test3 1
[/Quote]

USE tempdb;
GO
IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a;
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b;
GO
CREATE TABLE a
(a_id INT,
 a_name VARCHAR(10));
GO
 CREATE TABLE b
 (b_id INT,
  a_id INT);
GO
INSERT INTO a VALUES (1,'test1');
INSERT INTO a VALUES (2,'test2');
INSERT INTO a VALUES (3,'test3');
INSERT INTO a VALUES (4,'test4');
INSERT INTO a VALUES (5,'test5');
INSERT INTO a VALUES (6,'test6');
INSERT INTO b VALUES (1,1);
INSERT INTO b VALUES (2,1);
INSERT INTO b VALUES (3,1);
INSERT INTO b VALUES (4,2);
INSERT INTO b VALUES (5,2);
INSERT INTO b VALUES (6,3);
GO
SELECT a.a_id,a.a_name,COUNT(b.a_id) AS acount FROM b INNER JOIN a ON b.a_id=a.a_id GROUP BY a.a_id,a.a_name; 
--查询结果
a_id	a_name	acount
1	test1	3
2	test2	2
3	test3	1