mysql 的复杂查询

在做统计的时候，经常使用到外连接和全连接，所以以统计的例子记录mysql的连接方式。

表格结构如下

 表格存储的数据

内连接，所取的数据是，某个字段相同的数据。

按上面的表格，需求：在2月1号到5月8号中，有那一天是商品全都卖出去的，并统计其销售额和营销量

如图，求的便是三个类型共有的数据，mysql如下

SELECT 
    t1.date AS dateStr,
    t1.commoditycount AS vegCount,
    t1.commodityprice AS vegPrice,
    t2.commoditycount AS mealCount,
    t2.commodityprice AS mealPrice,
    t3.commoditycount AS fruitCount,
    t3.commodityprice AS fruitPrice
FROM
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
        inner join
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date
        inner join
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 3
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t3 ON t2.date = t3.date

外连接分为左外连接和右外连接。有A和B两个表，A表与B表左外连接，则以A表做基础，右外连接以B表做基础，其区别如下图所示

 左连接查询类型为1和2的商品，在2月1号到5月8号中的营销量和销售额

SELECT 
    t1.date AS dateStr,
    t1.commoditycount AS mealCount,
    t1.commodityprice AS mealPrice,
    t2.commoditycount AS fruitCount,
    t2.commodityprice AS fruitPrice
FROM(SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1 
        left outer JOIN
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date

右连接查询类型为1和2的商品，在2月1号到5月8号中的营销量和销售额

SELECT 
    t2.date AS dateStr,
    t1.commoditycount AS mealCount,
    t1.commodityprice AS mealPrice,
    t2.commoditycount AS fruitCount,
    t2.commodityprice AS fruitPrice
FROM(SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1 
        right outer JOIN
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date 

全连接，在mysql中，是没有全连接（full join）的这个 关键字的，所以我们需要用外连接实现全连接

表中有三种类型的食品，需要统计每天的销售额和营销量

先统计类型为1和类型为2的食品

mysql：

SELECT 
        t1.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    LEFT OUTER JOIN 
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date

UNION

SELECT 
        t2.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    RIGHT OUTER JOIN (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date 

类型为1的食品数据与类型为2的食品数据左连接，得到以类型为1基础数据+1，2共有的数据，即 

 类型为1的食品数据与类型为2的食品数据右连接，得到以类型为2基础数据+1，2共有的数据，即 

最后使用关键字  UNION，将两个表合并

将类型1与类型2的结果集当做一个新的表t12，与类型3的表t3做左右外连接，过程与上面的一致

 SELECT 
    t12.dateStr,
    t12.vegCount,
    t12.vegPrice,
    t12.mealCount,
    t12.mealPrice,
    t3.commoditycount AS fruitCount,
    t3.commodityprice AS fruitPrice
FROM
    (SELECT 
        t1.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    LEFT OUTER JOIN 
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date UNION SELECT 
        t2.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    RIGHT OUTER JOIN (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date) AS t12
        LEFT OUTER JOIN
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS dateStr,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 3
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY dateStr) AS t3 ON t12.dateStr = t3.dateStr
 

 

  union

    SELECT 
    t3.dateStr,
    t12.vegCount,
    t12.vegPrice,
    t12.mealCount,
    t12.mealPrice,
    t3.commoditycount AS fruitCount,
    t3.commodityprice AS fruitPrice
FROM
    (SELECT 
        t1.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    LEFT OUTER JOIN (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date UNION SELECT 
        t2.date AS dateStr,
            t1.commoditycount AS vegCount,
            t1.commodityprice AS vegPrice,
            t2.commoditycount AS mealCount,
            t2.commodityprice AS mealPrice
    FROM
        (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 1
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t1
    RIGHT OUTER JOIN (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS date,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 2
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY date) AS t2 ON t1.date = t2.date) AS t12
        RIGHT OUTER JOIN
    (SELECT 
        DATE_FORMAT(createTime, '%Y-%m-%d') AS dateStr,
            SUM(count) AS commoditycount,
            SUM(price) AS commodityprice
    FROM
        test.commodity_info
    WHERE
        type = 3
            AND createTime >= '2019-02-01 00:00:00'
            AND createTime <= '2019-05-08 00:00:00'
    GROUP BY dateStr) AS t3 ON t12.dateStr = t3.dateStr
    order by dateStr

mysql的复杂查询。