22,209
社区成员
发帖
与我相关
我的任务
分享
--左连接
declare @表一 table(id nvarchar(20))
insert into @表一
select '01' union all
select '02' union all
select '03'
declare @表二 table(id nvarchar(20),data nvarchar(20))
insert into @表二
select '01','f' union all
select '01','b' union all
select '01','c' union all
select '02','a'
select a.id,count(data) as count
from @表一 a left join @表二 b
on a.id = b.id
group by a.id
create table table1(id varchar(10))
insert table1 select '01' union select '02' union select '03'
create table table2(id varchar(10),date varchar(10))
insert table2 select '01','b'
insert table2 select '01','c'
insert table2 select '02','a'
select id,isnull((select count(*) from table2 where id=a.id),0) as count from table1 a
go
drop table table1,table2
/*
id count
---------- -----------
01 2
02 1
03 0
(3 行受影响)
*/
select id,isnull((select count(*) from table2 where id=a.id),0) as count from table1
select
表一.id,
count(表二.id) row_count
from 表一
left outer join 表二 on 表一.id = 表二.id
group by 表一.id
SELECT a.ID,COUNT(b.ID)
FROM
(
SELECT DISTINCT ID
FROM Table1
) a
LEFT JOIN
(
SELECT ID FROM Table2
) b
ON a.ID=b.ID
GROUP BY a.ID
select m.id , isnull(n.cnt , 0 ) cnt from tb1 m
left join
(select id , count(*) cnt from tb2 group by id) n
on m.id = n.id
select m.id , isnull(n.cnt , 0 ) cnt from tb1 m
left join
(select id , count(*) cnt from tb2 group by id) tb2
on m.id = n.id
SELECT
A.id,
cnt = SUM(1)
FROM 表一 A
LEFT JOIN 表二 B
ON A.id = B.id
GROUP BY A.id