条件加在where 和on后面的特殊比较

-- 条件加在where 和on后面的特殊比较

-- 查找满足班级为102，班的男女生人数，要求年龄大于等于

 

set nocount on

if object_id('Students') is not null

drop table Students;

 

create table Students

(

StudentNo varchar(50), --学号

ClassNo varchar(50), --班级

Gender INT, --性别0：女1：男

Age INT  --年龄

)

go

 

 

if object_id('Genders') is not null

drop table Genders;

 

create table Genders

(

    Gender INT,

    GenerName VARCHAR(20)

)

go

insert into Genders values(0, '女')

insert into Genders values(1, '男')

 

 

go

insert into Students values('00001', '101', 1, 8);

insert into Students values('00002', '101', 1, 8);

insert into Students values('00003', '101', 0, 8);

insert into Students values('00004', '101', 1, 8);

insert into Students values('00005', '101', 0, 8);

 

insert into Students values('00006', '102', 0, 11);

insert into Students values('00007', '102', 1, 11);

insert into Students values('00008', '102', 0, 11);

insert into Students values('00009', '102', 1, 11);

insert into Students values('00010', '102', 0, 11);

 

insert into Students values('00011', '103', 1, 10);

insert into Students values('00012', '103', 1, 10);

insert into Students values('00013', '103', 1, 10);

insert into Students values('00014', '103', 1, 10);

insert into Students values('00015', '103', 1, 10);

GO

 

 

select c.ClassNo,c.GenerName,num= count(d.Gender) 

from

(

    select distinct ClassNo,GenerName,b.Gender  from Students a

    cross join Genders b

)c

left join Students d on c.ClassNo=d.ClassNo and c.Gender=d.Gender and age>=10

where

    c.classno in ('102', '103') --and age>=10

group by c.ClassNo,c.GenerName

order by c.ClassNo

 

 

-- 加在ON后

ClassNo                                            GenerName            num

-------------------------------------------------- -------------------- -----------

102                                                男                   2

102                                                女                   3

103                                                男                   5

103                                                女                   0

 

--加在WHERE 后

 

ClassNo                                            GenerName            num

-------------------------------------------------- -------------------- -----------

102                                                男                   2

102                                                女                   3

103                                                男                   5