将报表组合在一起，可能使用SQL

Question

我有一个名为Register的表，其中包含以下字段:

日期，时间，标记。

一天可以有两条记录。按日期升序排序的列表中选择和显示所有记录是相当容易的。

我想做的是将数据显示为网格。类似于。

         | Mon | Tues| Wed| Thurs| Fri | Sat
9/8/2014 | /  | /P  | / |   L  | /  | /

有一个星期开始，然后将5组在一起。我甚至不确定sql是否是最好的选择，但是groupby命令似乎表明它可能能够做到这一点。

数据结构如下:

Date, AMPM, Mark
9/8/2014, AM, /
9/8/2014, PM, 
9/9/2014, AM, /
9/9/2014, PM, P
9,10,2014, AM, /
9,10,2014, PM, 
9,11,2014, PM, L

…

标记字段可以包含多个字母。例如，P意味着他们正在参加体育活动。L表示他们迟到了。

有没有人有任何资源可以为我指出正确的方向，这将是有帮助的。我甚至不确定这种类型的报告被称为什么，以及我是否应该使用SQL或javascript以可呈现的格式对这些数据进行分组。/表示AM, a表示PM。

Answer 1

下面的查询将得到您想要的结果。如果还需要Sunday，则必须在CASE语句中添加一个小条件来测试when days_after_last_Monday = 6。

select
  last_Monday Week_Starting,
  max(
    case
      when days_after_last_Monday = 0 then mark
      else null
    end) Mon,   --if the # of days between previous Monday and reg_date is zero, then get the according mark
   max(
     case
      when days_after_last_Monday = 1 then mark
      else null
     end) Tues,
   max(
     case
      when days_after_last_Monday = 2 then mark
      else null
     end) Wed,
   max(
     case
      when days_after_last_Monday = 3 then mark
      else null
     end) Thurs,
   max(
     case
      when days_after_last_Monday = 4 then mark
      else null
     end) Fri,
   max(
     case
      when days_after_last_Monday = 5 then mark
      else null
     end) Sat
from
(
  select 
    reg_date,
    last_Monday,
    julianday(reg_date) - julianday(last_Monday) as days_after_last_monday,   --determine the number of days between previous Monday and reg_date
    mark
  from
  (
    select
      reg_date,
      case 
        when cast (strftime('%w', reg_date) as integer) = 1 then date(reg_date, 'weekday 1')
        else date(reg_date, 'weekday 1', '-7 days') 
      end last_monday,    --determine the date of previous Monday
      mark
    from
    (
      select 
        reg_date,
        group_concat(mark, '') mark  --concatenate am and pm marks for each reg_date
      from
      (
        SELECT
          reg_date,
          ampm,
          mark
        FROM register
        order by reg_date, ampm  --order by ampm so that am rows are selected before pm
      )
      group by reg_date
    )
  )
)
group by last_Monday
order by last_Monday;

SQL Fiddle demo