SQL CROSS JOIN 笛卡尔积 例子

ref： https://blog.csdn.net/xiaolinyouni/article/details/6943337

现在有一个表student 结构如下：
id name class blood
1 张三 1 A
2 李四 2 C
3 王五 1 B
4 黄六 3 D
5 朱八 2 C
现在想查询出每个班的每种血型人数统计（假设只有ABCD四种血型）统计结果如下：
class blood num
1 A 1
1 B 1
1 C 0
1 D 0
2 A 0
2 B 0
2 C 2
2 D 0
3 A 0
3 B 0
3 C 0
3 D 1

如何写SQL?

答案：

if object_id('student') is not null
drop table student
go
create table student(
id int,
[name] varchar(50),
class varchar(50),
blood varchar(50))
 
go
 
insert student 
select 1,'张三','1','A' union all
select 2,'李四','2','C' union all
select 3,'王五','1','B' union all
select 4,'黄六','3','D' union all
select 5,'朱八','2','C'
 
go 
SELECT 
b.class,a.blood,COUNT(s.ID) AS num
FROM 
(SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' )a CROSS JOIN  (SELECT  DISTINCT class  from student)b
LEFT JOIN student s ON a.blood=s.blood AND s.class=b.class
GROUP BY b.class,a.blood
go 
drop table student

分步骤解释：

SELECT * FROM ( --
SELECT  DISTINCT class  from student) c
CROSS JOIN(
SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D')b   ----CROSS先列出所有可能值（枚举）

 

然后left join数据表：

select b.blood, c.class,s.class, s.blood,s.name,s.id from(
--SELECT * FROM ( --
SELECT  DISTINCT class  from student) c
CROSS JOIN(
SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D')b   ----CROSS先列出所有可能值（枚举）
left join
student s ON b.blood = s.blood AND s.class=c.class

 最后一步，group by：

select b.blood, c.class, count(name) as ct from( -- step 4： group by
--select b.blood, c.class,s.class, s.blood,s.name,s.id from( -- step 2: left join
--SELECT * FROM ( --step 1 : cross join
SELECT  DISTINCT class  from student) c
CROSS JOIN(
SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D')b   ----CROSS先列出所有可能值（枚举）
left join
student s ON b.blood = s.blood AND s.class=c.class
--STEP3 , GROUP BY
group by b.blood, c.class
--HAVING  count(name) <> 0

 

 

 加上having：

 

 * 这个用full join还有另外一种解法。

 ==========题外话================

其他关于where和and筛选结果对比：

where条件：

select b.blood, c.class,s.class, s.blood,s.name,s.id from(
--SELECT * FROM ( --
SELECT  DISTINCT class  from student) c
CROSS JOIN(
SELECT 'A'  AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D')b   ----CROSS先列出所有可能值（枚举）
left join
student s ON b.blood = s.blood AND s.class=c.class
WHERE C.CLASS = '1'
--AND C.CLASS = '1'

AND条件：