lotus

贵有恒何必三更眠五更起 最无益只怕一日曝十日寒

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

1. cross join简介

MySQL cross join是mysql中的一种连接方式,区别于内连接和外连接,对于cross join连接来说,其实使用的就是笛卡尔连接。在MySQL中,当CROSS JOIN不使用WHERE子句时,CROSS JOIN产生了一个结果集,该结果集是两个关联表的行的乘积。通常,如果每个表分别具有n和m行,则结果集将具有n*m行

引用https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php的图片,如图演示了cross join的过程,这个过程其实就是笛卡尔连接查询
在这里插入图片描述

2. cross join用法

cross join用法:

  1. SELECT * FROM t1
  2. CROSS JOIN t2;

注意:cross join的时候是不需要on或者using关键字的,这个是区别于inner join和join的

如果WHERE在条件表中添加一个子句t1并t2具有关系,则CROSS JOIN该INNER JOIN子句的工作方式类似于以下查询中所示:

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

ok,再列举一下cross join表作为衍生表的例子

  1. SELECT *
  2. FROM table111
  3. LEFT JOIN(table112 CROSS JOIN table113)
  4. ON table111.id=table113.id;

ok,介绍了cross join的简单用法,现在拿http://www.mysqltutorial.org/mysql-cross-join/的例子来介绍:

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

  1. CREATE DATABASE IF NOT EXISTS salesdb;

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

  1. USE testdb;

在salesdb数据库中创建新表:

  • 该表 products包含产品主数据,其中包括产品ID,产品名称和销售价格。
  • 该表stores包含出售产品的商店。
  • 该表sales包含按数量和日期在特定商店中出售的产品。

    CREATE TABLE products (

    1. id INT PRIMARY KEY AUTO_INCREMENT,
    2. product_name VARCHAR(100),
    3. price DECIMAL(13,2 )

    );

    CREATE TABLE stores (

    1. id INT PRIMARY KEY AUTO_INCREMENT,
    2. store_name VARCHAR(100)

    );

    CREATE TABLE sales (

    1. product_id INT,
    2. store_id INT,
    3. quantity DECIMAL(13 , 2 ) NOT NULL,
    4. sales_date DATE NOT NULL,
    5. PRIMARY KEY (product_id , store_id),
    6. FOREIGN KEY (product_id)
    7. REFERENCES products (id)
    8. ON DELETE CASCADE ON UPDATE CASCADE,
    9. FOREIGN KEY (store_id)
    10. REFERENCES stores (id)
    11. ON DELETE CASCADE ON UPDATE CASCADE

    );

将数据插入三个表中。假设我们有三个产品iPhone,iPad并且Macbook Pro其在两个商店出售North和South。

  1. INSERT INTO products(product_name, price)
  2. VALUES('iPhone', 699),
  3. ('iPad',599),
  4. ('Macbook Pro',1299);
  5. INSERT INTO stores(store_name)
  6. VALUES('North'),
  7. ('South');
  8. INSERT INTO sales(store_id,product_id,quantity,sales_date)
  9. VALUES(1,1,20,'2017-01-02'),
  10. (1,2,15,'2017-01-05'),
  11. (1,3,25,'2017-01-05'),
  12. (2,1,30,'2017-01-02'),
  13. (2,2,35,'2017-01-05');

ok,业务场景:现在要统计每个商店每种商品总共营业额是多少钱?

很显然,用SUM(quantity * price),再group by一下就可以,这个sql很好写

  1. SELECT
  2. sto.`store_name`,
  3. pro.`product_name`,
  4. SUM(quantity * price) AS revenue
  5. FROM
  6. sales sal
  7. INNER JOIN stores sto
  8. ON sto.`id` = sal.`store_id`
  9. INNER JOIN products pro
  10. ON sal.`product_id` = pro.`id`
  11. GROUP BY sto.`store_name`,pro.`product_name`;

在这里插入图片描述
ok,看了一下,发现没卖出的商品是没统计出来的,所以不太符合业务需求,业务是要统计所有的商店商品,所以可以用cross join笛卡尔连接,得出所有的商店商品组合数据

笛卡尔查询组合数据sql:

  1. SELECT
  2. a.`store_name`,
  3. b.product_name
  4. from stores cross join products

在这里插入图片描述

前面统计sql已经有了,所以将组合数据SQL和统计数据的SQL进行关联:

  1. SELECT
  2. a.`store_name`,
  3. b.product_name,
  4. IFNULL(c.revenue, 0) AS revenue
  5. FROM
  6. stores a
  7. CROSS JOIN products b
  8. LEFT JOIN
  9. (SELECT
  10. sto.`id` AS store_id,
  11. pro.`id` AS product_id,
  12. sto.`store_name`,
  13. pro.`product_name`,
  14. SUM(quantity * price) AS revenue
  15. FROM
  16. sales sal
  17. INNER JOIN stores sto
  18. ON sto.`id` = sal.`store_id`
  19. INNER JOIN products pro
  20. ON sal.`product_id` = pro.`id`
  21. GROUP BY sto.`store_name`,
  22. pro.`product_name`) c
  23. ON a.id = c.store_id
  24. AND b.id = c.product_id
  25. ORDER BY a.store_name ;

在这里插入图片描述

请注意,IFNULL如果收入为NULL (在商店没有销售的情况下),查询使用该函数返回0。

通过CROSS JOIN这种方式使用该子句,您可以回答广泛的问题,例如,按销售员,月份查找销售收入,即使该销售员在特定月份没有销售。

ok,本博客是翻译两篇英文博客的:

  • http://www.mysqltutorial.org/mysql-cross-join/
  • https://www.w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php

ok,本博客内容翻译自两篇英文博客,不过本博客进行一定修整,将两篇博客内容进行理解整合成这篇中文博客,原因是这两篇博客的例子还是不错的,举出了cross join的常用使用场景,当然除了两篇博客提出的用法,cross join因为其笛卡尔连接的特性,还可以用于批量写数据,对应批量的写法,可以参考我之前的MySQL博客,本博客性质属于翻译的,所以转载请注明出处

posted on 2022-07-21 17:56  白露~  阅读(999)  评论(0编辑  收藏  举报