[Mysql] CROSS JOIN 交叉连接

数据导入

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `scores` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `students` VALUES ('1', 'Odin', '100');
INSERT INTO `students` VALUES ('2', 'Harry', '95');
INSERT INTO `students` VALUES ('3', 'Lee', '90');

students表

交叉连接(CROSS JOIN)，即笛卡尔积，它会将左右两张需要连接的表进行组合。例如，左表有200行，右表有100行，则交叉连接的结果有200*100行，即2000行

语法格式

SELECT * FROM table1 CROSS JOIN table2;

使用交叉连接会将两个表中所有的数据两两组合

例子:对"students表"自身进行交叉连接

SELECT *
FROM students AS s1 CROSS JOIN students AS s2;

结果展示:

除了上述的写法，交叉连接也可以通过如下代码进行实现:

SELECT * FROM students AS s1, students AS s2;

两种实现方法的结果相同

---

练习案例1:比较日期数据

数据导入

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  `turnover` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `sales` VALUES ('1', '2019-01-01', '97');
INSERT INTO `sales` VALUES ('2', '2019-01-02', '87');
INSERT INTO `sales` VALUES ('3', '2019-01-03', '88');
INSERT INTO `sales` VALUES ('4', '2019-01-04', '98');
INSERT INTO `sales` VALUES ('5', '2019-01-05', '100');
INSERT INTO `sales` VALUES ('6', '2019-01-06', '80');
INSERT INTO `sales` VALUES ('7', '2019-01-07', '77');
INSERT INTO `sales` VALUES ('8', '2019-01-08', '92');

sales表(日销表)

date:日期  turnover:营业额 

问题:请找出所有比前一天(昨天)营业额更高的数据

说明:前一天的意思是如果"当天"是2号，"昨天"(前一天)就是1号

解题步骤:

1.将sales表进行交叉连接

SELECT * FROM sales AS a CROSS JOIN sales AS b;

2.选出"a.日期比b.日期大一天"的数据

使用DATEDIFF(a.date, b.date) = 1或者TIMESTAMPDIFF(DAY, a.date, b.date) = -1 

-- DATEDIFF函数
SELECT * 
FROM sales AS a 
CROSS JOIN sales AS b
ON DATEDIFF(a.date, b.date) = 1;

-- TIMESTAMPDIFF函数
SELECT * 
FROM sales AS a 
CROSS JOIN sales AS b
ON TIMESTAMPDIFF(DAY, a.date, b.date) = -1;

3.找出a中销售额大于b中的销售额的数据

WHERE a.turnover > b.turnover 

-- DATEDIFF函数
SELECT * 
FROM sales AS a 
CROSS JOIN sales AS b
ON DATEDIFF(a.date, b.date) = 1
WHERE a.turnover > b.turnover;

-- TIMESTAMPDIFF函数
SELECT * 
FROM sales AS a 
CROSS JOIN sales AS b
ON TIMESTAMPDIFF(DAY, a.date, b.date) = -1
WHERE a.turnover > b.turnover;

4.删除多余数据

题目只需要查找销售额大于前一天的id、日期(date)、销售额(turnover)

-- DATEDIFF函数
SELECT a.id, a.date, a.turnover
FROM sales AS a 
CROSS JOIN sales AS b
ON DATEDIFF(a.date, b.date) = 1
WHERE a.turnover > b.turnover;

-- TIMESTAMPDIFF函数
SELECT a.id, a.date, a.turnover 
FROM sales AS a 
CROSS JOIN sales AS b
ON TIMESTAMPDIFF(DAY, a.date, b.date) = -1
WHERE a.turnover > b.turnover;

结果展示:

---

补充知识

扩展补充 | DATEDIFF函数

扩展补充 | TIMESTAMPDIFF函数