よくある質問１ （問） 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 ;
5 ：
よくある質問２ （問） 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 ：
よくある質問３ （問） 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によって文法がかなり違うので注意
よくある質問５ （問） 年月(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ならこれを適当に改変すれば動きますが どのみちお奨めしません。
select x ,y ,z ,(select SUM(S) from table2 T2 where T2.k = T1.k) as r from ) select x,y,z,k from abc ) as table1 T1 ←導出テーブルでこのT1という相関名をつけられません
31 ：
>>30 SQL SERVER 2008 R2 EXPRESSです。 よろしくお願いします。 導出テーブルに相関名ってSQLデフォルトでも普通はつけられないのかな。
32 ：
それ相関名じゃなくない？ table1ってのが相関名だろ。相関名二つもつけれるか？
33 ：
>>32 table1だけではエラーになってしまいました。 select x ,y ,z ,(select SUM(S) from table2 T2 where T2.k = table1.k) as r ←ここで「table1」と指定することになりますが、エラーになって外テーブルと相関しません。 from ) select x,y,z,k from abc ) as table1 ←外側テーブルとなる導出テーブルを名づけます。 かといって、as table1 T1と二重に名前付けできません。 導出テーブルを相関クエリではつかえないってことでしょうか。
SQLserver 2008 R2 です where句の等号不等号の表記で、 where ・・・ and x <= y だと、okで、 where ・・・ and x =< y だと、エラーになります。 これって、標準なんでしょうか。 どっちでも良いように思うんですが、納得のいく説明があれば教えてください。
tableA id name 1 サザエ 2 カツオ 3 ワカメ 4 タラヲ というテーブルがあり、tableA.idをデータとしてセットする tableBがあったとします。 tableB tableA_id key1 key2 key3 4 1 2 3 key1〜key3にセットされる値は、tableA.idの値になります。tableA_idに関しても同様です。 この2つのテーブルからtableBのkey1〜key3にセットされているidに紐づく 実際のnameを取得したいです。 tableBがちょっと妙な気がするのですが、「tableA.idを構成しているのが、key1〜key3という意味」らしいです データ取得時にtableA_idにセットする値が渡ってくるものとします SELECT name FROM tableA WHERE id IN (SELECT key1, key2, key3 FROM tableB WHERE tableA_id = 4); 最初はこのようなSQL文を組んでみたのですが、サブクエリ的にダメなようなので SELECT name FROM tableA WHERE id IN (SELECT key1 FROM tableB WHERE tableA_id = 4); というようなSQL文をkey1〜key3まで繰り返すことしか思いつきません。。 もう少し効率的なSQL文が組めればと思うのですが、どなたかご指導頂けないでしょうか？
>>44 試してないけど SELECT name FROM tableA WHERE id IN ( SELECT key1 FROM tableB WHERE tableA_id = 4 UNION SELECT key2 FROM tableB WHERE tableA_id = 4 UNION SELECT key3 FROM tableB WHERE tableA_id = 4 ) はできないかな。
>>44 最終的に欲しいデータが解らん タラヲ,サザエ,カツオ,ワカメ で良いのか？ 普通に考えたらtableAを4回joinするだけだが select t.name,t1.name,t2.name,t3.name from tableB join tableA t on t.id=tableB.tableA_id join tableA t1 on t1.id=tableB.key1 join tableA t2 on t2.id=tableB.key2 join tableA t3 on t3.id=tableB.key3 こんな感じ。mySQLで動くかどうかは知らん
追加しようとしているレコードを主キーから追加・修正を自動判別して実行させたいんですけど良い方法は無いでしょうか？ 今はselect count (*) from tableName where primarykey=fookeyで 1ならupdate、0ならinsertってしているんですがこの方法しか無いのでしょうか？ python2.6.6からsqlite3を使用しています。
MYSQL5.5 key a b -------------------- 1 3 2 2 1 1 3 5 2 4 2 3 こんな感じでkeyが1〜500万くらいまでの500万行のテーブルなんですが UPDATE テーブル SET a = a + 1 WHERE key = 5749 UPDATE テーブル SET b = b + 1 WHERE key = 18312 UPDATE テーブル SET a = a + 1 WHERE key = 991127 〜 みたいなUPDATE文を300回くらい同時に実行すると10秒程度掛かってしまい 遅すぎて死にそうです なにか早くする方法ないでしょうか
エンジンはInnoDBです >>62の10秒超というのは ↓のようなストアドプロシージャを実行した時に掛かった時間です CREATE PROCEDURE test( IN x1 INT, IN x2 INT, IN x3 INT 〜〜 ) BEGIN UPDATE テーブル SET a = a + 1 WHERE key = x1 UPDATE テーブル SET b = b + 1 WHERE key = x2 UPDATE テーブル SET a = a + 1 WHERE key = x3 〜〜 ×300行 今自宅でローカルに同様の構成で10万行のテーブルを作って 100行でプロシージャを実行してみたらやはり3〜3.5秒程掛かります 試しに UPDATE テーブル SET a = a + 1 WHERE key = 1 を1行だけ実行じてみたら (クエリの実行時間 0.0474 秒)と出ました phpmyadminから実行してるのですがこの表示が間違っているでしょうか MYSQLのUPDATEが遅いというのは色々読んでわかったのですが 1行だけで50msというのは何か問題があると考えた方がいいのでしょうか
select id from ( (select id from table1) except (select id from table2) ) as t というSQLがあります。このとき、「select id from table1」の部分を、リテラル値を埋め込んだ形にする方法があれば教えてください。 イメージとしては select id from ( (81, 72, 47, 99) except (select id from table2) ) as t; とか select id from ( (select id from (81, 72, 47, 99)) except (select id from table2) ) as t; みたいな感じです。 (PostgreSQL 9.x) よろしくお願いします。
