mysql—在sql中基于日期组织数据

x6yk4ghg 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(333)

我的数据集如下所示：

Category    Date
 --------    ----
 Backlog     7/18/18
 Backlog     7/18/18
 Testing     7/18/18
 Done        7/18/18
 Backlog     7/19/18
 Develop     7/19/18
 Backlog     7/20/18
 Done        7/20/18
 Done        7/20/18

我想通过计算/组织转化为：

Backlog     Testing    Develop    Done     Date
 -------     -------    -------    ----     ----
    2           1          0        1      7/18/18
    2           0          1        0      7/19/18
    1           0          0        2      7/20/18

但是，原始数据集每天都在用新的日期更新。第二天，几个新行将被添加到表中，带有backlog/develope/testing/done标签，日期为7/21/18。如何在sql中创建这种转换，同时仍然考虑到数据集每天都在更新这一事实？

sql mysql Dataset Date pivot

来源：https://stackoverflow.com/questions/51429507/organizing-data-based-on-date-in-sql

2条答案

按热度按时间

olmpazwi1#

您需要条件聚合：

select sum(Category = 'Backlog') as Backlog, 
       sum(Category = 'Testing') as Testing,
       sum(Category = 'Develop') as Develop,
       sum(Category = 'Done') as Done,
       date
from table t
group by date;

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

z4bn682m2#

select *
from 
(
  select  Category,count(Category) as Total,Date
  from yourTableName group by Date,Category
) src
pivot
(
  sum(total)
  for Category in ([Backlog],[DONE],[Testing])
) piv;

输出如下：

Date1       Backlog DONE    Testing
2018-07-18  2       1        1
2018-07-19  2       NULL    NULL
2018-07-20  NULL    2       NULL

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

我来回答

mysql—在sql中基于日期组织数据

2条答案

相关问题

热门标签

最新问答