在Mysql中围绕日期列引用表 [英] PIvoting table around date column in Mysql

问题描述

我正在尝试旋转这张桌子

I am trying to pivot this table

timetableId, assignmentId, dateChecked, hoursWorked
          1,       11,      2017-09-10,     5
          2,       12,      2017-09-10,     5
          3,       13,      2017-09-11,     8
          4,       11,      2017-09-11,     8
          5,       12,      2017-09-11,     8
          6,       13,      2017-09-10,     8

以使其显示

assignmentId, tenth,  eleventh
          11,      5,   8
          12,      5,   8
          13,      0,   8

基于此网站上的教程，我具有以下代码

I have the following code based on the tutorial from this website http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

create view timetable_extended as (
 select
    timetables.assignmentId,
    case when dateChecked = '10-09-2017' then timetables.hoursWorked end as tenth,
    case when dateChecked = '11-09-2017' then timetables.hoursWorked end as eleventh
   from timetables
);

create view timetable_extended_Pivot as (
 select
   assignmentId,
   sum(tenth) as tenth,
   sum(eleventh) as eleventh
 from timetable_extended
 group by assignmentId
);

create view timetable_extended_Pivot_Pretty as (
  select 
  assignmentId, 
  coalesce(tenth, 0) as tenth, 
  coalesce(eleventh, 0) as eleventh
 from timetable_extended_Pivot
);

但是由于某种原因，第一个视图将所有值都返回为null而不是执行应该做的事-即

however for some reason the first view returns all values as null instead of doing what it is supposed to do - namely this

    assignmentId, tenth,  eleventh
          11,      5,       NULL
          11,      NULL     8
          12,      5,       NULL
          12,      NULL     8
          13,      NULL,    8

我做错了什么?我需要将日期转换为字符串吗?

What am I doing wrong? Do I need to convert the date to a string?

我尝试使用与主列相同的字符串代码，并且效果很好

I have tried the same code with string as the main column and it works perfectly

create table User_Items
(
 Cust_Names varchar(10),
 Item_Type  varchar(50),
 Item_Amount float
 );

insert into User_Items values
 ('Alison', 'Computer', 345.39),
 ('Alison', 'Monitor', 123.45),
 ('Jason', 'Computer', 435.34),
 ('Jason', 'Monitor', 158.23),
 ('Jason', 'Software', 243.54);

 create view User_Items_Extended as (
  select
   User_Items.Cust_Names,
   case when Item_Type = "Computer" then Item_Amount end as Computer,
   case when Item_Type = "Monitor" then Item_Amount end as Monitor,
   case when Item_Type = "Software" then Item_Amount end as Software
  from User_Items
 );

create view User_Items_Extended_Pivot as (
 select
  Cust_Names,
  sum(Computer) as Computer,
  sum(Monitor) as Monitor,
  sum(Software) as Software 
  from User_Items_Extended
 group by Cust_Names
);

create view User_Items_Extended_Pivot_Pretty as (
 select 
  Cust_Names, 
  coalesce(Computer, 0) as Computer, 
  coalesce(Monitor, 0) as Monitor, 
  coalesce(Software, 0) as Software
from User_Items_Extended_Pivot
);

推荐答案

问题在这里:

case when dateChecked = '10-09-2017'

使用YYYY-MM-DD格式进行日期比较，而不是MM-DD-YYYY.

Use YYYY-MM-DD format for your date comparisons, not MM-DD-YYYY.

这篇关于在Mysql中围绕日期列引用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！