SQL質疑応答スレ 18問目
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 17問目
https://mevius.2ch.sc/test/read.cgi/db/1468157341/
17問目：https://mevius.2ch.sc/test/read.cgi/db/1468157341/
16問目：http://echo.2ch.sc/test/read.cgi/db/1447160858/
15問目：http://peace.2ch.sc/test/read.cgi/db/1402919549/
14問目：http://peace.2ch.sc/test/read.cgi/db/1371476534/
13問目：http://toro.2ch.sc/test/read.cgi/db/1343899481/
12問目：http://toro.2ch.sc/test/read.cgi/db/1316769778/
11問目：http://hibari.2ch.sc/test/read.cgi/db/1299305530/
10問目：http://hibari.2ch.sc/test/read.cgi/db/1274791771/
9問目：http://pc11.2ch.sc/test/read.cgi/db/1252492296/
8問目：http://pc11.2ch.sc/test/read.cgi/db/1236253554/
7問目：http://pc11.2ch.sc/test/read.cgi/db/1223525474/
6問目：http://pc11.2ch.sc/test/read.cgi/db/1210940477/
5問目：http://pc11.2ch.sc/test/read.cgi/db/1193486961/
4問目：http://pc11.2ch.sc/test/read.cgi/db/1176553195/
3問目：http://pc11.2ch.sc/test/read.cgi/db/1160458216/
2問目：http://pc8.2ch.sc/test/read.cgi/db/1141622643/
帰ってきた：http://pc8.2ch.sc/test/read.cgi/db/1124178925/
Part 2：http://pc8.2ch.sc/test/read.cgi/db/1103113155/
初代：http://pc8.2ch.sc/test/read.cgi/db/1056973582/
（問）
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
;
（問）
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
;
（問）
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によって文法がかなり違うので注意
（問）
列の数が可変な問合せはどう書きますか？
（答）
標準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
ブロック条件が厳しすぎる
誰か残りのFAQを貼ってくれ
（問）
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい
例：201006を指定したら、以下の結果を得たい
20100601
20100602
・
・
・
20100630
（答）
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。
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ならこれを適当に改変すれば動きますが
どのみちお奨めしません。
どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
（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ならこれを適当に改変すれば動きますが
どのみちお奨めしません。
generate_series（）関数がダメみたいだな
ｓｅｌｅｃｔ 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
;
全然速そうだな
ソートが頭から離れないのか。
パーティション切ってrow_numberで先頭行だから
オプティマイザの出来次第だけどパフォーマンスに大差はないと思うけど
相関サブクエリとjoinなら同じ実行計画はいたりするし
>>14
はいはい
どうせ正解は書かないんじゃなくて書けないんでしょ
変な日本語を何とかしろよ
>>3 を見たのか？
サブクエリだらけになってしまうのですが、これをスマートに１サブクエリとかで取得できませんでしょうか？
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を盛り込もうとすると
サブクエリだらけになってしまう。
何がしたいのかはよくわからんが
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
;
ありがとうございます！！
メインテーブルとサブテーブルの結合条件がクロスジョインとは普通、思わない。
> メインテーブルとサブテーブルの結合条件がクロスジョイン
え？
main_table と「sub_table」の cross join だと思ってるなら知識不足以前の話かとww
http://headlines.yahoo.co.jp/hl?a=20090319-00000026-tsuka-soci
この記事で問題になった通称やりすぎ防パトは、創価学会と警察署が引き起こしていたようです
掻い摘んで説明すると
・創価学会は、町内会や老人会、ＰＴＡ、商店会等の住民組織に関し、学会員が役員になるよう積極的に働きかける運動を
９０年代末から開始し、結果、多くの住民組織で役員が学会員という状況が生まれた
・防犯パトロールの担い手は地域の住民と住民組織で、防犯活動に関する会議や協議会には、住民組織の代表に役員が出席する為
防犯活動や防パトに、創価学会が間接的に影響力を行使可能となった
・防パトは住民が行う為、住民が不審者や要注意人物にでっち上げられるトラブルが起きていたが
創価学会はその緩さに目をつけ、住民組織を握っている状況を利用し、嫌がらせ対象者を不審者や要注意人物にでっち上げ
防パトに尾行や監視、付き纏いをさせるようになった
・防パトは地元警察署との緊密な連携により行われる為、創価学会は警察署幹部を懐柔して取り込んでしまい
不審者にでっち上げた住民への嫌がらせに署幹部を経由して警察署を加担させるようになった
・主に当該警察署勤務と考えられる創価学会員警察官を動かし、恐らく非番の日に、職権自体ないにもかかわらず
私服警官を偽装させて管轄内を歩いて回らせ、防犯協力をお願いしますと住民に協力を求めて回り
防犯とは名ばかりの、単なる嫌がらせを住民らに行わせた（防犯協力と称し依頼して回っていた警察官らの正体は恐らく所轄勤務の学会員警察官）
※これに加えて防犯要員が同様のお願いをして回る
・こうして防犯パトロールを悪用し、住民を欺いて嫌がらせをさせつつ、創価学会自体も会員らを動員し、組織的な嫌がらせを連動して行った
つまり警察署に勤務する学会員警察官、警察署幹部、創価学会が通称やりすぎ防犯パトロールの黒幕
詳細は下記スレをご覧下さい
やりすぎ防犯パトロールは創価学会と警察署の仕業だった
https://rio2016.2ch.sc/test/read.cgi/bouhan/1516500769/
以下のような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
「結果」
3 | ccc | 6 | c_key1
4 | ddd | NULL | NULL
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
ありがとうございます
使用しているのはMariaDB 10.1でしたが（書き忘れ失礼しました）、頂いた解答をヒントに試してみます
う〜ん、MariaDB使ったことないけど
https://mariadb.com/kb/en/library/row_number/
を見るとrow_number()は10.2.0以降でないとダメみたい
10.2.0には上げられないの？
こちらもちょうどそこに行き着いてました
バージョンを上げることは可能なので最新版にアップデートして試してみようと思います
わざわざ調べて頂き感謝します
改憲議員が３分の２を超えております。『憲法改正国民投票法』、
でググってみてください。国会の発議はすでに可能です。
平和は勝ち取るものです。お願い致します。☆☆
SELECT が先頭に来るのは覚えていますが、
次がFROMか、WHEREか迷ったりします
なにか覚えやすい方法ってありますか？
順番はきっちり決まってるから覚えれば迷うことはない
それすら覚えられないならマやめた方がいい
ありがとうございます
覚えます
CASE式で
SELECT c1,
CASE (SELECT count( * ) FROM t1 AS a WHERE a.c1 = b.c1 AND c2 IS NULL)
WHEN 0 THEN sum(c2)
ELSE 0 END
FROM t1 AS b
GROUP BY c1
設計見なおしたほうがいいパターンだと思う
SELECT c1, CASE sum(c3) WHEN 0 THEN sum(c2) ELSE 0 END
FROM (SELECT c1, c2, CASE WHEN c2 IS NULL THEN 1 ELSE 0 END AS c3 FROM t1) AS t2
GROUP BY c1
ORDER BY c1;
ありがとうございます！
後者の方が問い合わせが少ない感じで良さそうですね。
その可読性が高いやつ提示しなよ
union all
セレクト max(カラム名) as カラム別名 from テーブル名 group by グループ列名 having 列名 is not null
珍しくSQL書いたと思ったらやっぱり意味不明なSQLじゃんか
havingの意味分かってないんだろうな
おい勘違いするな
いつも意味不明なイチャモンつけてるアゲは俺だ
アゲだからといってバカと一緒にされては困るなw
>>45のやつはCASE WHEN EXISTSにしたほうがよかったね
SQLの知識があんまりなくていつも効率悪い調査の仕方だったりベンダーさんに投げてしまったりになってしまうんで質問させてください
使用しているのはoracle SQLです。
下みたいなAテーブル、Bマスタがあって
Aテーブル(トラン)
no 名前 上長名前 日付
Bマスタ
名前 社員番号 適用開始日 適用終了日
【抜き出したい情報】
・Aテーブルに1つもない(名前、上長名前)人員の情報(Bマスタの名前、社員番号)
・Aテーブルの名前、上長名前に1以上トランが発生している人員情報、発生件数(名前、上長名前で分ける)
を抽出したい場合はどうすればよいでしょうか？
実業務ではベンダーさんからの報告で解決したのですが次からは自分でも頑張ってみたくて勉強したのですが実際に書こうと思うと全く上手くいきませんOTL
とりあえず自分の思うように書いてみ
すみません構文もめちゃくちゃだし途中まで書いてわからなくなっちゃったのですが
上から一つ目
select B.名前,B,社員番号 from Aテーブル A inner join Bマスタ B
on A.名前 = B.名前 or A.上長名前 = B.名前
where
A.日付 between B.適用開始日 and B.適用終了日
ってとこまで書いて挫折してしまいました・・・・・
COUNT、EXISTSを使えばよさそうだなとは思うのですがどうでしょう？
ちなみに2つ目のはまったく見当もつかない状況です・・・・
元は営業職で今期から情報部に異動してきて勉強はしてるのですがまったくわかりません・・・
本職SEの方の文の書き方の考え方のコツなんかも教えていただけるとうれしいです
having に集計関数と書かずにカラム名と書いたら、見事にそこだけ問題視するんだな。
セレクトと書いているのもSQLインジェクション対策に引っかかるため。
union all
セレクト max(グループ名) as カラム別名 from テーブル名 group by グループ列名 having max(グループ名) is not null
そもそもSQLをずばり教えてくれというのが間違い。
select句にselect文を書くのを推奨するな。
あなたが書いているとおりに書けばよい。
なぜ一つのSQLでいきなりやろうとするのか？
3パターンの整理をせよ。
それあなたのいう1つ目もできていない。
少なくともそのSQLにこだわりがあるのなら、内部結合ではなく外部結合しなければ、1つ目も達成できていない。
ここで動かしてみてくれよ
そういうクエリでも動くDBMSがあるんならぜひ知りたい
http://sqlfiddle.com/#!17/05796a
なんで？
1. SQLの研修を受ける
2. 最小限のテストデータを作成して試行錯誤を繰り返す
3. 少し高度なクエリをWebや書籍で学ぶ
まず1と2をやること(>>58のやつはSQLの研修を受講すれば解決できるレベル)
基本的なクエリは問題なく出来るようになってから3に進む
3はミックのブログやセルコの本がおすすめ
SQLの書き方よりもDB設計を学んだほうが役立つ度合いが大きいよ
http://sqlfiddle.com/#!9/81ab8/1
2つめはfilter句が使えるDBもあるけど普通はCASE式かな
SQLの受講は予定してるけど・・・
ここってそういうの教えてくれる板じゃないの？
勉強法なんて聞いてないんだが
営業をお払い箱になって情シスでもお荷物だな
とか言ってるやつがこのスレに投げるだけになるんだぜ
鬱陶しいだろ
内部結合や外部結合は算数で言えば四則演算レベルの基礎なんだからさ
そのややこしい承認欲求どうにかしろやw
やっぱりSEって気持ち悪いな
ベンダー嬲り倒すことにしたわ
てか早く答えろやwwwPGしかできないアホなんだからこれくらいすぐ答えだせwwwwww
どこが出てるんだよwwwwww
やっぱSEって頭おかしいのしかいないのなwwwwww
回答してくれた気持ち悪いエス・イーさんの親切心台無しだなww
これでもいける
select c1,
case when count(*) = count(c2)
then max(c2)
else 0
end
from t1
group by c1
order by c1
そんな答え出てるものより60でも答えたれや
それではその答えを31文字以内で示しなさい。
どこにでてんだよwwwwww
あほかよSEさんwwwwww
インターネッツの使い方も知らないんやなｗｗｗﾌﾟﾌﾟｯ
それなら簡単じゃね
バカはお口にチャックで
どの辺が簡単じゃないの？
そりゃ営業上がりに馬鹿にされるわけだわ
簡単なSQLだからこそ
俺居座るからなはやくだせや
他のやつ質問すんなよこれが最優先だから
100〜のスレッドの続きを読む