问一个很简单的SQL写法但是很常用

[delphi2CS]

if object_id('Students') is not null
drop table Students;

create table Students
(
StudentNo varchar(50), --学号
ClassNo varchar(50), --班级
Gender INT --性别 0：女 1：男
)


insert into Students values('00001', '101', 1);
insert into Students values('00002', '101', 1);
insert into Students values('00003', '101', 0);
insert into Students values('00004', '101', 1);
insert into Students values('00005', '101', 0);

insert into Students values('00006', '102', 0);
insert into Students values('00007', '102', 1);
insert into Students values('00008', '102', 0);
insert into Students values('00009', '102', 1);
insert into Students values('00010', '102', 0);

insert into Students values('00011', '103', 1);
insert into Students values('00012', '103', 1);
insert into Students values('00013', '103', 1);
insert into Students values('00014', '103', 1);
insert into Students values('00015', '103', 1);

--统计每个班男女总数

select ClassNo, Gender, count(*) as Students
from Students
group by ClassNo, Gender
order by ClassNo, Gender

--输出结果
ClassNo Gender Students
101 0 2
101 1 3
102 0 3
102 1 2
103 1 5

--希望得到的结果
ClassNo Gender Students
101 0 2
101 1 3
102 0 3
102 1 2
103 0 0 --我想即使女生数量为0，这一行也输出
103 1 5

[yesyesyes]

好像不用那么复杂

加个all就行了

select ClassNo, Gender, count(*) as Students
from Students
group by ALL ClassNo, Gender
order by ClassNo, Gender

[delphi2CS]

--还是没有得到结果哦，为了简单起见我建了一个性别表，加上过滤条件后还是没有得到符合要求的结果

if object_id('Students') is not null
drop table Students;

create table Students
(
StudentNo varchar(50), --学号
ClassNo varchar(50), --班级
Gender INT --性别 0：女 1：男
)

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);
insert into Students values('00002', '101', 1);
insert into Students values('00003', '101', 0);
insert into Students values('00004', '101', 1);
insert into Students values('00005', '101', 0);

insert into Students values('00006', '102', 0);
insert into Students values('00007', '102', 1);
insert into Students values('00008', '102', 0);
insert into Students values('00009', '102', 1);
insert into Students values('00010', '102', 0);

insert into Students values('00011', '103', 1);
insert into Students values('00012', '103', 1);
insert into Students values('00013', '103', 1);
insert into Students values('00014', '103', 1);
insert into Students values('00015', '103', 1);
GO


select 
	cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
	(select distinct ClassNo from Students) cls
	cross join Genders gd
	left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender
group by 
	cls.ClassNo, gd.GenerName
order by 
	cls.ClassNo, gd.GenerName


/*--输出结果，满足要求
101	男	3
101	女	2
102	男	2
102	女	3
103	男	5
103	女	0
*/



--想查102和103班的男女数，结果又不对了
select 
	cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
	(select distinct ClassNo from Students) cls
	cross join Genders gd
	left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender
where 
	std.classno in ('102', '103') 
group by 
	cls.ClassNo, gd.GenerName
order by 
	cls.ClassNo, gd.GenerName

/*--输出结果
102	男	2
102	女	3
103	男	5
*/

/*--期望输出结果
102	男	2
102	女	3
103	男	5
103	女	0
*/

[SQLCenter]

where
cls.classno in ('102', '103')

[fpzgm]

--测试通过
 select 
    cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
    (select distinct ClassNo from Students 
     where classno in ('102', '103')  ) cls   --在这加限制条件
    cross join Genders gd
    left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender

group by 
    cls.ClassNo, gd.GenerName
order by 
    cls.ClassNo, gd.GenerName

/*
102    男    2
102    女    3
103    男    5
103    女    0
*/

[SQL2088]

if object_id('Students') is not null
drop table Students;

create table Students
(
StudentNo varchar(50), --学号
ClassNo varchar(50), --班级
Gender INT --性别 0：女 1：男
)

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);
insert into Students values('00002', '101', 1);
insert into Students values('00003', '101', 0);
insert into Students values('00004', '101', 1);
insert into Students values('00005', '101', 0);

insert into Students values('00006', '102', 0);
insert into Students values('00007', '102', 1);
insert into Students values('00008', '102', 0);
insert into Students values('00009', '102', 1);
insert into Students values('00010', '102', 0);

insert into Students values('00011', '103', 1);
insert into Students values('00012', '103', 1);
insert into Students values('00013', '103', 1);
insert into Students values('00014', '103', 1);
insert into Students values('00015', '103', 1);
GO

select c.ClassNo,c.GenerName,num=sum(case when d.Gender=c.Gender then 1 else 0 end) 
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
where 
    c.classno in ('102', '103') 
group by c.ClassNo,c.GenerName
order by c.ClassNo


ClassNo                                            GenerName            num
-------------------------------------------------- -------------------- -----------
102                                                男                    2
102                                                女                    3
103                                                男                    5
103                                                女                    0

(4 行受影响)

[delphi2CS]

还是不对

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 
    cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
    (select distinct ClassNo from Students) cls
    cross join Genders gd
    left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender
group by 
    cls.ClassNo, gd.GenerName
order by 
    cls.ClassNo, gd.GenerName


