行转列,需要公司近三十天的销售量(30列)

Question

问题遇到的现象和发生背景

现在我有一张表,其中包含了和订单相关的数据,包括公司key，公司每天的销售额，日期

希望可以看到每个公司近三十天每一天的销量，每个公司单独显示（也就是说我用A公司的账号登录，就只显示A公司的近三十天销量，每天销量单独显示，当日无订单，即为0）
（需做LEFT JOIN与其他表相关联，可使用company_key和sku_key关联上)

问题相关代码，请勿粘贴截图

SELECT
       t.company_key,
       t.spu_key,
       t.sku_key
       --近1天销量
       --近2天销量
       --...
       --近30天销量
FROM t
LEFT JOIN tblB
ON t.company_key = tblB.company_key AND t.sku_key = tblB.sku_key
--其中tblB是包含订单相关数据的表

运行结果及报错内容

无

我的解答思路和尝试过的方法

无

我想要达到的结果

希望可以看到每个公司近三十天每一天的销量，每个公司单独显示（也就是说我用A公司的账号登录，就只显示A公司的近三十天销量，每天销量单独显示，当日无订单，即为0）

Answer 1

可以拿一点真实结构，或者相似的表模型贴出来。

SELECT company_key,
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 0  THEN sum ELSE 0 END) AS '第1天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 1  THEN sum ELSE 0 END) AS '往前第2天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 2  THEN sum ELSE 0 END) AS '往前第3天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 3  THEN sum ELSE 0 END) AS '往前第4天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 4  THEN sum ELSE 0 END) AS '往前第5天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 5  THEN sum ELSE 0 END) AS '往前第6天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 6  THEN sum ELSE 0 END) AS '往前第7天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 7  THEN sum ELSE 0 END) AS '往前第8天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 8  THEN sum ELSE 0 END) AS '往前第9天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 9  THEN sum ELSE 0 END) AS '往前第10天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 10 THEN sum ELSE 0 END) AS '往前第11天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 11 THEN sum ELSE 0 END) AS '往前第12天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 12 THEN sum ELSE 0 END) AS '往前第13天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 13 THEN sum ELSE 0 END) AS '往前第14天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 14 THEN sum ELSE 0 END) AS '往前第15天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 15 THEN sum ELSE 0 END) AS '往前第16天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 16 THEN sum ELSE 0 END) AS '往前第17天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 17 THEN sum ELSE 0 END) AS '往前第18天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 18 THEN sum ELSE 0 END) AS '往前第19天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 19 THEN sum ELSE 0 END) AS '往前第20天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 20 THEN sum ELSE 0 END) AS '往前第21天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 21 THEN sum ELSE 0 END) AS '往前第22天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 22 THEN sum ELSE 0 END) AS '往前第23天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 23 THEN sum ELSE 0 END) AS '往前第24天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 24 THEN sum ELSE 0 END) AS '往前第25天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 25 THEN sum ELSE 0 END) AS '往前第26天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 26 THEN sum ELSE 0 END) AS '往前第27天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 27 THEN sum ELSE 0 END) AS '往前第28天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 28 THEN sum ELSE 0 END) AS '往前第29天',
SUM(CASE WHEN TO_DAYS(NOW() )-TO_DAYS(action_date) = 29 THEN sum ELSE 0 END) AS '往前第30天'
FROM t
WHERE company_key = '';-- 这里用登陆人的公司key

• 还有一个问题，你是需要累加的三十天还是每天的。这个需求没说清楚。

• 上面的sql 只是单表的