SQL質疑応答スレ 18問目

**NAME IS NULL** · 2018/01/04(木) 22:12:22.14

このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。

SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。

質問するときはDBMS名を必ず付記してください。

【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明

前スレ：
SQL質疑応答スレ 17問目
https://mevius.5ch.net/test/read.cgi/db/1468157341/

**NAME IS NULL** · 2018/01/04(木) 22:13:25.04

**NAME IS NULL** · 2018/01/04(木) 22:14:28.52

よくある質問１

（問）
ID | DATE　　　　 | DATA
--+----------+-----
1 | 2007-11-11 | aaa
2 | 2007-11-11 | bbb
1 | 2007-11-10 | ccc
3 | 2007-11-12 | ddd
3 | 2007-11-11 | eee
4 | 2007-11-10 | fff
1 | 2007-11-12 | ggg

このようなテーブルから、下記のように

1 | 2007-11-12 | ggg
3 | 2007-11-12 | ddd
2 | 2007-11-11 | bbb
4 | 2007-11-10 | fff

各idに対して最新の１件だけ抽出するSQLの書き方を教えてください。

（答）
ｓｅｌｅｃｔ A.ID,
　　　 A.DATE,
　　　 A.DATA
from TableName A
　　 inner join
　　 (ｓｅｌｅｃｔ ID, max(DATE) as MAX_DATE
　　　　from TableName
　　　　group by ID
　　 ) B
　　 on A.ID = B.ID
　　 and A.DATE = B.MAX_DATE
;

**NAME IS NULL** · 2018/01/04(木) 22:15:39.02

よくある質問２

（問）
key　　　data
----------------
1　　　　　a
1　　　　　a
1　　　　　b
1　　　　　b
1　　　　　a
2　　　　　b
2　　　　　a
2　　　　　a

というテーブルから

key　　　a　　　b
--------------------
1　　　　3　　　2
2　　　　2　　　1

というExcelのピボットの様なデータを取得したいのですが、どういうSQLになりますか？
a,bというのは固定なので、仮にcというデータがあっても無視して構いません。

（答）
ｓｅｌｅｃｔ key,
　　　 SUM(CASE data WHEN 'a' THEN 1 END) AS a,
　　　 SUM(CASE data WHEN 'b' THEN 1 END) AS b
FROM table
GROUP BY key
ORDER BY key
;

**NAME IS NULL** · 2018/01/04(木) 22:16:40.08

よくある質問３

（問）
ID HOGE
01 A
01 B
01 C
02 A
03 B

HOGEをAもBもCも持っている、ID：01だけ取り出すにはどうすればよかですか

（答１）
ｓｅｌｅｃｔ id
FROM TableName
WHERE hoge in ('A','B','C')
GROUP BY id
HAVING count(DISTINCT hoge) = 3
;

（答２）
ｓｅｌｅｃｔ *
from TableName T1
where not exists (ｓｅｌｅｃｔ *
　　　　　　　　　from (values 'A', 'B', 'C') T2 (HOGE)
　　　　　　　　　where not exists (ｓｅｌｅｃｔ *
　　　　　　　　　　　　　　　　　　from TableName T3
　　　　　　　　　　　　　　　　　　where T1.ID = T3.ID
　　　　　　　　　　　　　　　　　　and T2.HOGE = T3.HOGE
　　　　　　　　　　　　　　　　　 )
　　　　　　　　 )
;
※valuesの部分(Table Value Constructor)はDBMSによって文法がかなり違うので注意

**NAME IS NULL** · 2018/01/04(木) 22:17:39.88

よくある質問４

（問）
列の数が可変な問合せはどう書きますか？

（答）
標準SQLでは書けません。
pivotという機能を搭載したDBMSなら一見書けそうですが実はやっぱり書けません。
Oracle 11g以降でpivot xmlというキーワードを使用すれば一応可変っぽくはなります。
が、素直にプロシージャを書くかアプリケーションで処理したほうが良いでしょう。

SQL Serverのpivot（2005以降）
http://msdn.microsoft.com/ja-jp/library/ms177410.aspx

Oracleのpivot（11g以降）
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/statements_10002.htm#CHDCEJJE
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

**NAME IS NULL** · 2018/01/04(木) 22:48:11.80

ダメだ
ブロック条件が厳しすぎる
誰か残りのFAQを貼ってくれ

**NAME IS NULL** · 2018/01/05(金) 02:46:10.18

よくある質問５

（問）
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい

　例：201006を指定したら、以下の結果を得たい

　　　20100601
　　　20100602
　　　　・
　　　　・
　　　　・
　　　20100630

（答）
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。

**NAME IS NULL** · 2018/01/05(金) 02:47:19.49

よくある質問５