/*--输出结果，满足要求
101    男    3
101    女    2
102    男    2
102    女    3
103    男    5
103    女    0
*/



--想查102和103班的男女数，结果又不对了
select 
    cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
    (select distinct ClassNo from Students) cls
    cross join Genders gd
    left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender
where 
    cls.classno in ('102', '103') and std.Age >= 10
group by 
    cls.ClassNo, gd.GenerName
order by 
    cls.ClassNo, gd.GenerName

/*--输出结果
102	男	2
102	女	3
103	男	5
*/

/*--期望输出结果
102    男    2
102    女    3
103    男    5
103    女    0
*/

[「已注销」]

SELECT DISTINCT s.classno, ISNULL(m.GENDER, 0) AS GENDER, ISNULL(m.WW, 0) AS students
FROM         [#Students] AS s LEFT OUTER JOIN
                          (SELECT     classno, GENDER, COUNT(GENDER) AS WW
                            FROM          [#Students] AS s
                            WHERE      (GENDER = 0) and  classno in('102','103')
                            GROUP BY classno, GENDER) AS m ON s.classno = m.classno
where  s.classno in('102','103')                            
UNION
SELECT DISTINCT s.classno, ISNULL(m.GENDER, 0) AS GENDER, ISNULL(m.Man, 0) AS students
FROM         [#Students] AS s LEFT OUTER JOIN
                          (SELECT     classno, GENDER, COUNT(GENDER) AS Man
                            FROM          [#Students] AS s
                            WHERE      (GENDER = 1) 
                            GROUP BY classno, GENDER) AS m ON s.classno = m.classno
where  s.classno in('102','103')                             

classno                                            GENDER      students
-------------------------------------------------- ----------- -----------
102                                                0           3
102                                                1           2
103                                                0           0
103                                                1           5

(4 row(s) affected)

[fpzgm]

--测试通过
 select 
    cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from 
    (select distinct ClassNo from Students 
     where classno in ('102', '103') and  Age >= 10 ) cls   --在这加限制条件
    cross join Genders gd
    left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender

group by 
    cls.ClassNo, gd.GenerName
order by 
    cls.ClassNo, gd.GenerName

/*
102    男    2
102    女    3
103    男    5
103    女    0
*/

[fpzgm]

[Quote=引用 20 楼 delphi2cs 的回复:]
还是不对

SQL code


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 ……
[/Quote]

age 必须限制在cls 里面

[jenny0810]

[Quote=引用 2 楼 sqlcenter 的回复:]
那就比较麻烦了


SQL code
select a.ClassNo, b.Gender, count(c.StudentNo) as Students
from (select distinct ClassNo from Students) a
cross join
(select 0 Gender union all select 1) b
left join
……
[/Quote]up

[fpzgm]

#22这种写法，提前将限制条件写上，执行的效率要比在join之后写上where条件效率要高！

[SQLCenter]

这这这...你没理解left join的精髓

前面搞七搞八的就是让Students里面缺的内容变成NULL，你倒好，第一次 where std.classno in (102,103), 第二次 where ... and std.Age >= 10，前面搞七搞八的功夫白费了。

对Students的筛选要在on筛选里面完成：

    left join  Students std on std.ClassNo=cls.ClassNo and std.Gender=gd.Gender and std.Age>=10
where 
    cls.classno in ('102', '103')-- and std.Age >= 10

[delphi2CS]

[Quote=引用 22 楼 fpzgm 的回复:]
SQL code

--测试通过
select
cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from
(select distinct ClassNo from Students
where classno in ('102', '103') and Age >= 10 )……
[/Quote]

如果我把103班的年龄都改成8，是不是103班都不出来了，呵呵？

[fpzgm]

[Quote=引用 27 楼 delphi2cs 的回复:]
引用 22 楼 fpzgm 的回复:
SQL code

--测试通过
select
cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from
(select distinct ClassNo from Students
where classno in ('102', '103') and Age >= 10……
[/Quote]

8>10?如果>?就会出？不>?还出？

[SQL2088]

[Quote=引用 22 楼 fpzgm 的回复:]
SQL code

--测试通过
select
cls.ClassNo, gd.GenerName, count(std.StudentNo) as Students
from
(select distinct ClassNo from Students
where classno in ('102', '103') and Age >= 10 )……
[/Quote]这个不错

[SQL2088]

这得看看
26楼

[SQLCenter]

[Quote=引用 15 楼 yesyesyes 的回复:]

好像不用那么复杂

加个all就行了

select ClassNo, Gender, count(*) as Students
from Students
group by ALL ClassNo, Gender
order by ClassNo, Gender
[/Quote]

这个我测试了一下，不行，不知道哪个版本或哪种数据库可以这样实现。

[delphi2CS]

看来还得好好学习一下 select * from A left join B ON 条件1 WHERE 条件2 表A和表B的过滤各自应该写在哪里，条件1和条件2的区别在哪里

[delphi2CS]

我刚才想了想，是不是可以这样理解，join 相当于关联，where 是对关联后的结果再过滤
对于inner join 条件1和条件2都是一样
对于left join ，其实关联的结果已经对了，但是where对关联后的结果一过滤，就把满足条件的结果又去掉了
对与right join，和left join 差不多

[SQLCenter]

#33
对头