SQL質疑応答スレ 7問目

1 名前：NAME IS NULL mailto:sage [2008/10/09(木) 13:11:14 ID:???] このスレは 「こういうことをやりたいんだけどSQLでどう書くの？」 「こういうSQLを書いたんだけどうまく動きません＞＜」 などの質問を受け付けるスレです。 SQLという言語はISOによって標準化されていますが この標準を100%実装したDBMSは存在せず、 また、DBMSによっては標準でない独自の構文が 追加されていることもあります。 質問するときはDBMS名を必ず付記してください。 【質問テンプレ】 ・DBMS名とバージョン ・テーブルデータ ・欲しい結果 ・説明 前スレ：SQL質疑応答スレ 6問目 pc11.2ch.net/test/read.cgi/db/1210940477/ 2 名前：NAME IS NULL mailto:sage [2008/10/09(木) 13:11:59 ID:???] SQL言語リファレンス一覧 Oracle Database otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05750-02/toc.htm Microsoft SQL Server msdn.microsoft.com/ja-jp/library/bb510741.aspx IBM DB2 Database publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp （目次から参照情報→SQL） PostgreSQL www.postgresql.jp/document/pg833doc/html/sql.html MySQL dev.mysql.com/doc/refman/5.1/ja/sql-syntax.html 参考リンク sql.main.jp/cont/sql/map.html www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html oraclesqlpuzzle.hp.infoseek.co.jp/ www.techscore.com/tech/sql/ 3 名前：NAME IS NULL mailto:sage [2008/10/09(木) 13:12:31 ID:???] 過去ログ SQL質疑応答スレ pc8.2ch.net/test/read.cgi/db/1056973582/ SQL質疑応答スレ Part 2 pc8.2ch.net/test/read.cgi/db/1103113155/ 【帰ってきた】SQL質疑応答スレ pc8.2ch.net/test/read.cgi/db/1124178925/ 【帰ってきた】SQL質疑応答スレ 2問目 pc8.2ch.net/test/read.cgi/db/1141622643/ 【帰ってきた】SQL質疑応答スレ 3問目 pc11.2ch.net/test/read.cgi/db/1160458216/ 【帰ってきた】SQL質疑応答スレ 4問目 pc11.2ch.net/test/read.cgi/db/1176553195/ SQL質疑応答スレ 5問目 pc11.2ch.net/test/read.cgi/db/1193486961/ 4 名前：NAME IS NULL mailto:sage [2008/10/09(木) 13:13:10 ID:???] よくある質問１ （問） 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 名前：NAME IS NULL mailto:sage [2008/10/09(木) 13:14:25 ID:???] よくある質問２ （問） 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 名前：NAME IS NULL mailto:sage [2008/10/09(木) 15:40:31 ID:???] いちょーつ 7 名前：NAME IS NULL [2008/10/10(金) 00:27:52 ID:JpXt+XXo] SQL質疑応答スレ 6問目にて時間と分の足し算を質問した者です。 回答くださった皆様ありがとうございます。 参考にして頑張ってみます。 8 名前：NAME IS NULL [2008/10/10(金) 00:51:42 ID:1bc3EllS] ・DBとバージョン SQLite ・テーブルデータ MainTable key　… ------- 002　… 014　… 034　… 105　… SubTable key　data　time … ----------------- 002　2345　1 … 002　1345　5 … 002　1123　8 … 034　4562　3 … 034　2930　9 … 105　1222　7 … 105　4850　8 … ・欲しい結果 key　c1　c2 ----------------- 002　0　2 014　0　0 034　1　1 105　1　2 ・説明 c1はSubTableのdataが1000以上かつtimeが5以上の数。c2はSubTableのtimeが5以上の数。 MainTableを軸にしてSubTableに対応するkeyが無い場合は0を表示したいのですが、 SELECT 　m.key, 　coalesce( t1.c, 0 ) as c1, 　coalesce( t2.c, 0 ) as c2 FROM MainTable m 　LEFT JOIN 　( SELECT m.key, count(*) as c 　　FROM subTable s 　　INNER JOIN MainTable m ON m.key = s.key 　　WHERE s.time >= 5 and s.data >= 1000 　　GROUP BY m.key ) t1 　ON t1.key = m.key 　LEFT JOIN 　( SELECT key, count(*) as c 　　FROM subTable 　　WHERE s.time >= 5 　　GROUP BY key ) t2 　ON t2.key = m.key バインドする時に「s.time >= 5」の部分が２回出てきたり、同じようなサブクエリを ２回使ってたり…。もっとスマートな形があると思うのですが、良い解決策が見つかりません。 9 名前：8 [2008/10/10(金) 01:04:08 ID:1bc3EllS] すみません。ちょっと間違えました。 ・MainTableはoverカラムがあります。 誤：key　… 正：key　over　… ・c1の条件は1000以上では無く、MainTable.over以上です。(1000以外の場合もあります) 誤：c1はSubTableのdataが1000以上かつtimeが5以上の数。 正：c1はSubTableのdataがMainTable.over以上かつtimeが5以上の数。 ・なので、何の為にMainTableをJOINしてるかわからないSQL文になっていました。 誤：WHERE s.time >= 5 and s.data >= 1000 正：WHERE s.time >= 5 and s.data >= m.over 以上、よろしくおねがいします。 10 名前：NAME IS NULL mailto:sage [2008/10/10(金) 02:39:38 ID:???] >>9 SQLiteで動くかどうか未確認。 SELECT key,sum(CASE WHEN data >= over THEN 1 ELSE 0 END) AS c1 ,count(data) AS c2 FROM MainTable LEFT JOIN (SELECT * FROM SubTable WHERE time >= 5) AS T1 USING(key) GROUP BY key; --補足 c1もc2もtime>=5なので、サブクエリT1で先に抽出。 data >= over の判定はCASEで。 と言う具合に条件判定を別けてるので、実運用時の条件次第では 複雑なCASE文になるかもしれんよ。 11 名前：8 mailto:sage [2008/10/10(金) 11:52:55 ID:???] ありがとうございます。 なるほど。sumの中にCASEですか。 そういう書き方ができるとは知りませんでした。 (まだ、SQL初めて2週間なので…) SQLiteで無事に動きました。とても助かりました。 12 名前：NAME IS NULL mailto:sage [2008/10/11(土) 08:05:59 ID:???] だれか SQLServerとOracleでの 文字列→日付 日付→文字列 をフォーマット付きで指定できる奴の テンプレ作っておいてくれ 俺が帰ってくるまでにｗ なかったら俺が探して書くｗ 13 名前：NAME IS NULL mailto:sage [2008/10/11(土) 15:19:19 ID:???] PCとActiveSync接続中のネット接続ON/OFF切り替えするメニューどこでしたか？ 14 名前：NAME IS NULL mailto:sage [2008/10/11(土) 15:19:51 ID:???] 誤爆しました。

---

---

[ 新着レスの取得/表示 (agate) ] / [ 携帯版 ]

read.cgi ver5.27 [feat.BBS2 +1.6] / e.0.2 (02/09/03) / eucaly.net products.
担当:undef