SELECT A.Name,
COUNT(B.Name) AS count1,
COUNT(CASE WHEN B.data = 1 THEN 1 END) AS count2
FROM tb1 AS A
LEFT JOIN tb2 AS B
ON A.Name = B.Name
GROUP BY A.Name
COUNT(B.Name) AS count1,
COUNT(CASE WHEN B.data = 1 THEN 1 END) AS count2
FROM tb1 AS A
LEFT JOIN tb2 AS B
ON A.Name = B.Name
GROUP BY A.Name
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-05 15:26:06
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (name VARCHAR(4))
INSERT INTO @tb1
SELECT '小红' UNION ALL
SELECT '小明' UNION ALL
SELECT '小张' UNION ALL
SELECT '小李'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (name VARCHAR(4),data INT)
INSERT INTO @tb2
SELECT '小红',0 UNION ALL
SELECT '小红',0 UNION ALL
SELECT '小明',1 UNION ALL
SELECT '小明',0--SQL查询如下:SELECT A.Name,
COUNT(B.Name) AS count1,
COUNT(CASE WHEN B.data = 1 THEN 1 END) AS count2
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.Name = B.Name
GROUP BY A.Name/*
Name count1 count2
---- ----------- -----------
小红 2 0
小李 0 0
小明 2 1
小张 0 0(4 行受影响)*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([name] varchar(4))
insert [a]
select '小红' union all
select '小明' union all
select '小张' union all
select '小李'
if object_id('[b]') is not null drop table [b]
go
create table [b]([name] varchar(4),[data] int)
insert [b]
select '小红',0 union all
select '小红',0 union all
select '小明',1 union all
select '小明',0
---查询---
select
a.name,
isnull(count(b.data),0) as [count1],
sum(case when b.data=1 then 1 else 0 end) as [count2]
from
a
left join
b
on
a.name=b.name
group by
a.name---结果---
name count1 count2
---- ----------- -----------
小红 2 0
小李 0 0
小明 2 1
小张 0 0(所影响的行数为 4 行)