よくある質問１ （問） 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) よろしくお願いします。
上記のような2000万行程度のテーブルに↓のようなSELECT文を投げたいのですが SELECT abs(a + b - c) AS abs_hoge, key FROM table WHERE key = 2 ORDER BY abs_hoge ASC LIMIT 1; 大体20〜40秒程度の時間が掛かります インデックスはkeyにのみ張ってます 一つのkeyの値につき2000レコード程あるので まず2000行フェッチされた後、a+b-cの絶対値でfile_sortされてるみたいなのですが どうにかして処理を速くできないでしょうか 実際はa,b,cだけでなくd,f 〜 y,zくらいまでカラムがあり 場合によって abs(a - f - k)のように文を変えるので複合インデックスも試そうとしましたが難しいです よろしくお願いします
update table1 set col1 = 'value1' where id = 111; update table1 set col1 = 'value2' where id = 222; update table1 set col1 = 'value3' where id = 333; これを1文でスマートに実行するにはどうしたらいいですか。 以下のようなのを考えたけど、どう考えてもスマートではありません。 update table1 set col1 = case when id = 111 then 'value1' when id = 222 then 'value2' when id = 333 then 'value3' end where id in (111, 222, 333);
update table1 set col1='value'||substr(to_char(id),1,1);
145 ：
全件ヒットしちゃうじゃん
146 ：
問題はそこじゃないだろ
147 ：
自分でいうのもなんだけど。
148 ：
１文かどうかなんて重要じゃないと思うが そもそも１文で処理する必要性がわからん
149 ：
排他制御がしやすい
150 ：
複文だとしにくいって言ってる？
151 ：
うん。 ちょっとだけだけど。
152 ：
今回は違うけど、id を書き換えるような場合は1行でやらないと 結果が変わってくる
153 ：
複数行ならtransaction使うだろ。 結果が変わって困るというなら設計がおかしい。
154 ：
transactionフリーにすると後々様々なメリットを享受できる
155 ：
トランザクションを理解できなかった奴がよくこういうことを言ったりする
156 ：
トランザクションを理解出来ないやつほど無駄にトランザクションを使う
157 ：
一文のSQLはトランザクションじゃないと誤解するのも初心者にはありがち
158 ：
俺も昔はそうだった
159 ：
SQL Server 2008 日付と時刻とを date型とtime型と、別々に分離されているものを datetime型に変換するにはどうしたらいいのでしょうか。 declare @date as date; declare @time as time; declare @datetime as datetime; set @datetime = @date + @time; ← かなぁと思ったら、NGでした
160 ：
文字列として組み立てて変換すればいいんじゃね？
161 ：
>>159 型が違うんだからまずdatetimeにしてから足せよ set @datetime = cast(@date as datetime) + cast(@time as datetime); 型が違うものの演算とか代入とか、型の優先順位や暗黙の変換によって結果が決まる奴は よっぼど自信があっても使わん方が良いぞ
PHP5+MySQL 5.1.69を使っているんだけど… id user tag 1 56 hoge 2 24 piyo 3 56 foo 4 12 bar 5 1 hoge 6 2 foo ...続く
って感じのテーブル…要するにuserひとつにタグが最大で8個ぐらい付いているっていうのがある userとtagの組み合わせはユニークになってる（ひとつのuserが同じtagはつけられない） これで例えばhogeのtagがついているuserは他にどんなtagがついているか知りたい それがグループ、オーダーで登録数順になった形式が欲しいんだけど こんなん tag sum foo 26 bar 12 pyo 5 ... 簡単かなって思ったけど全然思いつかない 誰かご教授いただけると有難い
172 ：
tag 毎の登録数が知りたいの？ であれば、select tag, count(*) from table group by tag order by count(*) desc; でいいと思う。 > これで例えばhogeのtagがついているuserは他にどんなtagがついているか知りたい にどう繋がるかはよくわからんけど。
173 ：
いやごめんちょっと違う id user tag 1 56 hoge 2 24 piyo 3 56 foo 4 12 bar 5 1 hoge 6 2 foo ... って状態で格納されてるけど 要するに user tag 56 hoge, piyo, foo 1 piyo, bar, hage 23 bar, foo, piyo 4 hage, bar ... ってphpで出力して使ってる これで 「hogeを登録している人は○○も登録していますよ」って感じにしたいから hogeを登録している人が他に登録しているtagを多い順に集計したい 上の4つだと例えばfooを登録している人は…ってなると
tag sum piyo 2 bar 1 hoge 1 ってなるようにしたいんだ
174 ：
ああそういうことか、ではこうかな？ ※ 手元に環境ないので未検証 select t2.tag as tag, count(*) as sum from table t1 inner join table t2 on t1.id = t2.id where t1.tag = 'foo' and t2.tag <> 'foo' group by t2.tag order by count(*) desc;
select tag, count(tag) as sum from table where tag <> 'hoge' and user in (select user from table where tag = 'hoge') group by tag order by count(tag) desc; こんなか？
179 ：
>>173 そこに書いてあるやつ、上と下でデータ違うじゃねえか ちゃんと合わせとけよ select tag,count(*) from テーブル where user in (select user from テーブル where tag='foo') and tag<>'foo' group by tag order by count(*) desc たぶんこんな感じ
180 ：
>>173 SELECT t2.tag,COUNT(1) FROM tables AS t1 INNER JOIN tables AS t2 USING(user) WHERE t1.tag != t2.tag AND t1.tag='foo' GROUP BY t2.tag ORDER BY t2.tag DESC; こんな感じ？
>>193 説明不足ですいません。 以下のようなカラムからｶﾗﾑAの1から一つ、ｶﾗﾑAの2から一つというように取得したいんです・・・ ｶﾗﾑA ｶﾗﾑB 1 a 1 b 1 c 2 a 2 b 2 c 3 a 3 b 3 c
195 ：
適当 select * from table group by カラムA;
196 ：
>>195 いくらなんでもそれは適当すぎる >>194 select カラムA, (select カラムB from テーブル where カラムA=t1.カラムA order by rand() limit 1); from (select カラムA from テーブル group by カラムA) t1 とかじゃね、mysql手元にないから知らんけど
SQL SERVER 2008 R2 - Microsoft SQL Server Management Studio 10.50.2550.0 の質問なのですが、SQL文発行時等のエラーメッセージが日本語にならないのですがどこに設定があるのでしょう メニューバーその他設定などは全て日本語で、DBの照合順序などもJapanese_CI_ASになっているのですが・・・ 別環境では問題なく全て日本語で出るのですが、新しく作った環境では上記のような状態になってしまいます とても初歩的で申し訳ありませんがご教授お願い致します
205 ：
各キーのトップ５を抽出するＳＱＬを教えてください。
206 ：
SELECT TOP 5
207 ：
id name num とあって id: 1 2 3 4 5 6 7 ... name: tarou hanako tarou tarou tarou hanako tarou num となっています。 update table set num = 1 where name = tarou とすると、tarou全てが1になります。 ですが、idが最大のtarouだけを1にしたいんです。 update table set num = 1 where name = tarou and id = 7 とやれば7のtarouだけを更新することができますが、常に最大のものだけを更新したいので これではできません。 簡単にできそうに見えるんですがやり方が分かりません。 やり方教えてください！ちなみにsqliteを使ってます。
208 ：
update table set num = 1 where id = (select max(id) from table where name = 'tarou') とか？
