SQL 問題 その6 --- A の解答

  MDB



  
select
 週,
 sum(日) as 日,
 sum(月) as 月,
 sum(火) as 火,
 sum(水) as 水,
 sum(木) as 木,
 sum(金) as 金,
 sum(土) as 土
from
(
select
 DatePart("ww",取引日付) as 週,
 iif(Weekday(取引日付) = 1,金額,0) as 日,
 iif(Weekday(取引日付) = 2,金額,0) as 月,
 iif(Weekday(取引日付) = 3,金額,0) as 火,
 iif(Weekday(取引日付) = 4,金額,0) as 水,
 iif(Weekday(取引日付) = 5,金額,0) as 木,
 iif(Weekday(取引日付) = 6,金額,0) as 金,
 iif(Weekday(取引日付) = 7,金額,0) as 土
 from V_得意先台帳
) as INVIEW
group by 
 週
  



  SQLServer



  
select
 週,
 sum(日) as 日,
 sum(月) as 月,
 sum(火) as 火,
 sum(水) as 水,
 sum(木) as 木,
 sum(金) as 金,
 sum(土) as 土
from
(
select
 DATEPART(ww,取引日付) as 週,
 case DATEPART(w,取引日付) when 1 then 金額 end as 日,
 case DATEPART(w,取引日付) when 2 then 金額 end as 月,
 case DATEPART(w,取引日付) when 3 then 金額 end as 火,
 case DATEPART(w,取引日付) when 4 then 金額 end as 水,
 case DATEPART(w,取引日付) when 5 then 金額 end as 木,
 case DATEPART(w,取引日付) when 6 then 金額 end as 金,
 case DATEPART(w,取引日付) when 7 then 金額 end as 土
 from V_得意先台帳
) as INVIEW
group by 週
  



  Oracle

  
select
 週,
 sum(日) as 日,
 sum(月) as 月,
 sum(火) as 火,
 sum(水) as 水,
 sum(木) as 木,
 sum(金) as 金,
 sum(土) as 土
from
(
select
 TO_CHAR(取引日付,'WW') as 週,
 case when TO_CHAR(取引日付,'D') = 1 then 金額 end as 日,
 case when TO_CHAR(取引日付,'D') = 2 then 金額 end as 月,
 case when TO_CHAR(取引日付,'D') = 3 then 金額 end as 火,
 case when TO_CHAR(取引日付,'D') = 4 then 金額 end as 水,
 case when TO_CHAR(取引日付,'D') = 5 then 金額 end as 木,
 case when TO_CHAR(取引日付,'D') = 6 then 金額 end as 金,
 case when TO_CHAR(取引日付,'D') = 7 then 金額 end as 土
 from V_得意先台帳
) INVIEW
group by 週
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加





フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