Skip to main content

MySQL交叉连接子句的一个实际应用

MySQL交叉连接子句简介

假设使用CROSS-join子句连接两个表。结果集将包括两个表中的所有行,其中每一行是第一个表中的行与第二个表中的行的组合。通常,如果每个表分别有n行和m行,那么结果集将有nxm行。

换句话说,CROSS-JOIN子句返回来自连接表的行的笛卡尔积。

下面说明了连接两个表t1和t2的交叉连接子句的语法:

SELECT * FROM t1
CROSS JOIN t2;

注意,与内部连接、左连接和右连接子句不同,交叉连接子句没有连接谓词。换句话说,它没有ON或USING子句。

如果添加WHERE子句,如果表t1和t2有关系,则交叉连接的工作方式与内部连接子句类似,如以下查询所示:

SELECT * FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;

MySQL交叉连接子句示例

让我们设置一些表格来演示交叉连接子句。

建立样本表

首先,创建一个新的数据库salesdb:

CREATE DATABASE IF NOT EXISTS salesdb;

其次,将当前数据切换到新数据库salesdb:

USE salesdb;

第三,在salesdb数据库中创建新表:

CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
price DECIMAL(13,2 )
);

CREATE TABLE stores (
id INT PRIMARY KEY AUTO_INCREMENT,
store_name VARCHAR(100)
);

CREATE TABLE sales (
product_id INT,
store_id INT,
quantity DECIMAL(13 , 2 ) NOT NULL,
sales_date DATE NOT NULL,
PRIMARY KEY (product_id , store_id),
FOREIGN KEY (product_id)
REFERENCES products (id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (store_id)
REFERENCES stores (id)
ON DELETE CASCADE ON UPDATE CASCADE
);

以下是三个表格的说明:

最后,将数据插入三个表中。假设我们有三种产品iPhone、iPad和Macbook Pro,分别在南北两个商店销售。

INSERT INTO products(product_name, price)
VALUES('iPhone', 699),
('iPad',599),
('Macbook Pro',1299);

INSERT INTO stores(store_name)
VALUES('North'),
('South');

INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
(1,2,15,'2017-01-05'),
(1,3,25,'2017-01-05'),
(2,1,30,'2017-01-02'),
(2,2,35,'2017-01-05');

MySQL交叉连接示例

此语句返回每个商店和产品的总销售额,计算销售额并按商店和产品进行分组,如下所示:

SELECT 
store_name,
product_name,
SUM(quantity * price) AS revenue
FROM
sales
INNER JOIN
products ON products.id = sales.product_id
INNER JOIN
stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;

MySQL CROSS JOIN GROUP BY example

现在,如果你想知道哪家商店没有销售特定产品呢。上述问题无法回答这个问题。

要解决这个问题,需要使用交叉连接子句。

首先,使用CROSS-JOIN子句获得所有商店和产品的组合:

SELECT 
store_name, product_name
FROM
stores AS a
CROSS JOIN
products AS b;

MySQL CROSS JOIN stores and products

接下来,将上面的查询结果与一个查询连接起来,该查询返回按商店和产品列出的总销售额。下面的问题说明了这个想法:

SELECT 
b.store_name,
a.product_name,
IFNULL(c.revenue, 0) AS revenue
FROM
products AS a
CROSS JOIN
stores AS b
LEFT JOIN
(SELECT
stores.id AS store_id,
products.id AS product_id,
store_name,
product_name,
ROUND(SUM(quantity * price), 0) AS revenue
FROM
sales
INNER JOIN products ON products.id = sales.product_id
INNER JOIN stores ON stores.id = sales.store_id
GROUP BY stores.id, products.id, store_name , product_name) AS c ON c.store_id = b.id
AND c.product_id= a.id
ORDER BY b.store_name;

MySQL CROSS JOIN query example

请注意,如果收入为NULL(如果商店没有销售额),则查询使用IFNULL函数返回0。

通过这种方式使用交叉连接子句,您可以回答一系列问题,例如,按销售人员、月份查找销售收入,即使销售人员在特定月份没有销售。