表A auto_Id(pk) courseName
1 销售
2 计算机
3 金融
4 管理表B auto_Id(pk) name A_id
1 one 1
2 two 1
3 three 1
4 aaa 2
5 bbb 2
6 ccc 2
7 ddd 1返回结果应该为 courseName countNum(人数)
销售 4
计算机 3
金融 0
管理 0
-.#
1 销售
2 计算机
3 金融
4 管理表B auto_Id(pk) name A_id
1 one 1
2 two 1
3 three 1
4 aaa 2
5 bbb 2
6 ccc 2
7 ddd 1返回结果应该为 courseName countNum(人数)
销售 4
计算机 3
金融 0
管理 0
-.#
FROM tb1 AS A
LEFT JOIN tb2 AS B
ON A.auto_Id = B.a_id
GROUP BY A.auto_Id,A.courseName;
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-27 18:02:43
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (auto_Id INT,courseName VARCHAR(6))
INSERT INTO @tb1
SELECT 1,'销售' UNION ALL
SELECT 2,'计算机' UNION ALL
SELECT 3,'金融' UNION ALL
SELECT 4,'管理'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (auto_Id INT,name VARCHAR(5),A_id INT)
INSERT INTO @tb2
SELECT 1,'one',1 UNION ALL
SELECT 2,'two',1 UNION ALL
SELECT 3,'three',1 UNION ALL
SELECT 4,'aaa',2 UNION ALL
SELECT 5,'bbb',2 UNION ALL
SELECT 6,'ccc',2 UNION ALL
SELECT 7,'ddd',1--SQL查询如下:SELECT A.auto_Id,A.courseName,COUNT(B.a_id) AS countNum
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.auto_Id = B.a_id
GROUP BY A.auto_Id,A.courseName
ORDER BY COUNT(B.a_id) DESC;/*
auto_Id courseName countNum
----------- ---------- -----------
1 销售 4
2 计算机 3
3 金融 0
4 管理 0(4 行受影响)*/
--> 测试数据:[表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A]([auto_Id] int,[courseName] varchar(6))
insert [表A]
select 1,'销售' union all
select 2,'计算机' union all
select 3,'金融' union all
select 4,'管理'
--> 测试数据:[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([auto_Id] int,[name] varchar(5),[A_id] int)
insert [表B]
select 1,'one',1 union all
select 2,'two',1 union all
select 3,'three',1 union all
select 4,'aaa',2 union all
select 5,'bbb',2 union all
select 6,'ccc',2 union all
select 7,'ddd',1select [courseName],countNum=count(A_id) from [表A] A left join [表B] B on A.auto_Id=B.[A_id]
group by [courseName]
/*
courseName countNum
---------- -----------
管理 0
计算机 3
金融 0
销售 4
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)*/drop table [表A],[表B]
INSERT INTO @tb1
SELECT 1,'销售' UNION ALL
SELECT 2,'计算机' UNION ALL
SELECT 3,'金融' UNION ALL
SELECT 4,'管理'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (auto_Id INT,name VARCHAR(5),A_id INT)
INSERT INTO @tb2
SELECT 1,'one',1 UNION ALL
SELECT 2,'two',1 UNION ALL
SELECT 3,'three',1 UNION ALL
SELECT 4,'aaa',2 UNION ALL
SELECT 5,'bbb',2 UNION ALL
SELECT 6,'ccc',2 UNION ALL
SELECT 7,'ddd',1select courseName , countNum=ISNULL(countNum,0)
from @tb1 k left join (select countNum=COUNT(*),A_id from @tb2 group by A_id) p
on k.auto_Id=p.A_idcourseName countNum
---------- -----------
销售 4
计算机 3
金融 0
管理 0