mysql - Case、group by、join 多个表

Question

我有 3 个表 transaction 、store 和 date。 存储表中的列需要根据条件分配值，并且新列需要在分组依据中使用。

ASK 是为了查找特定时间段内不同横幅的总销售额。

我正在使用以下查询..

"""select sum(net_spend) as sales , d.fis_week_id, st.banner
    from  ( select s*,
    CASE  WHEN st.format IN ('S','S MINI','S HYPER') THEN 'S'
          WHEN st.format = 'CHECKERS' THEN 'CHECKERS'
          ELSE st.format END AS banner 
          from store_dim s) st
    from transaction_item_fct tr 
    inner join date_dim d on d.date_id = tr.date_id 
    inner join store_dim_c s on st.store_id = tr.store_id
    where d.fis_week_id >=201809 and d.fis_week_id<=201813
    and tr.store_id = st.store_id 
    group by st.banner, d.fis_week_id

“”“

我哪里出错了？

以下是制作的表格数据

交易表-

store_id   week_id   net_spend
  1          12       345
  1          11       788
  2          13       556
  3          11       300

存储表

store_id   format
  1          S
  2          S MINI
  3          S Hyper
  4          Checker

日期表

week_id     fis_week_id
 11          201712
 12          201717

预期结果为

week_id     banner    spend
  11         S         888
  11         Hyper     666

Answer 1

您的问题并不是 100% 清楚，但我认为这个查询会满足您的要求。您需要根据您的需要更改 d.fis_week_id 上的 WHERE 条件，我已使其适合我设置的演示案例。

SELECT d.week_id, st.banner, SUM(t.net_spend) AS sales, d.fis_week_id
FROM date_dim d
LEFT JOIN transaction_item t ON t.week_id = d.week_id
JOIN (SELECT store_id,
             CASE WHEN format IN ('S', 'S MINI', 'S Hyper') THEN 'S'
                  WHEN format = 'Checker' THEN 'Checker'
                  ELSE format
                  END AS banner
      FROM store_dim s) st
    ON st.store_id = t.store_id
WHERE d.fis_week_id BETWEEN 201712 AND 201720
GROUP BY d.week_id, st.banner

我已经扩展了您的演示数据并在 SQLFiddle 创建了一个测试用例:

CREATE TABLE transaction_item
    (`store_id` int, `week_id` int, `net_spend` int);

INSERT INTO transaction_item
    (`store_id`, `week_id`, `net_spend`)
VALUES (1, 12, 345), (1, 11, 788), (2, 13, 556), (3, 11, 300),
       (4, 11, 440), (4, 12, 123), (5, 11, 100), (6, 13, 444);

CREATE TABLE store_dim
    (`store_id` int, `format` varchar(7));

INSERT INTO store_dim
    (`store_id`, `format`)
VALUES (1, 'S'), (2, 'S MINI'), (3, 'S Hyper'), (4, 'Checker'), (5, 'Checker'), (6, 'Other');

CREATE TABLE date_dim
    (`week_id` int, `fis_week_id` int);

INSERT INTO date_dim
    (`week_id`, `fis_week_id`)
VALUES (11, 201712), (12, 201717), (13, 201720);

输出:

week_id     banner      sales   fis_week_id
11          Checker     540     201712
11          S           1088    201712
12          Checker     123     201717
12          S           345     201717
13          Other       444     201720
13          S           556     201720