with TEMP (NUM) as (
　　　　select 1 from dual
　　　　union all
　　　　select NUM + 1 from TEMP where NUM < 31
)
select to_char(to_date('201006', 'YYYYMM') + NUM - 1, 'YYYYMMDD')
from TEMP
where to_date('201006', 'YYYYMM') + NUM - 1 < add_months(to_date('201006', 'YYYYMM'), 1)
;

※上記はOracleの場合です。（11gR2以降）
※再帰問合せをサポートするDBMSならこれを適当に改変すれば動きますが
　どのみちお奨めしません。

**NAME IS NULL** · 2018/01/05(金) 02:49:13.40

よくある質問５ (後半の訂正)

どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
（PostgreSQLの連番を生成する関数なら辛うじてセーフかもしれませんが
　賛否の分かれるところでしょう。）

with TEMP (NUM) as (
　　　　select 1 from dual
　　　　union all
　　　　select NUM + 1 from TEMP where NUM < 31
)
select to_char(to_date('201006', 'YYYYMM') + NUM - 1, 'YYYYMMDD')
from TEMP
where to_date('201006', 'YYYYMM') + NUM - 1 < add_months(to_date('201006', 'YYYYMM'), 1)
;

※上記はOracleの場合です。（11gR2以降）
※再帰問合せをサポートするDBMSならこれを適当に改変すれば動きますが
　どのみちお奨めしません。

**NAME IS NULL** · 2018/01/05(金) 02:50:38.56

いちおつ
generate_series（）関数がダメみたいだな

**NAME IS NULL** · 2018/01/05(金) 21:56:46.58

前スレ971の回答でも貼っとくか

ｓｅｌｅｃｔ　T5."T3_ID"　 "ID",
　　　　T5."T3_日付" "日付",
　　　　T5."T3_値"　 "値",
　　　　T5."T4_日付" "求めたい日付"
from　　(ｓｅｌｅｃｔ T3."ID"　　　"T3_ID",
　　　　　　　　T3."日付"　　"T3_日付",
　　　　　　　　T3."値"　　　"T3_値",
　　　　　　　　T4."T2_日付" "T4_日付",
　　　　　　　　row_number() over (partition by T3."ID", T3."日付" order by T4."T1_日付" desc) "日付順"
　　　　from　　"TableName" T3
　　　　　　　　left outer join
　　　　　　　　(ｓｅｌｅｃｔ T1."ID"　 "T1_ID",
　　　　　　　　　　　　T1."日付" "T1_日付",
　　　　　　　　　　　　T2."日付" "T2_日付",
　　　　　　　　　　　　row_number() over (partition by T1."ID", T1."日付" order by T2."値" desc) "値順"
　　　　　　　　from　　"TableName" T1
　　　　　　　　　　　　inner join
　　　　　　　　　　　　"TableName" T2
　　　　　　　　　　　　on T1."ID" = T2."ID"
　　　　　　　　　　　　and T2."日付" between add_months(T1."日付", -12) and T1."日付"
　　　　　　　　) T4
　　　　　　　　on　T3."ID" = T4."T1_ID"
　　　　　　　　and T3."日付" > T4."T1_日付"
　　　　where T4."値順" = 1
　　　　or　　T4."値順" is null
　　　　) T5
where T5."日付順" = 1
order by 1,2,3
;

**NAME IS NULL** · 2018/01/06(土) 14:50:55.67

なるほど
全然速そうだな

**NAME IS NULL** · 2018/01/06(土) 15:46:57.99

なんでそんな変なのばっかりなの？
ソートが頭から離れないのか。

**NAME IS NULL** · 2018/01/07(日) 01:11:19.54

>>13
パーティション切ってrow_numberで先頭行だから
オプティマイザの出来次第だけどパフォーマンスに大差はないと思うけど
相関サブクエリとjoinなら同じ実行計画はいたりするし

>>14
はいはい
どうせ正解は書かないんじゃなくて書けないんでしょ

**NAME IS NULL** · 2018/01/07(日) 10:01:46.51

なんというレベルの低さ

**NAME IS NULL** · 2018/01/07(日) 12:26:12.96

>全然速そうだな

変な日本語を何とかしろよ

**NAME IS NULL** · 2018/01/07(日) 14:04:49.83

>>15
>>3 を見たのか？

**NAME IS NULL** · 2018/01/25(木) 18:41:52.80

mysqlでサブクエリを asに代入するとき、常に1カラムしかasできないため
サブクエリだらけになってしまうのですが、これをスマートに１サブクエリとかで取得できませんでしょうか？

SELECT
(
　SELECT
　　max(num)
　FROM
　　sub_table
) as max_num ,
(
　SELECT
　　min(num)
　FROM
　　sub_table
) as min_num,
(
　SELECT
　avg(num)
　FROM
　　sub_table
) as avg_num
FROM
main_table
↑main_tableに　sub_tableの　min max avgを盛り込もうとすると
サブクエリだらけになってしまう。