1read 100read
2012年07月データベース9: SQL質疑応答スレ 13問目 (215) TOP カテ一覧 スレ一覧 2ch元 削除依頼
DBのデータ検証&便利ツールはどんなのをお使い? (291)
MySQL 5.0 (547)
MSDEよりいいDB、ありませんか? (345)
【Pure】HSQL database engine【Java】 (294)
IBM DB2 総合スレ2 (489)
頼むから正規化しろよ 第二正規形 (283)

SQL質疑応答スレ 13問目


1 :2012/08/02 〜 最終レス :2012/11/07
このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ:
SQL質疑応答スレ 12問目
http://toro.2ch.net/test/read.cgi/db/1316769778/

2 :
SQL言語リファレンス一覧
Oracle Database
http://docs.oracle.com/cd/E16338_01/server.112/b56299/toc.htm
Microsoft SQL Server
http://msdn.microsoft.com/ja-jp/library/bb510741
IBM DB2 Database
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/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/

3 :
過去スレ
12問目:http://toro.2ch.net/test/read.cgi/db/1316769778/
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/

4 :
よくある質問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 :
よくある質問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 :
よくある質問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 :
よくある質問4
(問)
列の数が可変な問合せはどう書きますか?
(答)
標準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

8 :
よくある質問5
(問)
年月(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ならこれを適当に改変すれば動きますが
 どのみちお奨めしません。

9 :
以上、テンプレ終わり

10 :

前スレはすまんかった

11 :
>>1
同じく申し訳ない。スレチ話題進行させてしまった。

12 :
どうも
やっぱ前スレの984は無理ですかね

13 :
>>12
前スレにちゃんとレスあるよ。
あとは自分でdatetimeをフォーマットする関数見つけてくればいいだけだよ。
か、もしくは文字列として扱って文字数で切ってもいいと思うけど。
それをDISTINCT付けて呼び出せばいいだけ。

14 :
>>13
なるほどわかりました
DateTimeをフォーマットして時刻を切り捨ててDistinctすれば良いってことですね
やってみますどうもです

15 :
SQLServerならConvertで111だな

16 :
http://26g.jp/u/0Fkn6uDJ

17 :
>>16
商用リンク
踏むべからず

18 :
ある期間において注文してくれた顧客IDを、日付ごとに一覧するには、次のようにします。
select date(created_at), customer_id
from sales_orders
where date(created_at) between '2012-08-01' and '2012-08-07'
group by date(created_at), customer_id
order by date(created_at), customer_id
ここで、ある期間において注文してくれた顧客数(重複を排除したユニーク数)を知るにはどうしたらいいでしょうか。

19 :
select count(distinct customer_id)
from sales_orders
where date(created_at) between '2012-08-01' and '2012-08-07'

20 :
>>19
すみません、正しくは「ある期間において注文してくれた顧客数(重複を排除したユニーク数)を *日付ごとに* 知るにはどうしたらいいでしょうか」でした。

21 :
少しは応用しろよ

22 :
select order_date, count(*) from
(
select distinct date(created_at) as order_date, customer_id
from sales_orders
where date(created_at) between '2012-08-01' and '2012-08-07'
)
group by order_date
order by order_date;
試してないので無保証

23 :
>>22
副問い合わせ使うんですか、なるほど!
あとgroup by使わなくてもdistinctでいけるんですね。大変勉強になりました。
ありがとうございました。

24 :
副問い合わせ使わんでも、COUNT(DISTINCT customer_id)でいける気がするんだが

25 :
質問しに来たら「よくある質問1」がドンピシャだった

26 :
こうかな
select date(created_at), count(distinct customer_id)
from sales_orders
where date(created_at) between '2012-08-01' and '2012-08-07'
group by date(created_at);

27 :
仕事柄毎日SQLをごりごりしてるんだけど未だに苦手な小計なんですが
グルーピングIDの番号って何か規則性があるんでしょうか?
小計中計大計とか出す時いつも結果を見ながらグルーピングIDの番号を調べているんですが面倒臭くて
SQLをメンテすると番号変わったりするし大嫌いなんだけどパフォーマンスを考えたらロールアップ以外の選択肢は無いし

28 :
>>27
自分がつかってるDBMSが何かもわからないのか?
まずそのマニュアル見ろよ
ORACLEとSQL Serverで見る限りではGROUPING_IDは指定した各項目がビットに割り当てられる数値っぽいが
2進数解らんのか?

29 :
なんでそんな態度悪いの?

30 :
にちゃんなんてこんなもんだろ。
優しく対応して欲しいなら、Oracle なり MS のセミナーとかに行けよ。

31 :
2chだからわざと態度悪くしてるの?

32 :
この程度で態度悪いと思うなら見ない方が良いと思うぞ

33 :
出た見ないほうがいい

34 :
自分で調べたりしないくせに人に優しく教えるよう強要するって図々しいなあ

35 :
何でこのスレにいるんだ

36 :
>>28 なんて、ちゃんと答え書いてあるし、まっとうな回答だと思うが。
そもそも、会社で先輩に聞いてもこんな回答だろ。

37 :
どなたかご教示ください。お願いします。
環境:SQLServer 2008
目的は、以下のようなデータでの、カラムBのnull以外の件数の取得です。
A(int) ・B(int)
------ ・----
100 ・ ・ null
count(B)の結果は0件となり正しいですが、・「警告: NULL 値は集計またはその他の SET 演算で削除されました。」・と警告が出ます。
この警告を取り除くにはどのようにすればよいでしょうか。

実験用に以下のクエリで再現できます。
select・
COUNT(null_column)・
from・
(
・ ・ select cast(null as int) null_column
) A
よろしくおねがいします。

38 :
やりたいこととクエリとテーブルの例がまったくかみ合わないんだが。
SET ANSI_WARNINGS OFF

39 :
〉38
実行したら警告出なくなりました。
ありがとうございます。

40 :
それでいいんだ…

41 :
>>28はきっと小計の出し方しらなかったと思う、んで知ったかぶりするためにすぐ調べた
でも難しくてムキー

42 :
>>41
お前そんなに悔しかったのか?w

43 :
質問:「指定条件に一番近い最大値を持つレコード」でテーブル結合したい
環境:postgresql 9.0
価格変動テーブル
kakaku_datetime name price
2012-08-01 10:00:00 トマト 100円
2012-08-01 10:00:00 バナナ 200円
2012-08-10 10:00:00 バナナ 180円
2012-08-15 10:00:00 トマト 150円
売上履歴テーブル
uriage_datetime name
2012-08-01 12:00:00 トマト
2012-08-05 18:00:00 トマト
2012-08-08 15:00:00 バナナ
2012-08-16 19:00:00 トマト
を結合させて、以下のように「その時にいくらだったか?」を含めたテーブルにしたいです。
uriage_datetime name price
2012-08-01 12:00:00 トマト 100円 ←kakaku_datetime = 2012-08-01 10:00:00 のトマトの価格を取ってくる
2012-08-05 18:00:00 トマト 100円 ←kakaku_datetime = 2012-08-01 10:00:00 のトマトの価格を取ってくる
2012-08-08 15:00:00 バナナ 200円 ←kakaku_datetime = 2012-08-01 10:00:00 のバナナの価格を取ってくる
2012-08-16 19:00:00 トマト 150円 ←kakaku_datetime = 2012-08-15 10:00:00 のトマトの価格を取ってくる
よろしくお願いします。

44 :
前スレよりコピペ
770* 名前:sage [] 投稿日:2012/06/21(木) 12:06:59.60 ID:oEiJL9az
すまん、763です。具体的に言うと
株価Tab(時刻、株価)
為替Tab(時刻、$\為替レート)
を株価のテーブルをメインにして
SELECT時刻、株価、$|為替レート
にしたいんだけど株価と為替の時刻は当然一致していないけど、
株価の時刻からみて最新の為替レートを表示したいんです。
よろしくおねがいします。
777 名前:NAME IS NULL [sage] 投稿日:2012/06/21(木) 19:14:27.33 ID:???
>>770
こうかな?
select *
from  A T1
    left outer join
    B T2
    on T1.a1 >= T2.b1
where  not exists (
        select *
        from  B T3
        where  T1.a1 >= T3.b1
        and   T2.b1 < T3.b1
    )
order by a1
;

45 :
>>44
素早い回答有り難うございます!ちょっと試してみましたが何とかなりそうです!

46 :
EXISTSとINの違いをわかり安く教えてもらえませんか?

47 :
一緒です

48 :
というのは嘘です

49 :
INは項目、EXISTSは行

50 :
NULLを含む含まないで違うんじゃなかった?

51 :
少し前に簡単な表で説明してくれてたが何だったかなあ

52 :
select文では、joinを使えば複数のテーブルを使った条件式が指定できます。
-- Sales部門に属する従業員の一覧
select e.id, e.name, e.saraly from employees as e
join departments as d on d.id = e.department_id
where d.name = 'Sales';

同じことをupdate文でもしたいのですが、どうすればいいでしょうか。
-- Sales部門に属する従業員のデータを更新
update employees as e set saraly = saraly + 10
join departments as d on d.id = e.department_id
where d.name = 'Sales';

副問い合わせを使えばできますけど、できればselect文と同じようにjoinを使って指定したいです。
update employees as e set saraly = saraly + 10
where e.department_id in (select id from departments where name = 'Sales');

よろしくお願いします。

53 :
>>52
SQL Server なら FROM で書けるけど
他DBは知らない

54 :
標準SQLではjoinはfrom句かmerge文のusing句にしか書けず、
from句はselect文(かサブクエリ)にしか書けない

55 :
>>53,54
ありがとうございます。
PostgreSQLではupdate文でfrom句が使えるようです。
ttp://www.postgresql.jp/document/current/html/sql-update.html
これで解決できました。

56 :
t1
id str
1 a
2 b
3 c
t2
id str
1 d
2 e
3 f
t3
id t1.id t2.id
1  1  1
2  1  2
3  1  3
4  2  1
5  3  1
6  3  3
こういうテーブルがあります
left joinで結合したところ
t1.id t1.str t2.id t2.str t3.id
1   a   1   d   1
1   a   2   e   2
1   a   3   f   3
2   b   1   d   4
3   c   1   d   5
3   c   3   f   6
こうなりました
これを
t1.idが2でt2.idが2か3はnullなので入っていません
同様にt1.idが3でt2.idが2のとこもnullなので入っていません
これを入れて
t1.id t1.str t2.id t2.str t3.id
1   a   1   d   1
1   a   2   e   2
1   a   3   f   3
2   b   1   d   4
2   b   2   e  null
2   b   3   f  null
3   c   1   d   5
3   c   2   e  null
3   c   3   f   6
こんな感じのを取得したいのですがどのようなSQLをかけばいいでしょうか?
使用DB SQLite3

57 :
>>56
t1とt2で直積取って、それにt3を外部結合
SQLite3ってCROSS JOIN使えたっけ?

58 :
>>57
cross joinは使えるみたいです
外部結合はleft outer joinが使えるらしいので試したのですが
使い方が悪いのかleft joinと結果が同じでした
ちょっとcross joinでいろいろ試してみます

59 :
>>58
left outer joinとleft joinは同じだぞ。outerが省略されただけ
t1とt2をcross joinしてからt3をleft joinするんだ

60 :
マテビューでセレクトする項目に空白を含めたいのですが、可能でしょうか?

61 :
可能です

62 :
では、セレクトするのは空白・型は日付型にできるでしょうか

63 :
お前の使ってるDBMSの日付型が空白を許容するなら可能

64 :
>>63
神様!
ありがとうございます!

65 :
え、マテビューで空白のselectって無理じゃない?

66 :
>>65
SELECTする取得元の項目がNOT NULLでなければいけた気がするよ

67 :
プログラムからSQLの呼び出しについての質問ですが、
以下のSQLのテンプレートがあり、@〜Cにweb画面から渡された値を展開しています。
インジェクション対応していなかったようで、その対応を考えてます。
インジェクションでサブSQLを実行され情報漏えいできなければなんでもいいとのことで、
「(」だけをケアすればいいかなと思い、「(」を削除して
Aは「'」を「''」にエスケープしますが、それ以外は「(」をカットしようかと考えています。
@とBにて、hogeの項目情報は公開されているので、他の項目を指定される文にはいいとの条件です。
select xx1, xx2 from hoge where @ = 'A' order by B C
「(」をカットだけすれば、とりあえず他テーブル情報を取得または更新されることは防げるでしょうか?

68 :
スレ違いだし、そのレベルでは話にならん

69 :
いいカモだなw

70 :
使用DB:postgresql
DBにこんなテーブルがあり
name price
りんご 100
バナナ 200
みかん 150
手元にこんなCSVデータがあって
りんご,150
みかん,100
CSVのデータを元に一括でUPDATEしたいです。
name price
りんご 150
バナナ 200
みかん 100
CSVデータの文字列加工はPHP使うので大体のことは出来ます。
最悪1行ずつUPDATEでいいのですが、行数が多いので出来ればSQL発行を1回にしたいです。お願いします。

71 :
1回ってINSERT文みたいにつなげたいってこと?
そのまま1行ずつ書いて、BEGIN ENDで挟むだけじゃだめかね。

72 :
そうです、UPDATEでも
INSERT INTO fruits (name,price) VALUES(りんご,150),(みかん,100)
みたいな書き方で1回ですっきり出来る方法はないかなーと。
特にないのならご私的の通りこんな感じで行こうと思います。
BEGIN;
UPDATE fruits SET price = 150 WHERE name = りんご;
UPDATE fruits SET price = 100 WHERE name = みかん;
END;

73 :
×ご私的
○ご指摘

74 :
なぜそうしたいかというのは、「UPDATE大量発行より1回の発行のほうが軽いだろJK」という思い込みが理由なので、
もし何らかの方法があるとしても負荷は大して変わんねーよということであれば>>72のでもいいです。
(UPDATE対象は1万レコードくらい、カラムは数個)

75 :
UPDATE fruits
SET price = CASE
WHEN name = りんご THEN 150
WHEN name = みかん THEN 100
ELSE price END;

76 :
>>75
ありがとうございました!

77 :
まずDBにワークテーブル作って、そこにCSVの内容突っ込んでから
SQLで更新かければ、更新のSQLは1行ですむぞ

78 :
同じ名前が複数あったとき困る気がする、、スクリプトで排除するのかな

79 :
大体、単価をマスターに含めること自体が
部分関数従属がだな

80 :
って、確認せずに書いてもいないことを思い込みで進めて、後で困るパターン。

81 :
使用DB:mysql5
t1
id str
1 a
2 b
3 c
t2
id t1.id t1.id
1 1 2
2 2 3
3 3 1
このふたつのテーブルを結合して
id t1.id t1.str t1.id t1.str
1 1 a 2 b
2 2 b 3 c
3 3 c 1 a
こういう結果を得る方法はありますか・・・?
select * from t1 a
INNER JOIN t2 b on b.id = a.id
and b.id = a.id
とやってみても、idが2つあるので結合出来ないです。

82 :
t2のカラム名おかしくね?
t2にt1を2回JOINするだけだと思うけど

83 :
同じテーブルを複数JOINできるよ

84 :
t2のカラム名、いくらmysqlでもこんなカラム名許すわけないよな
t2って実テーブルじゃないのか?
とりあえずt2のカラム名ちゃんとしてれば、t1に別名つけて2回Joinすればいけるはずだが

85 :
許しちゃうんだなこれが
select 1 as `t1.id`

86 :
複数のテーブルを結合して、その列名のみを取得するのはできるのでしょうか?
教えてください
SQLServerです。

87 :
SQL Server知らんからググったが、syscolumnsとサブクエリ使えばいけるんじゃね

88 :
列名のみ取得ってのがイマイチ何をどうしたいのか解らんな
where 1=0とかで良いような気もする

89 :
>>88
値として列名を取得したいのです。

90 :
列名も知らないDBを使って何かしようとしてるの?
SQLインジェクションのやり方の質問か

91 :
>>87
どうやったらできますか?
調べてもよくわかりません。
教えてください

92 :
>>91
それ結合とか関係ないな
システムテーブルってDBMS固有な場合が多いし、SQL Serverのスレ行って聞け

93 :
>>92
わかりました。ありがとうございました。

94 :
INFORMATION_SCHEMA が使えるだろ。

95 :
画期的なDBメンテツールを開発中なの鴨試練

96 :
こんなスレで聞いてるぐらいだから、あらぬ方向に画期的なツールになりそう…

97 :
SQL Server 2005です
INSERT テーブル名
SELECT
"あ",
"い",
"う",
(SELECT A列
FROM 別のテーブル名
WHERE B列 = 0)

これってサブクエリの制限にひっかかるのでしょうか?
コンパイルは通るみたいですが・・・

98 :
INSERTの構文調べようか

99 :
>>97
サブクエリの制限ってのが何を言ってるかよくわからんが
単一行を返すことを期待するサブクエリが複数行返したらエラーになった気はするな
それは実行時エラーで、コンパイル(つか文法チェック)ではエラーにはならない
SQL ServerならそのSQLは通る気がする
別のテーブル名 にB列=0の行が複数あれば実行時エラーがでる
そうじゃなければ多分動くだろ

100read 1read
1read 100read
TOP カテ一覧 スレ一覧 2ch元 削除依頼
【富士通】Symfoware【ティムポウェア】 (381)
DB板自治・質問・雑談スレ (891)
【オラクル>ポストグレスの理由】⇒言い訳の為 (247)
数十メガバイトのファイルをどんどん格納できるDB (202)
DB板のみんなでUDやるぞ! (413)
システム構築ベンダの実力 (937)
--log9.info------------------
神コテ ハッピーアトピーさんを応援するスレ (599)
医者にこんな事いわれたよ (881)
爪切りしようぜ!@毎日報告 (390)
(;・∀・)日光浴でよくなるかな? (503)
【朗報】佐賀大がアトピー慢性化の仕組みを解明 (360)
【アトピーでも】ワセリン治療法【前向きに】3 (938)
岡山で良い病院を教えて (789)
マリファナ吸うとアトピー治るぞ (211)
ダニ ホコリアレルギーの香具師★2 (466)
◆◆花粉症 関東地方 総合◆◆ Part18 (596)
【滝の】血管運動性鼻炎+花粉症の人【鼻水】 (480)
■使える花粉症用品■使えない花粉症用品■ (262)
口内・喉がカユイ人 (376)
化学物質過敏症等について議論するスレ (226)
鼻づまり対処法教えてください・・ (472)
【かゆい】花粉症によく効く目薬【ゴロゴロ】 (201)
--log55.com------------------
墓なら不審なシャウトも通報されないのにお前らはFFか
Narrow No.80
エアコンつけっぱ民は室温設定は何℃にしてるの
2−0でボール回ししなかった日本死亡
クロウラーの巣で遭難した糞タルPTが無事発見されたのにお前らは
全英でしこるんよ・・・
すげーことに気付いた、名前が5文字を超える女は存在しない
Akbaba