データベースでカラムの合計値とその内訳も合わせて表示したい

Question

MariaDB ver15.1

MySQLデータベースのカラム名、nickname、date、hourに、HTMLフォームを使って勤務者、勤務日、勤務時間を入れていきます。

このデータベースを使って勤務者ごとの直近7日間のhourの合計時間の多い人から並び替えて、nicknameとhourの合計時間、7日間それぞれのhourの値を表示する表を作るにはどんなSQL文を作ればよいでしょうか？

 SELECT
  nickname,sum(hour)
 FROM study_hour
  where date between date(now() - INTERVAL 7 day) and date(now())
 GROUP BY
  nickname
 ORDER BY
  sum(hour) DESC;

こうすれば、合計勤務時間の多い人順でnicknameとhourの合計時間を、表示できるのですが、
勤務日とその日の勤務時間をどのように表示すればいいのかわかりません。

具体的に表示したい表を書くと以下の通りです。
nickname|1/1|1/2|1/3|1/4|1/5|1/6|1/7|sum(hour)
甲|8|8|8|8|8|8|0|56
乙|8|8|8|8|8|7|0|55
丙|8|8|8|8|8|6|0|54

1週間の日付表示は、もしかするとPHPで表示した方がいいのかもしれません・・・
よろしくお願いします。

Comments

[m.ts10806]

細かい指摘で申し訳ないですが、下記、不要です。初心者アイコンつけられてますし、冒頭でそこを断られると読む気なくす人もいます。それならそれだけ書く労力をわかりやすい内容を書くために割いてください。

>超初心者の、初質問です。
質問自体わかりにくくてすみません。

[退会済みユーザー]

以後、気をつけます。
ご指摘ありがとうございました。

[m.ts10806]

質問は編集できるこで適宜ご対応ください。(以後、と後回しにすると大抵次の時には忘れてます…)

[退会済みユーザー]

なんども、すみません。
質問、修正できるのですね！
修正します！
勉強になりますm(_ _)m

Answer 1

select 項目の名称まで可変にするには動的SQLでないと無理です。
タイトルが必要ならPHPで基準の日からの相対で生成するとか。

SELECT  nickname, date(now()) as base_date, sum(hour)
      , sum(case when date=date(now() - INTERVAL 6 day) then hour else 0 end) as date6
      , sum(case when date=date(now() - INTERVAL 5 day) then hour else 0 end) as date5
      , sum(case when date=date(now() - INTERVAL 4 day) then hour else 0 end) as date4
      , sum(case when date=date(now() - INTERVAL 3 day) then hour else 0 end) as date3
      , sum(case when date=date(now() - INTERVAL 2 day) then hour else 0 end) as date2
      , sum(case when date=date(now() - INTERVAL 1 day) then hour else 0 end) as date1
      , sum(case when date=date(now()) then hour else 0 end) as date0
FROM  study_hour
where date between date(now() - INTERVAL 6 day) and date(now())
GROUP BY  nickname, date(now())
ORDER BY  sum(hour) DESC

抽出条件が8日分だったので、1週間分に補正掛けてます。

Comments

[退会済みユーザー]

迅速なご回答ありがとうございます。

まだわからない点がありますので教えていただけると幸いです。

ご回答いただいたコードには、date6 date5・・・date1となっておりますが、
ブラウザ表示をしたとき、現在の日付を基準とした直近7日分を表示したいと考えております。

つまり今(2/18)、ブラウザ表示した場合、

nickname|2/12|2/13|2/14|2/15|2/16|2/17|2/18|sum(hour)
甲|8|8|8|8|8|8|0|48
乙|8|8|8|8|8|7|0|47
丙|8|8|8|8|8|6|0|46

明日（2/19）、ブラウザ表示する場合、
nickname|2/13|2/14|2/15|2/16|2/17|2/18|2/19|sum(hour)
甲|8|8|8|8|8|0|4|44
乙|8|8|8|8|7|0|4|43
丙|8|8|8|8|6|0|4|42

といった感じです。
言葉足らずですみませんでした。
(最初の質問でsum(hour)の値も間違っていました。かさねがさねすみません)

[sazi]

SQLの取得結果をどのようにブラウザに展開するのかは答えかねます。
この質問内容では正直ブラウザに表示する部分の回答は付きにくいと思いますよ。

[sazi]

SQLで希望の編集を行うのに必要な情報は取得できると思いますので、後はPHPなりで表示用の編集を行えば良いのだと思いますが。

Answer 2

任意のカラム名を自動的につけたいならprocedureが良いでしょう。

• 元データ

create table tbl (
id int primary key auto_increment
,nickname varchar(10)
,d date,work double
,unique key(nickname,d)
);
insert into tbl(nickname,d,work) values
('X','2019-02-12',8),
('Y','2019-02-12',8),
('Z','2019-02-12',8),
('X','2019-02-13',8),
('Y','2019-02-13',8),
('Z','2019-02-13',8),
('X','2019-02-14',8),
('Y','2019-02-14',8),
('Z','2019-02-14',8),
('X','2019-02-15',8),
('Y','2019-02-15',8),
('Z','2019-02-15',8),
('X','2019-02-16',8),
('Y','2019-02-16',8),
('Z','2019-02-16',8),
('X','2019-02-17',8),
('Y','2019-02-17',7),
('Z','2019-02-17',6),
('X','2019-02-19',4),
('Y','2019-02-19',4),
('Z','2019-02-19',4),
('X','2019-02-20',1),
('Y','2019-02-20',2),
('Z','2019-02-20',3);

• procedure作成

drop procedure if exists shukei;
delimiter //
create procedure shukei(in d date) 
begin
set @d:=d;
set @sql="";
set @sql=concat(@sql,"select nickname");
set @sql=concat(@sql,",sum((d=@d-interval 6 day)*work) as `",@d-interval 6 day,"`");
set @sql=concat(@sql,",sum((d=@d-interval 5 day)*work) as `",@d-interval 5 day,"`");
set @sql=concat(@sql,",sum((d=@d-interval 4 day)*work) as `",@d-interval 4 day,"`");
set @sql=concat(@sql,",sum((d=@d-interval 3 day)*work) as `",@d-interval 3 day,"`");
set @sql=concat(@sql,",sum((d=@d-interval 2 day)*work) as `",@d-interval 2 day,"`");
set @sql=concat(@sql,",sum((d=@d-interval 1 day)*work) as `",@d-interval 1 day,"`");
set @sql=concat(@sql,",sum((d=@d)*work) as `",@d,"`");
set @sql=concat(@sql,",sum(( d between @d - interval 6 day and @d)*work) as sum_week ");
set @sql=concat(@sql,"from tbl ");
set @sql=concat(@sql,"where d between @d - interval 6 day and @d ");
set @sql=concat(@sql,"group by nickname ");
set @sql=concat(@sql,"order by sum_week desc ");
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

• 抽出

call shukei('2019-02-18');

※引数に任意の日付を指定すれば自動で１週間分のデータが取れる
※例外処理など一切無視しているので適宜調整が必要

Comments

[退会済みユーザー]

ありがとうございます！
ずっと、悩んでいたことが解決できました！
回答を参考にしながらやってみたらうまく表示できました！