--这样也行 select *,con=(select count(1) from Student where ClassID=t.ID) from class t
select c.id,c.name,count(*)as numbers from class c left join students s on c.id=s.classid group by c.id,c.name
Select Class.*,table1.num from Class Left Outer Join ( Select ClassID ,Count(*) as num From Student GROUP by ClassID ) as table1 on table1.ClassID= Class.ClassID
上一个有点问题 Select Class.*,case when table1.num is null then 0 Else table1.num END from Class Left Outer Join ( Select ClassID ,Count(*) as num From Student GROUP by ClassID ) as table1 on table1.ClassID= Class.ClassID
declare @student table(ID int, Name char(10),ClassID int) insert @student select 1, 'john' , 1 union all select 2, 'steve' , 1 union all select 3, 'william', 2 union all select 4, 'alice' , 2 union all select 5, 'jack ' , 2 union all select 6, 'sam ' , 3 union all select 7, 'godly' , 4 union all select 8, 'july' , 5 /*Class表 */ declare @Class table (id int, name char(20)) insert @Class select 1, 'grade-one' union select 2, 'grade-two' union select 3, 'grade-three' union select 4, 'grade-four' union select 5, 'grade-five' union select 6, 'grade-six ' -->1 select *,con=(select count(1)from @student where classid=a.id) from @class a -->2 select c.id,c.name,count(*) con from @Class c inner join @Student s on c.id=s.Classid group by c.id,c.name
--建立测试数据 create table student(ID int, Name char(10),ClassID int) insert student select 1, 'john' , 1 union all select 2, 'steve' , 1 union all select 3, 'william', 2 union all select 4, 'alice' , 2 union all select 5, 'jack ' , 2 union all select 6, 'sam ' , 3 union all select 7, 'godly' , 4 union all select 8, 'july' , 5
create table class(id int, name char(20)) insert Class select 1, 'grade-one' union select 2, 'grade-two' union select 3, 'grade-three' union select 4, 'grade-four' union select 5, 'grade-five' union select 6, 'grade-six ' --查询 select c.id,c.name, count(s.id) number from class c left join student s on c.id = s.classid group by c.id,c.name order by c.id--结果 id name number --------------------------------------- 1 grade-one 2 2 grade-two 3 3 grade-three 1 4 grade-four 1 5 grade-five 1 6 grade-six 0
请教:1楼的left outer join 和4楼的left join 有什么区别?3楼的con=(select count(1) from Student where ClassID=t.ID) 中count(1)时什么意思? 回复完,我去结贴!
select *,con=(select count(1) from Student where ClassID=t.ID)
from class t
from class c
left join
students s
on c.id=s.classid
group by c.id,c.name
from
Class Left Outer Join (
Select ClassID ,Count(*) as num From Student GROUP by ClassID
) as table1 on table1.ClassID= Class.ClassID
Select Class.*,case when table1.num is null then 0 Else table1.num END
from
Class Left Outer Join (
Select ClassID ,Count(*) as num From Student GROUP by ClassID
) as table1 on table1.ClassID= Class.ClassID
insert @student select
1, 'john' , 1 union all select
2, 'steve' , 1 union all select
3, 'william', 2 union all select
4, 'alice' , 2 union all select
5, 'jack ' , 2 union all select
6, 'sam ' , 3 union all select
7, 'godly' , 4 union all select
8, 'july' , 5 /*Class表 */
declare @Class table (id int, name char(20))
insert @Class select
1, 'grade-one' union select
2, 'grade-two' union select
3, 'grade-three' union select
4, 'grade-four' union select
5, 'grade-five' union select
6, 'grade-six '
-->1
select *,con=(select count(1)from @student where classid=a.id) from @class a
-->2
select c.id,c.name,count(*) con from @Class c inner join @Student s on c.id=s.Classid group by c.id,c.name
create table student(ID int, Name char(10),ClassID int)
insert student select
1, 'john' , 1 union all select
2, 'steve' , 1 union all select
3, 'william', 2 union all select
4, 'alice' , 2 union all select
5, 'jack ' , 2 union all select
6, 'sam ' , 3 union all select
7, 'godly' , 4 union all select
8, 'july' , 5
create table class(id int, name char(20))
insert Class select
1, 'grade-one' union select
2, 'grade-two' union select
3, 'grade-three' union select
4, 'grade-four' union select
5, 'grade-five' union select
6, 'grade-six ' --查询
select c.id,c.name, count(s.id) number from class c left join student s on c.id = s.classid
group by c.id,c.name
order by c.id--结果
id name number
---------------------------------------
1 grade-one 2
2 grade-two 3
3 grade-three 1
4 grade-four 1
5 grade-five 1
6 grade-six 0
回复完,我去结贴!
2取每个ClassID的行数