时间戳

首页 > 解决方案 > 将多行数据合并为一个

mysql - 将多行数据合并为一个

问题描述

第一个问题在这里，所以我会尽力说清楚。

我有 2 张桌子：

  1. “TABLE1”包含每个股票代码的记录和属性列表。在表 1 中，每个 stock_code 只有一条记录
  2. “TABLE2”包含产品属性随时间变化的日志。

“TABLE2”包含以下字段：。

  1. 股票代码
  2. stock_attribute
  3. 旧值
  4. 新值
  5. 改变日期
  6. 更改时间

表 2 具有每个 stock_code 的多个条目。

每次更改库存项目时，都会在表2中创建另一个条目，其中包含已更改的属性，更改日期，时间，旧值和新值。

我想创建一个查询，该查询将生成一个表，其中每个 stock_code 都有一条记录（来自表 1），以及过去一年中每周的一列，每个字段中的值是最后记录的“new_val”周（来自表 2）

我努力了

SELECT a.`stcode`, b.`week1`, b.`week2`, b.`week3`, b.`week4` etc. etc.
 from (SELECT stcode, )as a
 LEFT JOIN (SELECT stcode, 
(CASE WHEN chngdate BETWEEN DATE_SUB(CURDATE(),INTERVAL 363 DAY)  AND DATE_SUB(CURDATE(),INTERVAL 357 DAY)  THEN newval END)week1,
(CASE WHEN chngdate BETWEEN DATE_SUB(CURDATE(),INTERVAL 356 DAY)  AND DATE_SUB(CURDATE(),INTERVAL 350 DAY)  THEN newval END)week2,
(CASE WHEN chngdate BETWEEN DATE_SUB(CURDATE(),INTERVAL 349 DAY)  AND DATE_SUB(CURDATE(),INTERVAL 343 DAY)  THEN newval END)week3,
(CASE WHEN chngdate BETWEEN DATE_SUB(CURDATE(),INTERVAL 342 DAY)  AND DATE_SUB(CURDATE(),INTERVAL 336 DAY)  THEN newval END)week4,
(etc
etc
etc


    FROM (SELECT * from TABLE 2  ORDER BY "chngdate" DESC, "chngtime" DESC )as sub) as b ON b.stcode = s.stcode
ORDER BY stcode ASC

问题在于，我得到了包含多个条目的 stock_code 的多行......例如，对于 stock_code abc123，我得到的结果是

STCODE       WEEK1     WEEK2     WEEK3      WEEK4      WEEK5     WEEK6
abc123        null      null       4         null      null       null
abc123        2         null       null      null      null       null
abc123        null      null       null      null      3          null

我想要的是：

STCODE       WEEK1     WEEK2     WEEK3      WEEK4      WEEK5     WEEK6
abc123        2         null       4         null      3        null

我也尝试了以下方法，但是查询花了很长时间，它从未完成（有 52 个派生表！）

SELECT a.`stcode`, w1.`new_value`, w2.`new_value`, w3.`new_value`, w4.`new_value` etc. etc.
 from (SELECT stcode, )as a
 LEFT JOIN (SELECT stcode, 
LEFT JOIN (SELECT stcode, depot, fieldname, chngdate, chngtime, newval from STDepotAmendmentsLog WHERE chngdate BETWEEN DATE_SUB(CURDATE(),INTERVAL 363 DAY)  AND DATE_SUB(CURDATE(),INTERVAL 357 DAY)  ORDER BY "chngdate" DESC, "chngtime" DESC)as w1 on s.stcode = w1.stcode
etc for week 2, 3, 4 etc etc

标签： mysqlaggregate-functions

解决方案

您可以执行以下操作：

  1. 查找每个“周”的最佳日期
  2. 查找与这些日期对应的行
  3. 使用条件聚合将行转换为列

这是代码的粗略轮廓。它假设例如，如果今天是 2020-03-03，那么第 52 周是从 2020-02-26 到 2020-03-03。必要时进行调整：

SELECT t.stock_code
     , MAX(CASE WHEN weeknum = 51 THEN new_value END) AS week01
     , MAX(CASE WHEN weeknum = 50 THEN new_value END) AS week02
     , MAX(CASE WHEN weeknum =  1 THEN new_value END) AS week51
     , MAX(CASE WHEN weeknum =  0 THEN new_value END) AS week52
FROM table2 AS t
JOIN (
    SELECT stock_code
         , DATEDIFF(CURRENT_DATE, change_date) div 7 AS weeknum -- count multiples of 7
         , MAX(change_date) AS greatest_date
    GROUP BY stock_code, weeknum
    FROM table2
)  AS a ON t.stock_code = a.stock_code AND t.change_date = a.greatest_date
GROUP BY t.stock_code

推荐阅读