create table table1(id1 int,name varchar(20)) Insert into table1 select '1','急' union all select '2','緊急' union all select '3','一般'create table table2(id2 varchar(20), title varchar(20), id1 int) Insert into table2 select '2127','aaa','1' union all select '2128','bbb','1' union all select '2129','ccc','3'select * from table2--刪除 drop table table1 drop table table2select a.name, count=b.count from table1 a left join (select id1,count=count(*) from table2 group by id1)b on a.id1=b.id1--結果 name count -------------------- 急 2 緊急 NULL 一般 1
用第一次也可﹐要加個括號create table table1(id1 int,name varchar(20)) Insert into table1 select '1','急' union all select '2','緊急' union all select '3','一般'create table table2(id2 varchar(20), title varchar(20), id1 int) Insert into table2 select '2127','aaa','1' union all select '2128','bbb','1' union all select '2129','ccc','3'select * from table2--刪除 drop table table1 drop table table2select a.name, count=(select count(*) from table2 where id1=a.id1) from table1 a--結果 name count -------------------- 急 2 緊急 0 一般 1
Select t1.name,t2.cout1 from table1 t1,(Select id1,count(*) as cout1 from table2 group by id1) t2where t1.id1=t2.id1
真厉害向 hdhai9451(※★開拓者...脚妞伤了☆※) 学习
给你个简单的: Select t1.name,count(t2.id1) from table1 t1 left outer join table2 t2 on t1.id1=t2.id1 group by t1.name
create table table1(id1 int,name varchar(20))
Insert into table1
select '1','急'
union all select '2','緊急'
union all select '3','一般'create table table2(id2 varchar(20), title varchar(20), id1 int)
Insert into table2
select '2127','aaa','1'
union all select '2128','bbb','1'
union all select '2129','ccc','3'select * from table2--刪除
drop table table1
drop table table2select a.name, count=b.count from table1 a
left join (select id1,count=count(*) from table2 group by id1)b
on a.id1=b.id1--結果
name count
--------------------
急 2
緊急 NULL
一般 1
Insert into table1
select '1','急'
union all select '2','緊急'
union all select '3','一般'create table table2(id2 varchar(20), title varchar(20), id1 int)
Insert into table2
select '2127','aaa','1'
union all select '2128','bbb','1'
union all select '2129','ccc','3'select * from table2--刪除
drop table table1
drop table table2select a.name, count=(select count(*) from table2 where id1=a.id1) from table1 a--結果
name count
--------------------
急 2
緊急 0
一般 1
hdhai9451(※★開拓者...脚妞伤了☆※)
学习
Select t1.name,count(t2.id1)
from table1 t1 left outer join table2 t2 on t1.id1=t2.id1
group by t1.name