时间戳

首页 > 解决方案 > 如何明智地设置结果日期

mysql - 如何明智地设置结果日期

问题描述

我的表有以下记录，并且我需要将结果设置在数据表日期的下方。

date        code      lc_code      qty
7/1/2018    MC20651    1126         322.00
6/1/2018    MC10102    3356         30.00
5/1/2018    MC10201    4422         56.00
7/1/2018    MC10303    5065         55.00
4/1/2018    MC20902    7012         65.00
4/1/2018    MC50201    1258         45.00
3/1/2018    MC10201    1126         86.00
3/1/2018    MC50201    7012         14.25
2/1/2018    MC20651    1258         322.00
7/1/2018    MC20651    3356         78.00

下面是我需要设置为数据表的内容。你能提供最好的查询来做到这一点吗？，我设法得到这些值的总和，但我很难按行获得那个值

|  Code     |   2/1/2018 |  3/1/2018  |   4/1/2018 |    5/1/2018   |  6/1/2018  |    7/1/2018   |
-----------------------------------------------------------------------------------------------
|  MC20651  |  322.00|   78.00|   0.00 |   0.00  |   0.00 | 322.00 |        0.00|           0.00|
------------------------------------------------------------------------------------------------
|  MC10102  |   0.00 |  30.00 |   0.00 |   0.00  |   0.00 |   0.00 |        0.00|           0.00|
------------------------------------------------------------------------------------------------
|  MC10201  |   86.00|   0.00 |  56.00 |   0.00  |   0.00 | 134.25 |        0.00|           0.00|
------------------------------------------------------------------------------------------------
|  MC10303  |   0.00 |   0.00 |   0.00 |  55.00  |   0.00 |   0.00 |        0.00|           0.00|
------------------------------------------------------------------------------------------------
|  MC20902  |   0.00 |   0.00 |   0.00 | 960.00  |  65.00 |   0.00 |        0.00|           0.00|
------------------------------------------------------------------------------------------------
|  MC50201  |   0.00 |   0.00 |   0.00 |   0.00  |  14.25 |   45.00|        0.00|           0.00|
------------------------------------------------------------------------------------------------

标签： mysqlsql

解决方案

您可以使用CASE WHENWithSUM函数来制作它。

SELECT code,
       SUM(CASE WHEN date = '2/1/2018' THEN qty ELSE 0 END) AS `2/1/2018`,
        SUM(CASE WHEN date = '3/1/2018' THEN qty ELSE 0 END) AS `3/1/2018`,
        SUM(CASE WHEN date = '4/1/2018' THEN qty ELSE 0 END) AS `4/1/2018`,
        SUM(CASE WHEN date = '5/1/2018' THEN qty ELSE 0 END) AS `5/1/2018`,
        SUM(CASE WHEN date = '6/1/2018' THEN qty ELSE 0 END) AS `6/1/2018`,
        SUM(CASE WHEN date = '7/1/2018' THEN qty ELSE 0 END) AS `7/1/2018`
FROM T
GROUP BY code

编辑

如果您不想硬编码，可以使用 Dynamic PIVOT

动态创建了您的 SQL，然后使用执行它。

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

看起来像这样。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN date = ''',
      date,
      '''THEN qty ELSE 0 END) AS `',
      date,'`'
    )
  ) INTO @sql
FROM T;

SET @sql = CONCAT('SELECT code,', @sql, ' 
                   FROM T
                   GROUP BY code;');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sqlfiddle

推荐阅读