mysql - 围绕 Mysql 中的日期列旋转表

Question

我正在尝试旋转这张表

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

根据本网站的教程，我有以下代码 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
);

但是由于某种原因，第一个 View 将所有值返回为 null 而不是执行它应该执行的操作 - 即这个

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

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

我已经尝试过将字符串作为主列的相同代码，并且效果很好

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

Answer 1

问题出在这里:

case when dateChecked = '10-09-2017'

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