「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 11問目
http://hibari.2ch.net/test/read.cgi/db/1299305530/
Oracle Database
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/toc.htm
Microsoft SQL Server
http://msdn.microsoft.com/ja-jp/library/bb510741%28v=SQL.105%29.aspx
IBM DB2 Database
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0004100.html
PostgreSQL
http://www.postgresql.jp/document/current/html/sql.html
MySQL
http://dev.mysql.com/doc/refman/5.1/ja/sql-syntax.html
参考リンク
http://sql.main.jp/cont/sql/map.html
http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html
http://www.geocities.jp/oraclesqlpuzzle/
http://www.techscore.com/tech/sql/
11問目：http://hibari.2ch.net/test/read.cgi/db/1299305530/
10問目：http://hibari.2ch.net/test/read.cgi/db/1274791771/
9問目：http://pc11.2ch.net/test/read.cgi/db/1252492296/
8問目：http://pc11.2ch.net/test/read.cgi/db/1236253554/
7問目：http://pc11.2ch.net/test/read.cgi/db/1223525474/
6問目：http://pc11.2ch.net/test/read.cgi/db/1210940477/
5問目：http://pc11.2ch.net/test/read.cgi/db/1193486961/
4問目：http://pc11.2ch.net/test/read.cgi/db/1176553195/
3問目：http://pc11.2ch.net/test/read.cgi/db/1160458216/
2問目：http://pc8.2ch.net/test/read.cgi/db/1141622643/
帰ってきた：http://pc8.2ch.net/test/read.cgi/db/1124178925/
Part 2：http://pc8.2ch.net/test/read.cgi/db/1103113155/
初代：http://pc8.2ch.net/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の書き方を教えてください。
（答）
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
;
（問）
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
;
（問）
ID HOGE
01 A
01 B
01 C
02 A
03 B
HOGEをAもBもCも持っている、ID：01だけ取り出すにはどうすればよかですか
（答１）
SELECT id
FROM TableName
WHERE hoge in ('A','B','C')
GROUP BY id
HAVING count(DISTINCT hoge) = 3
;
（答２）
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によって文法がかなり違うので注意
（問）
列の数が可変な問合せはどう書きますか？
（答）
標準SQLでは書けません。
pivotという機能を搭載したDBMSなら一見書けそうですが実はやっぱり書けません。
Oracle 11g以降でpivot xmlというキーワードを使用すれば一応可変っぽくはなります。
が、素直にプロシージャを書くかアプリケーションで処理したほうが良いでしょう。
SQL Serverのpivot（2005以降）
http://msdn.microsoft.com/ja-jp/library/ms177410.aspx
http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.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
（問）
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい
例：201006を指定したら、以下の結果を得たい
20100601
20100602
・
・
・
20100630
（答）
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。
どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
（PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが
賛否の分かれるところでしょう。）
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ならこれを適当に改変すれば動きますが
どのみちお奨めしません。
MySQL 5.0
・テーブルデータ
userテーブル
id int
name varchar
・欲しい結果＆説明
nameはユニークではないので、値が同じレコードが複数存在します。
nameの値が自分と同じ値を持つレコードが存在するレコードだけ全件抽出したいです
例）
id name
1 aaa
2 bbb
3 ccc
4 bbb
この場合idが2と4のレコードだけ抽出したいです。
どのようなSQLを書けばいいでしょうか。
よろしくお願いします。
select * from user where id in (select id from user group by name having count(*) > 1)
こうかな
の間違いだろ
existsでもいい
select *
from user T1
where exists (select *
from user T2
where T1.id <> T2.id
and T1.name = T2.name)
;
MySQLって無茶苦茶なＳＱＬ通すから、間違いじゃないのかもしれん
が、それを堂々と回答されてもなぁ
>>32じゃなくて>>31
>>33じゃなくて>>32
mysqlver5.1.6で
update tbTest set flg=1 where seq_host = 524 AND non_printable=0 ORDER BY seq LIMIT 0 ,10
というのを実行しようとしたらmysqlではupdate文のoffsetができない？らしくエラーになりました。
これを実行(seqが特定のものから10個をフラグ建てたい)
するにはどうしたらいいでしょうか？
ためしてないけど
update tbTest set flg=1 where seq_host = 524 AND non_printable=0 AND seq in(
select seq from tbTest t where
(select COUNT(*) from tbTest where seq<t.seq)<10
)
updateのテーブルに別名付けれたり、updateにfromかけたりするともうちょっとすっきりするかもしれんが
それは多分標準的なＳＱＬじゃないだろうし
売り上げ
ID | AMOUNT |
--+----------------------
1,200
1,300
1,400
2,100
4,100
5,200
5,300
6,400
得意先マスタ
ID | NAME | OYA_CODE
--+----------------------+--------------
1,abc商事 本店,1
2,abc商事 い支店,1
3,abc商事 う支店,1
4,def工務店 本店,4
5,def工務店 い支店,4
6,ghiサービス,6
7,jklシステム,7
・欲しい結果
1,abc商事 本店,900
4,def工務店 本店,600
6,ghiサービス,6
7,jklシステム,400
・説明
会社ごとに、売り上げ合計を出したいです。
得意先マスタに親会社コードを持っているので、親会社がある場合は親会社に合算したいです。
自分が親会社の場合は自らのコードが入っています。
6,ghiサービス,400
自らが子会社の場合は親会社に合算するので、子会社としては表示しません。
お願いします。
ID=1,2,3がabc商事なら、結果の１行目は200+300+400+100で1000じゃないのか？
売上テーブルに7,400の行が無いのは抜けてるだけか？
子会社の子会社はあり得るのか？
抜けていたのと、計算違いすみません。
子会社の子会社(孫)はありません。
よろしくお願いします。
select
ID,
NAME,
(select sum(AMOUNT) from 売り上げ where ID in (select ID from 得意先マスタwhere OYA_CODE=t.ID) ) as 合計
from 得意先マスタ t
where ID=OYA_CODE
とかでどうだ
教えていただいたSQLでほしい情報を取得できました。
ありがとうございました。
ありがとうございます。
ちょっとテーブル構成がより複雑なので修正してやってみます。
副問い合わせがよくわからなかったものでして。
○売伝(既に結合したビュー)
売伝番号,得意先名,得意先コード,親得意先コード
1000,Ａ商事 本店,20000,20000
1001,Ａ商事 あ店,20001,20000
1002,Ａ商事 い店,20002,20000
1003,B工務店,30000,30000
○売伝明細(既に結合したビュー)
売伝番号,売掛締年月,明細金額
1000,2011/09,1000
1000,2011/09,2000
1000,2011/09,3000
1001,2011/09,500
1001,2011/09,500
1002,2011/09,100
1003,2011/08,300
■期待する結果
Ａ商事 本店,5100
B工務店,300
■試してみたSQL
SELECT
dbo.売伝.得意先名,
dbo.売伝明細.売掛締年月,
(SELECT sum(dbo.売伝明細.明細金額)
FROM
dbo.売伝明細
WHERE
dbo.売伝.得意先コード
in
( SELECT dbo.売伝.得意先コード
FROM
dbo.売伝
WHERE
dbo.売伝.親得意先コード = t.得意先コード))
AS 合計
FROM
dbo.売伝 t,
dbo.売伝 CROSS JOIN
dbo.売伝明細
WHERE
dbo.売伝.得意先コード=dbo.売伝.親得意先コード
■問題点？
CROSS JOINのせいで同じ結果がたくさん出てきてしまいます。
ビュー同士の結合とかはダメなんでしょうか？
うまく親会社ごとに合計したいです。お願いします。
dbo.売伝明細
の部分が要らないと思う
Ａ商事 本店,5100
どういう計算だ？
いろいろと突っ込みどころ満載なんだがとりあえず
SELECT
得意先名,
(SELECT sum(明細金額) FROM 売伝明細
WHERE 売伝番号 in
(SELECT 売伝番号 FROM 売伝 WHERE 親得意先コード = t.得意先コード)
) as 合計
FROM
売伝 t,
WHERE
得意先コード=親得意先コード
とか
JOINでやりたいなら、得意先名いらなければ
select
売伝.親得意先コード,
sum(明細金額) as 合計
from 売伝
join 売伝明細 on 売伝.売伝番号=売伝明細.売伝番号
group by 売伝.親得意先コード
とかでできるんじゃないか
得意先名ほしければ、これに得意先マスタ（あるだろ？）JOINしろ
売掛締年月はどうしたいのかわからん
サブクエリにしてもJOINにしても、テーブルが紐付く条件よく考えてみることだ
結合とか、サブクエリでの集計がよくわからないのでビューを結合してみました。
テーブル名 : 単純結合
売伝番号 金額 得意先コード 売上伝票番号 得意先名 親得意先コード
1000 5000 101 1000 モナー商事 本社 101
1001 2000 102 1001 斉藤製作所 102
1000 1000 101 1000 モナー商事 本社 101
1000 3000 101 1000 モナー商事 本社 101
1002 600 105 1002 モナー商事 岩槻事業所 101
1002 700 105 1002 モナー商事 岩槻事業所 101
1002 900 105 1002 モナー商事 岩槻事業所 101
1003 10000 106 1003 モナー商事 浦和事業所 101
これで親会社結合して
モナー商事 本社 21200
斉藤製作所 2000
とするにはどうしたらよいでしょうか？
すなおに個々のテーブル定義と欲しい結果書け
すみません。
ここのテーブルは多すぎて。
ただ１つのテーブルに親得意先コードと親得意先コードがあるため
集計のときとかとてもやっかいです。
１つの方法としては親得意先コードで、得意先名を再取得してしまえば
望むようなことができるかな？と考えています。
私のＳＱＬ知識不足もありますが、元々DBのないCOBOLからの入れ替えで
かつ途中参加になったもので苦労しています。
今回は開発側ではないけど、ちょっと作って・・・と言われました。
あとでテーブルまとめます。
今までにあげられたSQLではどうダメだったのか書かないと釣りかと思うぜ
それを求めるための方法がいくつかあるけれど、みんなそういうSQL書いてくれてるはず。
mysql5.1
select
*
from
tblA inner join (tblB left join tblC on tblB.out_c = tblC.key_c) on tblA.out_b = tblB.key_b
select
*
from
tblA inner join tblB on tblA.out_b = tblB.key_b left join tblC on tblB.out_c = tblC.key_c
PostgreSQL 9.0
・テーブルデータ
posts
id
post_tags
post_id
tag_id
tags
id
tag（タグ文字列）
・欲しい結果
複数のタグ(tags.tag)を指定してAND検索でpostsを取得
・説明
実際にはユーザテーブルも絡んだりしてもうちょっと複雑なのですが、タグ一つでの検索の場合は以下のような感じになると思います。
SELECT posts.* FROM posts
JOIN post_tags ON post_tags.post_id = posts.id
JOIN tags ON tags.id = post_tags.tag_id
WHERE tags.tag = 'javascript'
これを例えばタグ「javascript」とタグ「php」両方を持つpostを取得するにはどうしたら良いでしょうか？
>>6は見たかな？
かな〜り特殊な書き方だけどやってみた。
select * from (
select *
from crosstab('SELECT posts.id,post_tags.tag_id, tags.tag FROM posts
JOIN post_tags ON post_tags.post_id = posts.id
JOIN tags ON tags.id = post_tags.tag_id ORDER BY 1, 2' ) AS
ct( id integer, tag1 character varying, tag2 character varying,tag3 character varying, tag4 character varying)
) as t
WHERE concat(tag1,tag2,tag3,tag4) LIKE '%javascript%'
AND concat(tag1,tag2,tag3,tag4) LIKE '%php%'
ちなみにconcatの部分は9.1じゃない場合はnullを除外して連結しなおす必要がある。
あと、crosstabを使ってるから、tablefuncモジュールインストールしないといけない。
最後のWHERE句は以下のように変更してもいける
WHERE (tag1 = 'javascript' OR tag2 = 'javascript' OR tag3 = 'javascript' OR tag4 = 'javascript')
AND (tag1 = 'php' OR tag2 = 'php' OR tag3 = 'php' OR tag4 = 'php')
WHERE post_tags.tag_id in (SELECT id FROM tags where tag = 'javascript' )
AND post_tags.tag_id in (SELECT id FROM tags where tag = 'php' )
とかじゃいかんのか？
JOINが増えてるだけでやろうとしてることは同じような感じでした！
参考にします、ありがとうございます
>>63
crosstab初めて聞きました！
まだSQL文を理解できてないですが、勉強してみます、ありがとうございます
>>64
post_tagsを2回JOINすれば大丈夫そうです！
SELECT posts.* FROM posts
JOIN post_tags AS pt1 ON pt1.post_id = posts.id
JOIN post_tags AS pt2 ON pt2.post_id = posts.id
WHERE pt1.tag_id in (SELECT id FROM tags where tag = 'javascript' )
AND pt2.tag_id in (SELECT id FROM tags where tag = 'php' )
もしくはJOINだけでもいけました。
SELECT posts.* FROM posts
JOIN post_tags AS pt1 ON pt1.post_id = posts.id
JOIN tags AS t1 ON t1.id = pt1.tag_id
JOIN post_tags AS pt2 ON pt2.post_id = posts.id
JOIN tags AS t2 ON t2.id = pt2.tag_id
WHERE t1.tag = 'javascript' AND t2.tag = 'php'
パフォーマンステストをして方法を検討したいと思います
皆さんありがとうございました！
投稿のテーブルをつくり、
そこに１つの投稿へのレスもまとめて１つのテーブルに収めています。
設計としては以下のようになっています（説明のため多少シンプルにしています）
投稿テーブル
・id ID
・parent_id 上記のIDを外部キーとしたもの
・message 投稿内容
現在は
１．投稿テーブルから１ページに表示したい行を取得する
SELECT * FROM posts WHERE parent_id IS NULL LIMIT 10
２．上記で取得した行の id を parent_id としている行を取得する
SELECT * FROM posts WHERE parent_id = 1001;
SELECT * FROM posts WHERE parent_id = 1002;
SELECT * FROM posts WHERE parent_id = 1003;
：
つまり投稿１つに対してレスを探すためにSELECT文が１つ発行されており、
１ページ表示するのに 表示する投稿数+1 のSQL文が発行されてしまっています。
これを１つのSQL文で取得したいです。
別テーブルだとJOINでできるかと思いますが、同じテーブルでどうするのかがわかりません。
どういうSQL文にしたらよいでしょうか？
MySQL 5.1.51です。
再帰的に、レスのレスのレス．．．も欲しいとなると
・再帰ＳＱＬをつかう
・ストアドプロシジャで処理する
辺りが通常の手段だが、MySQLでサポートされてるかどうかはしらね
どっちもダメならホストアプリでやるかデータ形式工夫するかしないとダメだね
INNER JOIN になる、であってるでしょうか？？
と言っておいて考えると、CROSS JOINしてからWHEREで絞っても同じだから
結合条件があろうとなかろうとCROSS JOINと等価なのか
なんだけどデータを挿入か上書きのどちらかをしたいときって
重複するデータの存在を調べたうえでif文で分岐させてUPDATE、REPLACEするのと
とりあえず重複があるかどうか分からない状態でDELETEさせてINSERTするのと
どっちが早い？
> UNIQUEインデックスの新しい行と同じ値を持っていれば、
> 古い行は新しい行が挿入される前に削除されるという事以外、
> INSERTと全く同じように機能します。
ふぅん
俺ならいきなりINSERTする。
ON DUPLICATE KEY UPDATE 〜 付きで。
なんでREPLACEじゃないの？
指定したすべてのキーワードが関連づけられているデータを探し出すSQL文はどのように書けばいいでしょうか？
データ構造はdata, keyの二つで、
data=1に20と30のkeyが関連づけられていたら、
(1,20),(1,30)
のようなデータが入っています。
>>6
jinbutu、no、orderplan
A 1 M
A 2 N
A 7 M
B 3 N
B 8 N
B 9 N
C 13 M
C 14 N
C 15 M
C 16 N
C 17 M
このtblをdelete文で整理したいんですが
jinbutuのなかでorderplanが同じ場合は
noが一番小さいやつだけを残したいです
↓このようにしたい
A 1 M
A 2 N
B 3 N
C 13 M
C 14 N
よろしくお願いします
where exists (select *
from tbl T2
where tbl.jinbutu = T2.jinbutu
and tbl.orderplan = T2.orderplan
and tbl.no > T2.no)
;
DBのバージョン PupSQLite 1.9.13.5
テーブル datetime INTEGER PRIMARY KEY, ymdhm text
MT4 というFX のトレードや、バックテストといった、過去の検証を行ったりするプログラムを、SQLiteを
用いて保存しているのですが、文字列フィールドのエスケープシーケンスについて教えてください。
string Table = "MATRIX_"+Period();
sqlite_exec (DB, "create table "+Table+" (datetime INTEGER PRIMARY KEY, ymdhm text)");
YMDHM[0] = "201010140730";
query = "insert into "+Table+" (datetime,yymmdd ) values (" + t1 + "," + YMDHM[0] + ");";
上記の(" + t1 + "," + YMDHM[0] + ");"; このあたりで、
sqllite error code 1
#define SQLITE_ERROR 1 /* データベースが間違っています、または存在していません */
このエラーが出ます、文字列のエスケープシーケンスのやり方が間違っていると思うのですが、分かりません
教えて頂けないでしょうか。
どこで聞いたらいいでしょうか？
VALUE句もTEXTならシングルクォートで囲えよ・・・まあSQLiteなら数値なら通るだろうけど。
そうなると独自実装みたいなSQLは排除する必要があります。
主要なフリーのRDBMS
MySQL PostgreSQL SQLiteあたりでどれでも動くような書き方をするのに、
参考になるサイトや文献があったら教えてもらえませんでしょうか？
or
標準SQLの範囲で記述するのはもちろん、各RDBMSが実装できていない部分を調べて、それらを使わないようにする。
これはマニュアルを見るのが楽。
insert into TableName (datetime) values (datetime('now', 'localtime'));
このようにその日の日付(2011-10-17)をもったレコードを1日1回挿入しているのですが
90日まで遡ってまでのレコードを取得したい場合どう書くのが適切でしょうか？
where 日付レコード > 現在時刻-90日分の時刻
今日から遡って取得したいから
SELECT * FROM TableName ORDER BY id DESC
WHERE datetime > datetime('2011-10-17', '-90 days');
で大丈夫かな
>>94
SQL初めて触ったけど純粋に datetime > 'yyyy-MM-DD' の比較が成り立つのが不思議でして
型毎に演算子をオーバーロードしているようなものです
TOP カテ一覧 スレ一覧 2ch元 削除依頼 ▲
・ 次のスレ
6: DBのデータ検証＆便利ツールはどんなのをお使い？ (290)
7: Oracle 質問総合スレ6 (872)
8: MySQL 総合 Part21 (812)
9: だれかみずほ銀行のDBを直してやれよ (28)