Mysql报告丢失数据

Question

我有以下表格：

命令：

|order_id|customer_id|      order_date      |item_id|quantity|
|--------|-----------|----------------------|-------|--------|
|   1    |  1        |  2020-06-01 00:00:00 |   1   |      10|
|   2    |  1        |  2020-06-08 00:00:00 |   2   |      10|
|   3    |  2        |  2020-06-02 00:00:00 |   1   |      5 |
|   4    |  3        |  2020-06-03 00:00:00 |   3   |      5 |
|   5    |  4        |  2020-06-04 00:00:00 |   4   |      1 |
|   6    |  4        |  2020-06-05 00:00:00 |   5   |      5 |
|   7    |  5        |  2020-06-05 00:00:00 |   1   |      10|
|   8    |  5        |  2020-06-14 00:00:00 |   4   |      5 |
|   9    |  5        |  2020-06-21 00:00:00 |   3   |      5 |
|   5    |  4        |  2020-06-08 00:00:00 |   4   |      1 |

项目：

|item_id|item_name          |item_category|
|-------|---------  --------|-------------|
|   1   |LC Alg. Book       |Book         |
|   2   |LC DB. Book        |Book         |
|   3   |LC SmarthPhone     |Phone        |
|   4   |LC Phone 2020      |Phone        |
|   5   |LC SmartGlass      |Glasses      |
|   6   |LC T-Shirt XL      |T-Shirt      |

我正在创建一个查询，它应该返回一个报告，说明每一个item_category中有多少个单元是在一周的每一天被订购的。报告应如下所示：

+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

下面是我的查询，除了一种情况外，所有的查询都是准确的--周四的“Phone”应该是qty =1，但我的查询返回0。这个量在cte2中准确地返回，但是我在cte3中丢失了它。不知道为什么。我的问题出什么问题了？

with cte1 as 
    (select * from
        (select "Monday" as day_name union 
        select "Tuesday" union
        select "Wednesday" union
        select "Thursday " union
        select "Friday" union
        select "Saturday " union
        select "Sunday" ) t1
     cross join
        (select distinct item_category from items) t2),
cte2 as 
    (select i.item_category, 
        dayname(order_date) as day_name,
        sum(quantity) as tt_qty
    from orders o
    inner join items i 
    on i.item_id  = o.item_id 
    group by i.item_category, dayname(order_date)),
cte3 as
    (select cte1.day_name,
        cte1.item_category,
        coalesce(cte2.tt_qty, 0) as tt_qty
    from cte1
    left join cte2
    on cte2.day_name = cte1.day_name
    and cte2.item_category = cte1.item_category)
select item_category as category, 
    coalesce (max(case when cte3.day_name ='Monday' then cte3.tt_qty 
        end),0) as Monday,
    coalesce (max(case when cte3.day_name ='Tuesday' then cte3.tt_qty 
        end),0) as Tuesday,
    coalesce (max(case when cte3.day_name ='Wednesday' then cte3.tt_qty 
        end),0) as Wednesday,
    coalesce (max(case when cte3.day_name ='Thursday' then cte3.tt_qty
        end),0) as Thursday,
    coalesce (max(case when cte3.day_name ='Friday' then cte3.tt_qty 
        end),0) as Friday,
    coalesce (max(case when cte3.day_name ='Saturday' then cte3.tt_qty 
        end),0) as Saturday,
    coalesce (max(case when cte3.day_name ='Sunday' then cte3.tt_qty 
        end),0) as Sunday       
from cte3
group by item_category
order by 1

DBfiddle链接：https://www.db-fiddle.com/#&togetherjs=sD7fqL9voK

Answer 1

在cte1的星期四和星期六的"y“之后，您有一个空间，因此连接不能像预期的那样工作，合并将返回所有周四/周六的零。以下cte1的替换将使查询按预期的方式工作。

with cte1 as 
    (select * from
        (select 1 as wd, "Monday" as day_name union all
        select 2, "Tuesday" union all
        select 3, "Wednesday" union all
        select 4, "Thursday" union all ## you had a space after "Thursday "
        select 5, "Friday" union all
        select 6, "Saturday" union all ## you had a space after "Saturday "
        select 7, "Sunday" ) t1
     cross join
        (select distinct item_category from items) t2),

见: dbfiddle.uk https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4a0502c1b3cd021385bae20e2630f924

我为平日引入了一个整数，因为它使订购变得更容易，还使用了union all而不是union。

Answer 2

使用mySQL 8.0，我的解决方案只有3个选择和1个JOIN：

(但由于定单表中的电话/4 2020-06-08星期一项，周一电话项目的值为0)。

CREATE TABLE orders_tab
(order_id INT, customer_id INT, order_date DATE, item_id INT, qty INT);
INSERT INTO orders_tab VALUES
(1, 1, '2020-06-01', 1, 10),
(2, 1, '2020-06-08', 2, 10),
(3, 2, '2020-06-02', 1, 5),
(4, 3, '2020-06-03', 3, 5),
(5, 4, '2020-06-04', 4, 1),
(6, 4, '2020-06-05', 5, 5),
(7, 5, '2020-06-05', 1, 10),
(8, 5, '2020-06-14', 4, 5),
(9, 5, '2020-06-21', 3, 5),
(5, 4, '2020-06-08', 4, 1);
CREATE TABLE items_tab
(item_id INT, item_name VARCHAR(22), item_cat VARCHAR(9));
INSERT INTO items_tab VALUES
(1, 'LC Alg. Book', 'Book'),
(2, 'LC DB. Book', 'Book'),
(3, 'LC SmarthPhone', 'Phone'),
(4, 'LC Phone 2020', 'Phone'),
(5, 'LC SmartGlass', 'Glasses'),
(6, 'LC T-Shirt XL', 'T-Shirt');
/*___________________________________*/
WITH cte AS (
    SELECT item_cat, WEEKDAY(order_date) AS week_day, OT.item_id, SUM(qty) AS items_qty_by_day
    FROM orders_tab OT
    RIGHT JOIN items_tab IT ON (OT.item_id=IT.item_id)
    GROUP BY week_day, OT.item_id
), cte2 AS (
    SELECT week_day, item_cat, SUM(items_qty_by_day) AS items_qty_by_day 
    FROM cte
    GROUP BY week_day, item_cat
)
SELECT item_cat, 
    SUM(IF(week_day=0, items_qty_by_day, 0)) AS monday,
    SUM(IF(week_day=1, items_qty_by_day, 0)) AS tuesday,
    SUM(IF(week_day=2, items_qty_by_day, 0)) AS wednesday,
    SUM(IF(week_day=3, items_qty_by_day, 0)) AS thursday,
    SUM(IF(week_day=4, items_qty_by_day, 0)) AS friday,
    SUM(IF(week_day=5, items_qty_by_day, 0)) AS saturday,
    SUM(IF(week_day=6, items_qty_by_day, 0)) AS sunday
FROM cte2
GROUP BY item_cat