SQL質疑応答スレ 18問目

1NAME IS NULL2018/01/04(木) 22:12:22.14ID:???
このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。

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

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

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

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

2NAME IS NULL2018/01/04(木) 22:13:25.04ID:???

3NAME IS NULL2018/01/04(木) 22:14:28.52ID:???
よくある質問1

(問)
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に対して最新の1件だけ抽出するSQLの書き方を教えてください。

(答)
select A.ID,
    A.DATE,
    A.DATA
from TableName A
   inner join
   (select ID, max(DATE) as MAX_DATE
    from TableName
    group by ID
   ) B
   on A.ID = B.ID
   and A.DATE = B.MAX_DATE
;

4NAME IS NULL2018/01/04(木) 22:15:39.02ID:???
よくある質問2

(問)
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というデータがあっても無視して構いません。

(答)
select 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
;

5NAME IS NULL2018/01/04(木) 22:16:40.08ID:???
よくある質問3

(問)
ID HOGE
01 A
01 B
01 C
02 A
03 B

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

(答1)
select id
FROM TableName
WHERE hoge in ('A','B','C')
GROUP BY id
HAVING count(DISTINCT hoge) = 3
;

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

6NAME IS NULL2018/01/04(木) 22:17:39.88ID:???
よくある質問4

(問)
列の数が可変な問合せはどう書きますか?

(答)
標準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

7NAME IS NULL2018/01/04(木) 22:48:11.80ID:???
ダメだ
ブロック条件が厳しすぎる
誰か残りのFAQを貼ってくれ

8NAME IS NULL2018/01/05(金) 02:46:10.18ID:???
よくある質問5

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

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

   20100601
   20100602
    ・
    ・
    ・
   20100630

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

9NAME IS NULL2018/01/05(金) 02:47:19.49ID:???
よくある質問5

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ならこれを適当に改変すれば動きますが
 どのみちお奨めしません。

10NAME IS NULL2018/01/05(金) 02:49:13.40ID:???
よくある質問5 (後半の訂正)

どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
(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ならこれを適当に改変すれば動きますが
 どのみちお奨めしません。

11NAME IS NULL2018/01/05(金) 02:50:38.56ID:???
いちおつ
generate_series()関数がダメみたいだな

12NAME IS NULL2018/01/05(金) 21:56:46.58ID:???
前スレ971の回答でも貼っとくか

select T5."T3_ID"  "ID",
    T5."T3_日付" "日付",
    T5."T3_値"  "値",
    T5."T4_日付" "求めたい日付"
from  (select 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
        (select 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
;

13NAME IS NULL2018/01/06(土) 14:50:55.67ID:???
なるほど
全然速そうだな

14NAME IS NULL2018/01/06(土) 15:46:57.99ID:Os9t48kn
なんでそんな変なのばっかりなの?
ソートが頭から離れないのか。

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

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

16NAME IS NULL2018/01/07(日) 10:01:46.51ID:yu9dE7vj
なんというレベルの低さ

17NAME IS NULL2018/01/07(日) 12:26:12.96ID:???
>全然速そうだな

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

18NAME IS NULL2018/01/07(日) 14:04:49.83ID:yu9dE7vj
>>15
>>3 を見たのか?

19NAME IS NULL2018/01/25(木) 18:41:52.80ID:heRfYWvO
mysqlでサブクエリを asに代入するとき、常に1カラムしかasできないため
サブクエリだらけになってしまうのですが、これをスマートに1サブクエリとかで取得できませんでしょうか?

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を盛り込もうとすると
サブクエリだらけになってしまう。

20NAME IS NULL2018/01/25(木) 19:27:52.27ID:???
これでそのSQLと同じ意味にはなる
何がしたいのかはよくわからんが

select sub.*
from (select max(num) as max_num,
       min(num) as min_num,
       avg(num) as avg_num
   from  sub_table
   ) sub
   cross join
   main_table
;

21192018/01/26(金) 09:39:10.20ID:IngnXOhQ
>>20
ありがとうございます!!

22NAME IS NULL2018/01/26(金) 12:43:24.38ID:pxozcjcA
自作自演?

23NAME IS NULL2018/01/26(金) 17:32:37.75ID:???
どうしてそう思ったの?

24NAME IS NULL2018/01/26(金) 20:18:17.38ID:???
感謝ぐらい好きにさせてやれよ歪んでるなあ

25NAME IS NULL2018/01/28(日) 15:06:04.05ID:Qiu5BIxt
明らかに情報不足なのに情報を補完して回答しているからな。

26NAME IS NULL2018/01/28(日) 17:13:34.73ID:27cZ5HqG
それはキミ、情報不足やない、キミの知識不足やろ。

27NAME IS NULL2018/01/28(日) 18:21:25.57ID:qkfYCnFE
>>26
メインテーブルとサブテーブルの結合条件がクロスジョインとは普通、思わない。

28NAME IS NULL2018/01/28(日) 18:34:16.20ID:???
>>27
> メインテーブルとサブテーブルの結合条件がクロスジョイン
え?

29NAME IS NULL2018/01/28(日) 18:55:59.94ID:???
>>19のSQLがクロスジョインだと思えないのは知識不足としか言いようがない

30NAME IS NULL2018/01/28(日) 19:07:37.13ID:???
>>20は何も補完」してないだろ。逆に>>19の意味不明な部分はそのまま字面通り解釈して回答してる。

31NAME IS NULL2018/01/28(日) 19:16:59.64ID:???
>>29
main_table と「sub_table」の cross join だと思ってるなら知識不足以前の話かとww

32NAME IS NULL2018/01/30(火) 17:05:33.92ID:???
やりすぎ防犯パトロール、特定人物を尾行監視 2009年3月19日19時7分配信 ツカサネット新聞
http://headlines.yahoo.co.jp/hl?a=20090319-00000026-tsuka-soci

この記事で問題になった通称やりすぎ防パトは、創価学会と警察署が引き起こしていたようです

掻い摘んで説明すると

・創価学会は、町内会や老人会、PTA、商店会等の住民組織に関し、学会員が役員になるよう積極的に働きかける運動を
 90年代末から開始し、結果、多くの住民組織で役員が学会員という状況が生まれた

・防犯パトロールの担い手は地域の住民と住民組織で、防犯活動に関する会議や協議会には、住民組織の代表に役員が出席する為
 防犯活動や防パトに、創価学会が間接的に影響力を行使可能となった

・防パトは住民が行う為、住民が不審者や要注意人物にでっち上げられるトラブルが起きていたが
 創価学会はその緩さに目をつけ、住民組織を握っている状況を利用し、嫌がらせ対象者を不審者や要注意人物にでっち上げ
 防パトに尾行や監視、付き纏いをさせるようになった

・防パトは地元警察署との緊密な連携により行われる為、創価学会は警察署幹部を懐柔して取り込んでしまい
 不審者にでっち上げた住民への嫌がらせに署幹部を経由して警察署を加担させるようになった

・主に当該警察署勤務と考えられる創価学会員警察官を動かし、恐らく非番の日に、職権自体ないにもかかわらず
 私服警官を偽装させて管轄内を歩いて回らせ、防犯協力をお願いしますと住民に協力を求めて回り
 防犯とは名ばかりの、単なる嫌がらせを住民らに行わせた(防犯協力と称し依頼して回っていた警察官らの正体は恐らく所轄勤務の学会員警察官)
 ※これに加えて防犯要員が同様のお願いをして回る

・こうして防犯パトロールを悪用し、住民を欺いて嫌がらせをさせつつ、創価学会自体も会員らを動員し、組織的な嫌がらせを連動して行った

つまり警察署に勤務する学会員警察官、警察署幹部、創価学会が通称やりすぎ防犯パトロールの黒幕

詳細は下記スレをご覧下さい
やりすぎ防犯パトロールは創価学会と警察署の仕業だった
https://rio2016.5ch.net/test/read.cgi/bouhan/1516500769/

33NAME IS NULL2018/02/04(日) 15:27:38.88ID:gIv0OydZ
質問です
以下のようなTableAとTableBをTableAのIDで結合して、

1. TableBに特定のKEY(ここではKEY=2)が含まれているならそのデータを、
2. 含まれていなければ若いKEYのデータを、
3. KEYそのものが存在しなければNULL

を返す結果を取得したいです
どのようなSQLなら実現しますか?


「TableA」
ID | NAME
--+-------
1 | aaa
2 | bbb
3 | ccc
4 | ddd

「TableB」
ID2 | ID | KEY | DATA
--+----+-----+-----
1 | 1   | 1    | a_key1
2 | 1   | 2    | a_key2
3 | 1   | 3    | a_key3
4 | 2   | 1    | b_key1
5 | 2   | 2    | b_key2
6 | 3   | 1    | c_key1
7 | 3   | 3    | c_key3

「結果」
ID | NAME    | ID2 | DATA
--+---------+----------
1 | aaa | 2    | a_key2
2 | bbb | 5    | b_key2
1 | ccc | 6    | c_key1
3 | ddd | NULL | NULL

34NAME IS NULL2018/02/04(日) 15:29:21.67ID:gIv0OydZ
訂正

「結果」
3 | ccc | 6    | c_key1
4 | ddd | NULL | NULL

35NAME IS NULL2018/02/04(日) 17:18:13.30ID:???
>>33
SQL-Server なら
select T.ID as ID, T.NAME as NAME, T.ID2 as ID2, T.DATA as DATA
from (
select
row_number() over (
partition by TableA.Id
order by
case TableB.[KEY]
when 2 then 0 else 1
end,
TableB.[KEY] asc
) as R,
TableA.ID as ID,
TableA.NAME as NAME,
TableB.ID2 as ID2,
TableB.DATA as DATA
from TableA left join TableB on TableA.ID = TableB.ID
) T
where T.R = 1

36NAME IS NULL2018/02/04(日) 17:38:30.70ID:gIv0OydZ
>>35
ありがとうございます
使用しているのはMariaDB 10.1でしたが(書き忘れ失礼しました)、頂いた解答をヒントに試してみます

37NAME IS NULL2018/02/04(日) 18:26:18.34ID:???
>>36
う〜ん、MariaDB使ったことないけど
https://mariadb.com/kb/en/library/row_number/
を見るとrow_number()は10.2.0以降でないとダメみたい
10.2.0には上げられないの?

38NAME IS NULL2018/02/04(日) 18:33:52.86ID:gIv0OydZ
>>37
こちらもちょうどそこに行き着いてました
バージョンを上げることは可能なので最新版にアップデートして試してみようと思います
わざわざ調べて頂き感謝します

39NAME IS NULL2018/02/14(水) 13:22:57.02ID:???
☆ 日本の、改憲をしましょう。現在、衆議員と参議院の両院で、
改憲議員が3分の2を超えております。『憲法改正国民投票法』、
でググってみてください。国会の発議はすでに可能です。
平和は勝ち取るものです。お願い致します。☆☆

40NAME IS NULL2018/02/15(木) 23:04:18.91ID:???
SQLがなかなか覚えられません
SELECT が先頭に来るのは覚えていますが、
次がFROMか、WHEREか迷ったりします
なにか覚えやすい方法ってありますか?

41NAME IS NULL2018/02/15(木) 23:30:33.91ID:???
暗記

42NAME IS NULL2018/02/16(金) 00:34:33.15ID:???
>>40
順番はきっちり決まってるから覚えれば迷うことはない
それすら覚えられないならマやめた方がいい

43NAME IS NULL2018/02/16(金) 07:59:00.96ID:???
>>41-42
ありがとうございます
覚えます

新着レスの表示
レスを投稿する