SQL質疑応答スレ 19問目 (89レス)
1-

1 2019/05/23(木) 20:25:40 ID:???
このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。

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

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

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

前スレ:
SQL質疑応答スレ 18問目
2chスレ:db
2 2019/05/23(木) 20:29:13 ID:???
SQL言語リファレンス一覧
Oracle Database
https://docs.oracle.com/cd/E96517_01/sqlrf/index.html
Microsoft SQL Server
https://docs.microsoft.com/ja-jp/sql/t-sql/language-reference
IBM DB2 Database
https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/c0004100.html

PostgreSQL
http://www.postgresql.jp/document/current/html/sql.html
MySQL
https://dev.mysql.com/doc/refman/8.0/en/sql-syntax.html
https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax.html


参考リンク
http://sql.main.jp/index.html
https://www.atmarkit.co.jp/ait/articles/0006/21/news001.html
https://oraclesqlpuzzle.ninja-web.net/
https://www.techscore.com/tech/sql/index.html/
3 2019/05/23(木) 20:31:43 ID:???
過去スレ
18問目:2chスレ:db
17問目:2chスレ:db
16問目:2chスレ:db
15問目:2chスレ:db
14問目:2chスレ:db
13問目:2chスレ:db
12問目:2chスレ:db
11問目:2chスレ:db
10問目:2chスレ:db
9問目:2chスレ:db
8問目:2chスレ:db
7問目:2chスレ:db
6問目:2chスレ:db
5問目:2chスレ:db
4問目:2chスレ:db
3問目:2chスレ:db
2問目:2chスレ:db
帰ってきた:2chスレ:db
Part 2:2chスレ:db
初代:2chスレ:db
4 2019/05/23(木) 20:34:29 ID:???
よくある質問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
;
5 2019/05/23(木) 20:36:39 ID:???
よくある質問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
;
6 2019/05/23(木) 20:40:07 ID:???
よくある質問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によって文法がかなり違うので注意
7 2019/05/23(木) 20:41:16 ID:???
よくある質問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
8 2019/05/23(木) 20:43:39 ID:???
よくある質問5

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

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

   20100601
   20100602
    ・
    ・
    ・
   20100630

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

どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
(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ならこれを適当に改変すれば動きますが
 どのみちお奨めしません。
9 2019/05/23(木) 20:44:50 ID:???
以上、テンプレ終わり
10 2019/05/25(土) 01:14:38 ID:1nV7ZQjK(1)
空文字列を入れたがるやつにかぎって、レコードによっては空文字列だったり、NULLだったりと両方想定してないといけない設計にして、運用ではまる。
11 2019/05/25(土) 01:39:12 ID:???
苦労しているんだね
同情します
12 2019/05/25(土) 06:44:36 ID:LBGwosS9(1)
SQLじゃなくてテーブル設計の質問なんですが、お願いします。

商品を複数の倉庫に保管するモデリングなんですが、現状は、
----------------------------------------------------------------
pkey | 商品ID | 商品名 | 倉庫1 | 倉庫2 | 倉庫3 | 倉庫4 | 倉庫5 |
----------------------------------------------------------------
という風になって、保管する場所が増えるたびに、倉庫カラムが増える設計になっています。
これは、
-------------------------
pkey | 商品ID | 倉庫番号 |
-------------------------
    |      |   倉庫1  |
-------------------------
    |      |   倉庫2  |
-------------------------
    |      |   倉庫3  |
-------------------------
    |      |   倉庫4  |
-------------------------
    |      |   倉庫5  |
-------------------------

という風にするべきなのでしょうか?
最初の設計だとカラムが増えていくのに対して、この方法だとレコードがとんでもない数になっていくのですが・・・・
13 2019/05/25(土) 07:25:57 ID:???
毎回思うけどレコード増えてなにが困るん?
14 2019/05/25(土) 07:54:17 ID:???
>>13
?もともと1レコードに収まっていたデータを多数のレコードに分割すると、読み取りのパフォーマンスは落ちないでしょうか?
?副作用ですが、正規化することで、正規化前と比べてテーブル数が増える(20倍程度)と、結合で速度が落ちないか?
という点が不安です。
15 2019/05/25(土) 08:54:05 ID:???
>>12
倉庫が増えたら項目増やさないといけなくなるのと
倉庫ごとに数量、単価、金額もちたくなるとさらに項目が増える

もとのデータは下のテーブル形式で、夜間処理で照会用の中間テーブルとして上のテーブルをつくればいい
16 2019/05/25(土) 10:08:56 ID:???
>>15
おっしゃる通りです。経験のなさから、そこまで考えが及びませんでした。
まずは第三正規形に正規化した状態でのパフォーマンス確認からしてみます。
17 2019/05/25(土) 11:34:00 ID:???
>>12
その商品、特定倉庫1ヶ所にしまうのかい?
18 2019/05/25(土) 11:45:54 ID:???
SQL質疑スレでやるよりも、こちらでした方が良いかも

DB設計を語るスレ 10
2chスレ:db

答える人は同じなんだろうけどね
19 2019/05/25(土) 13:46:20 ID:Z+RZPCej(1)
縦横問題はRDBができて以来定番の課題&質問になってるな
PIVOTとかも使いにくいしいい加減DBMS側でうまいことしてほしいところ
20 2019/05/26(日) 00:42:51 ID:???
今なら
「ラクテンスーパーポイントスクリーン」
登録するだけでRポイント150pが貰える!

※Androidアプリのみ
iPhoneユーザーはWeb版から登録のみ可能

登録完了後に表示される招待コ一ドをお持ちですか?のところで
「i9WPjs」
を入力する

完了

祭りだ♪ヽ('∀')メ('∀')メ('∀')ノワッショイ
21 2019/05/26(日) 03:38:10 ID:2BWK9yiY(1)
>>14
あなたは30年前からタイムマシンに乗ってやってきたのですか?
22 2019/05/29(水) 11:13:10 ID:???
>>21
30年前でもその設計はしないと思う
23 2019/05/31(金) 19:07:06 ID:???
たまには30年先から来た人に話を聞いてみたい
24 2019/05/31(金) 23:38:45 ID:5a1yvLIB(1)
>>23
過去には行けるが未来には行けない。これが現代の常識。
25 2019/05/31(金) 23:48:27 ID:???
それだとおかしくなる
26 2019/06/01(土) 00:31:01 ID:bltHuGZw(1)
>>25
逆にだった。時間の流れが遅いところにいると、時間の流れが速いところに戻ったときに未来にたどりつく。
27 2019/06/01(土) 01:34:20 ID:???
現在から過去に行けるなら、未来人が現在に来ることは可能だな

現代の常識だとそうなる
28 2019/06/02(日) 23:01:40 ID:???
ここSQLスレじゃなくなったん?
29 2019/06/03(月) 21:16:01 ID:???
SQLスレですね
30 2019/07/13(土) 19:26:52 ID:AJIqdE5u(1)
今更だが、SQLの正しい記述ってのはあるのか?
31 2019/07/13(土) 20:51:42 ID:???
今更だが、正しさとはなんだ?
32 2019/07/13(土) 20:54:06 ID:???
アメリカが正義だ
33 2019/07/13(土) 21:24:45 ID:???
標準SQLがBNF記法でまとめられてるサイト
https://ronsavage.github.io/SQL/
34 2019/07/13(土) 21:47:23 ID:???
>>30
SQL:2016とかの規格の話?
35 2019/07/14(日) 08:50:25 ID:KDHP+Bri(1)
>>34
はい。
36 2019/07/28(日) 18:04:41 ID:???
https://i.imgur.com/QvQTuqJ.jpg
37 2019/07/29(月) 21:32:16 ID:???
点数テーブル
名前 日付 点数
A  7/27 50
A  7/28 70
B  7/27 80
B  7/28 90

ゲタテーブル
A  7/27 10
A  7/28 -5
B  7/27 -20
B  7/28 5

というような2つのテーブルがあって
点数をプラスマイナスした結果を出力したいのですが、どういうやり方がありますか?
今UNION ALLして、名前と日付をGROYP BYして
点数をSUMで集計しているのですが、これで問題ないでしょうか?

本当は例よりもjoinjoinしていて複雑なので、文も大分長くなってますしこれでいいのか不安になってます
38 2019/07/29(月) 21:38:08 ID:???
普通にleftjoinして加算すれば
39 2019/07/29(月) 21:48:46 ID:???
ありがとうございます!
+で繋げたら普通に足し算できたんですね・・・お恥ずかしい
ゲタのほうに該当がなかった場合点数+NULLみたいにならないか不安ですが試してみます!
40 2019/07/30(火) 00:01:05 ID:???
COALESCEなりCASEなりでNULLを0にすればいいんでない?
41 2019/07/30(火) 06:42:33 ID:???
>>39
isnullつかえば
42 2019/07/31(水) 22:15:14 ID:???
37の意図する事が今一分からないだが、
単純に点数テーブルとゲタテーブルをマージして
Group by して sum取っちゃ拙いのか?

MySQLだとこんな具合で
select `名前`,`日付`,sum(`点数`) from (
select `名前`,`日付`,`点数` from `点数テーブル`
union
select `名前`,`日付`,`点数` from `ゲタテーブル`
) g
group by `名前`,`日付`;
43 2019/07/31(水) 22:32:34 ID:???
>>37がやってるってのがそれだろ。
しかも>>37は正しくunion all使ってるのにお前は間違えてるし。
44 2019/07/31(水) 22:35:57 ID:???
>>43
あ、すまんな、その通りだ
45 2019/08/01(木) 08:09:51 ID:???
まあunionでもいいと思うが万が一ゲタ側のレコードに対応するレコードが点数テーブルにない時もレコード出力されちゃうから俺ならleft joinでやると思う
46 2019/08/01(木) 20:10:22 ID:???
>>37です。皆さんありがとうございます。参考になります
その後、LEFT JOINで書いたのですが、
点T
A 7/30 70
B 7/30 80

ゲタT
A 7/30 +10
A 7/30 -5
B 7/30 -10
というように、ゲタの対応するレコードが1件だけではなかったらしく
普通にLEFT JOINすると
A 7/30 80
A 7/30 65
B 7/30 70
のように複数行になってしまいました

サブクエリでSUMするしかないかなと考えているのですが
以下の2つのやり方でどちらが良いでしょうか?
後者のほうがシンプルで良いかなと思ってます
もしくは、もっと他に良い方法があるでしょうか?

?
SELECT 人, 日, 点+ゲタ計
FROM 点T
LEFT JOIN (
  SELECT 人, 日, SUM(ゲタ)
  FROM ゲタT
  GROUP BY 人, 日
)
ON 点T.人 = ゲタT.人
AND 点T.日 = ゲタT.日

?
SELECT 人, 日, 点+ゲタの合計
FROM (
  SELECT 人, 日, 点, SUM(ゲタ)
  FROM 点T
  LEFT JOIN ゲタT
  ON 点T.人 = ゲタT.人
  AND 点T.日 = ゲタT.日
  GROUP BY 人, 日, 点
)

見づらく恐縮なのですが、よろしくお願いします
47 2019/08/01(木) 22:23:33 ID:???
2番目のgroup by はやばい.
正誤で言えば、1番目が正しい
48 2019/08/02(金) 06:39:22 ID:???
ゲタハカセスギw
49 2019/08/02(金) 09:43:54 ID:???
ありがとうございます!
確かに?はおかしかったですね・・・
?のほうで書いてみたいと思います
ありがとうございました!
50 2019/08/02(金) 22:05:00 ID:???
サブクエリする必要はないけど
?も別に間違いじゃないけどな
SELECT 人, 日, 点+SUM(ゲタ)
  FROM 点T
  LEFT JOIN ゲタT
  ON 点T.人 = ゲタT.人
  AND 点T.日 = ゲタT.日
  GROUP BY 人, 日, 点
51 sage 2019/08/03(土) 13:02:52 ID:wjgyF6r3(1)
>>47
なにがやばくてどう間違ってるのか詳しく

どう書いてもまあいいんだが、気になるなら実際のテーブル(インデックス)構成と件数で実行計画取ってみれ
1-
あと 38 レスあります
スレ情報 赤レス抽出 画像レス抽出 歴の未読スレ AAサムネイル

ぬこの手 ぬこTOP 0.283s*