如何设置结果日期

mo49yndu 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(317)

我的表有下面的记录，我需要的是将下面的结果设置为datatable date-wise。

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

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

|  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|
------------------------------------------------------------------------------------------------

sql mysql

来源：https://stackoverflow.com/questions/51270723/how-to-set-result-date-wise

1条答案

按热度按时间

gab6jxml1#

你可以用 CASE WHEN 与 SUM 功能使之成为可能。

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，然后使用excute。

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公司

赞(0）回复(0）举报 2021-06-21

我来回答

如何设置结果日期

1条答案

相关问题

热门标签

最新问答