09/09/09 19:31:36
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 8問目
ｽﾚﾘﾝｸ(db板)
2:NAME IS NULL
09/09/09 19:32:51
SQL言語リファレンス一覧
Oracle Database
URLﾘﾝｸ(otndnld.oracle.co.jp)
Microsoft SQL Server
URLﾘﾝｸ(msdn.microsoft.com)
IBM DB2 Database
URLﾘﾝｸ(publib.boulder.ibm.com) （目次から参照情報→SQL）
PostgreSQL
URLﾘﾝｸ(www.postgresql.jp)
MySQL
URLﾘﾝｸ(dev.mysql.com)
参考リンク
URLﾘﾝｸ(sql.main.jp)
URLﾘﾝｸ(www.atmarkit.co.jp)
URLﾘﾝｸ(oraclesqlpuzzle.hp.infoseek.co.jp)
URLﾘﾝｸ(www.techscore.com)
3:NAME IS NULL
09/09/09 19:33:55
過去スレ
SQL質疑応答スレ 8問目
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ 7問目
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ 6問目
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ 5問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 4問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 3問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ 2問目
ｽﾚﾘﾝｸ(db板)
【帰ってきた】SQL質疑応答スレ
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ Part 2
ｽﾚﾘﾝｸ(db板)
SQL質疑応答スレ
ｽﾚﾘﾝｸ(db板)
4:NAME IS NULL
09/09/09 19:35:02
よくある質問１
（問）
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
09/09/09 19:36:09
よくある質問２
（問）
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
09/09/09 19:37:35
よくある質問３
（問）
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によって文法がかなり違うので注意
7:1
09/09/09 19:40:02
なんか落ちてたんで立てた
後悔はしていない
8:NAME IS NULL
09/09/09 20:00:55
>>1
乙
9:NAME IS NULL
09/09/10 06:37:53
おつ
10:NAME IS NULL
09/09/10 20:13:02
SQL の SELECT で取得する個数を指定することはできますか？
例えば、ソートした結果の100番目から150番目を個取得するなどです。
11:10
09/09/10 20:14:00
「個数を指定する」というよりも、「範囲を指定する」の方が言葉として適切でした _o_
12:NAME IS NULL
09/09/10 20:17:00
>>11
どっちもできるよ
13:10
09/09/10 20:24:15
>>12
■件数指定 LIMIT
・先頭から 3 件問い合わせるには、次のようにする。
access の top と同じことができる。
(TOP 10 取得、表示行制限、レコード数限定、件数指定、表示件数)
(1 レコード取得、1 件取得)
select * from testm
order by key1
limit 0, 3
;
みたいな感じでしょうか？
「access の top と同じことができる。」の意味が解らないのですが。。
14:NAME IS NULL
09/09/10 20:29:43
count(1)とcount(*)の違いを教えていただけないでしょうか？
15:NAME IS NULL
09/09/10 21:21:19 91yFw6iE
お願いします。
テーブル１(t1)には
[ID][受け渡し区分][客着日]フィールドがあります。
主キー 数値 日付型です
テーブル２(t2)には
[ID][データ内容][受け渡し日]フィールドがあります。
主キー テキスト 日付型です。
主キーで結合し、[データ内容][受け渡し日][客着日]の選択クエリを作ろうとしています。
[受け渡し日]の日付に[受け渡し区分]に入った数値を加算した日付を、
[客着日]に入るようなイメージなのですが、
どうもうまく作れません。
分かりにくいかもしれませんが、どうぞよろしくお願いします。。
16:NAME IS NULL
09/09/10 21:46:29
>>14
その違いは、使ってるデータベスのマニュアルに書いてあるはずだから、
マニュアル読んでくるといいよ
17:NAME IS NULL
09/09/10 21:52:43
>>15
よう分からんが、こうか？
select
t2.データ内容, t2.受け渡し日, t1.客着日
from テーブル1 t1
join テーブル2 t2 on t1.id = t2.id
where
t1.客着日 = t1.受け渡し区分 + t2.受け渡し日
あと、使ってるDBとバージョンは提示した方がいいぞ
18:NAME IS NULL
09/09/10 22:15:32 91yFw6iE
>>17
すいません。
access2000とかいう化石みたいなもの使ってます。
大まかにご提示いただいたようなテーブルを作成したのですが、
select t2.データ内容, t2.受け渡し日, t1.客着日
from t1,t2
join t2 on t1.id = t2.id
where t1.客着日 = t2.受け渡し区分 + t2.受け渡し日
where指定のあたりが間違ってるとのことなのです・・。
19:NAME IS NULL
09/09/10 22:16:37
>>16
わかりませんでした、ごめんなさい
20:NAME IS NULL
09/09/10 22:19:12
日付演算の方法がわからんというオチだったりしてなw
21:NAME IS NULL
09/09/10 22:54:35
>>19
Oracle
URLﾘﾝｸ(otndnld.oracle.co.jp)
MySQL
URLﾘﾝｸ(dev.mysql.com)
PostgreSQL
URLﾘﾝｸ(www.postgresql.jp)
firebird
URLﾘﾝｸ(firebird.skr.jp)
SQL Server
URLﾘﾝｸ(msdn.microsoft.com)
DB2
URLﾘﾝｸ(publib.boulder.ibm.com)
好きなの嫁
言ってることは、どのドキュメントも同じだから
22:NAME IS NULL
09/09/10 23:05:29
>>21
親切にありがとうございました
結論として、まったく変わらないということですね
勉強になりました
23:NAME IS NULL
09/09/10 23:24:41
だめだこりゃ・・・
24:NAME IS NULL
09/09/10 23:42:45
>>22の来世に幸あれ
25:NAME IS NULL
09/09/11 06:12:59
おまえら自信なさげに不親切な回答してるけど
count(1)とcount(*)の結果は同じだぜ？
26:NAME IS NULL
09/09/13 14:43:18 BgFEKAE5
Oracle10gでスキーマ内で更新されたレコードを抽出することはできますでしょうか？
判別としては各テーブルにUPDDATE項目があります。
DBA_TAB_COLUMNSあたりを使用して抽出できないでしょうか？
27:NAME IS NULL
09/09/13 16:06:40 olkIEqmS
お願いします。
access2003です。
あるテーブルに日付フィールドがあり、
そのフィールドの日付が土日であれば、
翌営業日(月)の日付を返してくれるクエリを作成したいと思っています。
どうぞよろしくお願いします。
とゆうか、sql単体では出来ないのでしょうか
28:NAME IS NULL
09/09/13 18:49:28
SELECT Switch(Weekday(日付)=1, Dateadd("d", 1, 日付), Weekday(日付)=7, Dateadd("d", 2, 日付)) AS 営業日
FROM あるテーブル AS T1
WHERE T1.日付 = (SELECT Max(T2.日付) FROM あるテーブル AS T2);
最新日が土日なら翌月曜日に
でも確認してないから全然自信ないわ…
vba使ったほうが絶対良いよ
29:NAME IS NULL
09/09/13 20:16:58
>>26
LogMinerを使うか、FlashBack Queryでもどうにかできるかも
まあトリガーをあらかじめ仕掛けておくのが常道だけど
30:NAME IS NULL
09/09/13 20:28:08 olkIEqmS
ありがとうございます。
UPDATE T1 SET [土日後]=[加算前]+2 WHERE WEEKDAY(加算前)=1;
これで、Ｔ１テーブルの、
加算前フィールドの日付が日曜の場合、
＋土日後フィールドに2日を加算した値を入れるようには出来ました。
ここから、スウィッチを文に含めると構文エラーと怒られるんですが、
何かケアレスしてそうな所ございませんか・・・
vbaは読みづらい書きづらいで、sqlで済ませたいんです。。
31:NAME IS NULL
09/09/13 20:51:49 mrtjO9XU
>>29
既存のシステムの保守にあたり、テストを行いたいのですが、
機能を処理してメインとなるテーブルはわかるのですが、その他にもないか
判別したいのです。
トリガーとなるとすべてのテーブルに仕掛けておかないといけないですよね
それしか方法はないですか・・・
32:NAME IS NULL
09/09/13 21:01:45
>>31
じゃあやっぱLogMinerで掘るしかないんじゃね？
33:NAME IS NULL
09/09/13 21:04:26
>>30
無理して１SQLでやらなくても、土曜の場合＋２するUPDATE文と
日曜の場合＋１するUPDATE文２回実行すればいいんじゃないかね
余計なお世話かもしれないが
お前のとこの営業日は、祝祭日も年末年始も考慮しないのか
34:NAME IS NULL
09/09/13 21:45:34
>>30
UPDATE文だったのか…Accessだと確か更新クエリでサブクエリ使えなかったような
構文にも自信ないからこっちに行った方がいいかも
Access総合相談所 其の22
ｽﾚﾘﾝｸ(bsoft板)
35:NAME IS NULL
09/09/14 14:44:11
注意: 他のSQLデータベース管理システムでの作業に親しんだユーザは、count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。
SELECT count(*) FROM sometable;のような問い合わせはテーブル全体を逐次スキャンを用いてPostgreSQLにより実行されます。
うわ、今まさに失望した。
36:NAME IS NULL
09/09/14 14:46:41
ちなみにpostgresqlのdistinct速くなったらしいね、というのはもう古い情報だろうか
37:NAME IS NULL
09/09/15 19:01:50 nAU+9E+9
複数のテーブルから同条件で抽出したレコードのカウントを取得したいのですが、
スマートな方法はありませんか？
テーブルをUNION ALLで連結し、
countした結果をさらに合計する方法を考えたんですが、
他にいい方法はありますか？
例）
select count as (cnt) from ta union all select count as (cnt) from tb
これの結果をさらに合計↑
38:NAME IS NULL
09/09/15 19:15:01
UNION ALLしてからcountすればいいじゃない
39:37
09/09/15 22:23:28 nAU+9E+9
>>38
すみません、よかったら具体例書いていただけませんか？
40:NAME IS NULL
09/09/15 22:31:25
select count(*) from (select * from ta union all select * from tb) where 条件
ということだったんじゃないの？
41:37
09/09/15 23:53:26 nAU+9E+9
サブクエリーですね。
試してみます。ありがとうございました！
42:NAME IS NULL
09/09/17 14:32:22
今PHPを学習していて、SQLite使ってますが、やはりMySQLの方がいいのでしょうか？
ユーザ管理機能などの点でSQLiteは駄目だとか読みました
しかし何か、SQLiteの方がPHP本体では推奨しているとか、MySQLを禁止しているところもあるとか聞いたりしたものでどうなのかなぁ、と
それでも学習するという意味でも、実用的という意味でもMySQLの方がいいのかなぁと思い始めてきたのですが、どういう違いがあるのでしょうか？
43:NAME IS NULL
09/09/17 14:51:03
コンパクトで、別途サービスなりデーモンなりを設定しなくていいのがSQLiteの優位性かなあと
44:37
09/09/17 16:47:58 k//XEl+s
37ですが、残念なことにMySQLではサブクエリーが使用できませんでした。
37のはなんとか他の方法でいけたのですが、もう一つ質問があります。
複数のテーブルから同条件で抽出した結果セットの並び替えを行いたいのですが、
サブクエリーを使用せず並び替える方法はありませんか？
select * from ta union all select * from tb order by 条件
とするとテーブルtbのみ並び替えが行われると思うのですが、
taとtbテーブルを合わせた結果セットの並び替えを行いたいのです。
どうすればいいのでしょう？
45:NAME IS NULL
09/09/17 17:15:04
>select * from ta union all select * from tb order by 条件
>とするとテーブルtbのみ並び替えが行われると思うのですが、
思うだけではなく、実行してみれば？
実行環境がないのかな？
46:37
09/09/17 17:18:33 k//XEl+s
すみません、whereが各select個々に記述しないとだめだったので、
orderも個々じゃないと駄目なのかなと。
確かに推測ではなく確認を取るべきですね。
動作させて出直してきます。
47:NAME IS NULL
09/09/17 19:27:07
>>44
> MySQLではサブクエリーが使用できませんでした
半端な情報を流すのはやめた方がいいです。
同じような言い方をするならば、MySQLはサブクエリをサポートしています。
48:NAME IS NULL
09/09/17 20:00:27
× MySQLはサブクエリをサポートしています
○ MySQLはバージョン4.1からサブクエリをサポートしています
49:NAME IS NULL
09/09/17 20:25:33
両方にorder byかけたっけ？
と思いSQL Server2008で試したけど無理だった。
unionの外でかけるしかないね
50:NAME IS NULL
09/09/17 20:29:32
order byの評価順は一番最後
unionよりも後
51:NAME IS NULL
09/09/18 15:36:31
複数店舗の定休日を管理するテーブル設計はどうしたらいいでしょうか。
定休日は「毎週土日、第三木曜日、祝日、毎月15日のみ」などいろいろあります。
また、どの店が定休日かを抽出する必要があります。
( 来週の金曜日が定休日でなく、かつ予約がない店など )
一番先に思いついたのは例えば「毎週土曜日」というデータを持ち、そこから今月であれば 9/5, 9/12, 9/19, 9/26 という日付データを持とうかと考えましたが、来月や年末を見ることを考えると、データ量が膨大になります(^^;。
お知恵を貸してください！
52:NAME IS NULL
09/09/18 17:07:13
テーブル設計はすれ違いな気もするが
日付、店舗ごとに休みの日もてばいいんじゃね
１店舗最大３６６件／年だぜ。それで膨大ってことはないだろ
53:NAME IS NULL
09/09/18 17:27:07
で、バッチでレコード作ればいいよねきっと。
54:51
09/09/18 17:43:05
>>52-53
すれ違い気味にもかかわらず、ありがとうございます。
# どこがいいかと探してみて「何故データベース設計は軽視されるのか」があったのですが、
# ここもちょっと違う感じです。相応しい板に誘導していただければ移動します＞＜
確かに言われてみれば、366×年数×店舗の件数なので、そんなに膨大ではないですね。
下記のように持ち方でいいのでしょうか。
ShopID|Holiday
------+-------
1|2009/1/1
1|2009/1/3
2|2009/9/18
2|2009/9/19
3|2000/1/1
この場合、2009/1/1 が休みじゃない ShopID の抽出 ( 2 と 3 ) の SQL はどのようになるでしょうか。
55:NAME IS NULL
09/09/18 17:58:54
select * from ショップマスタ where not exists (
select * from 休日マスタ where 休日マスタ.ShopID = ショップマスタ.ShopID and Holiday = '2009/1/1');
こんな感じで取れない？
56:51
09/09/18 18:07:45
>>55
ありがとうございます！ばっちりです。
先の話ですが、ショップマスタが 1000 件、
休日マスタが 10 万件の場合、
ShopID と Holyday へのインデックス適用以外に
パフォーマンスで考えることはなんでしょうか。
SQL もこのままでいけるものでしょうか。
57:NAME IS NULL
09/09/18 18:28:38
速度はある程度出ると思うけど、一応実測してみてね
58:NAME IS NULL
09/09/18 18:33:41
>>57
そうですね。ありがとうございます。
0.3s くらい出ているので問題なさそうです。
59:NAME IS NULL
09/09/18 19:00:35
オプティマイザとかによるんだが、(Holiday,ShopID)で主キーにするなら
select * from ショップマスタ where ShopID not in
(select ShopID from 休日マスタ where Holiday = '2009/1/1')
のが速い気がする
60:NAME IS NULL
09/09/18 20:29:55
>>59
ああ、やっぱりいろいろあるのですね。
ありがとうございます。
PostgreSQL 8.3 を考えています。
データ件数を 100 万件など増やして試してみます。
61:NAME IS NULL
09/09/19 15:47:13
inとexistsでの速度計測したらはってほしいな。
62:NAME IS NULL
09/09/20 08:41:22 OTmXdOaQ
このスレの存在をしらずに、別のスレで同じ書き込みをしました。
すみません。
質問は、、、
Mampを使って、php、mysqlとつないで簡単な掲示板を作ったのですが、
文字化けだらけです。utf-8に設定してるのですが、、
どこが問題なのでしょうか。
この問題を解決された方、アドバイスをよろしくお願いします。
63:NAME IS NULL
09/09/20 10:47:07
>どこが問題なのでしょうか。
macじゃね？
64:NAME IS NULL
09/09/20 23:22:18
>>61
ちなみにSQL Server2008だとどっちも全く同じ実行計画だった
65:NAME IS NULL
09/09/21 03:59:08
データ量は？
66:NAME IS NULL
09/09/23 00:06:44
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32))
というので、usernameにPRIMARY KEYを設定したいのですが、
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32) PRIMARY KEY(username))
のように設定すればいいのでしょうか？
主キーの設定の仕方が分からないもので教えていただけたらと思います
67:NAME IS NULL
09/09/23 01:22:34
>>66
使ってるDB書いてくれないと答えられないよ！！！！
68:66
09/09/23 13:43:47 1c6MbXvq
すみませんでした
リレーショナルデータベースで、抽象化レイヤはSQLiteです
PEAR::Authを利用前にユーザ情報テーブルを定義しようと思ったのです
69:NAME IS NULL
09/09/23 18:57:27
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32),PRIMARY KEY(username))
でいけるんじゃない
70:NAME IS NULL
09/09/24 01:17:28
SQL Server 2005＋Management Studioを使用しています。
1列目 2列目 3列目
0 2
3 2
0 4
1 5
1，2列目を使って計算し、3列目に結果を格納したビューを作りたいと考えています。
・1列目が0以外の時は1,2列目を使って計算して結果を3列目に格納し、
・1列目が0の時は計算しない(3列目はNULL値を格納)。
のような場合を考えているのですが、どのような方法があるでしょうか？
ご教授よろしくお願いします。
71:NAME IS NULL
09/09/24 01:27:33
CASE でできるだろ。
72:NAME IS NULL
09/09/24 02:00:53
select
a.1列目,
a.2列目,
case when a.1列目<>0 then null
else a.1列目+a..列目 end as 3列目
table a
73:70
09/09/24 02:21:50
>71,72
さっそくやってみたらできました！本当にありがとうございます！！
74:NAME IS NULL
09/09/24 13:15:44 Z8PuTcTT
MYSQL5使用してます。
テーブル
id code date
1 abc 2009-09-01 00:00:00
2 def ...
3 abc ...
4 abc ...
・欲しい結果
１日範囲で個別コードごとの総カウント数(COUNT)を取得し返したい。
・説明
アクセス集計していますが、コードがバラバラのため
個別にどれだけ該当コードでアクセスされたのか集計したいのです。
SQL文２回なら出来ますが、出来れば１度にやりたいのです。
よろしくお願いします。
75:51
09/09/24 13:49:14
>>61
計測してみました。
PostgreSQL 8.3.7 ( Xeon 1.86GHz x 2、 メモリ 2G ) の環境で
shop 10 万件、holiday 1000 万件で試しました。
shop.shop_id, holiday.shop_id, holiday.holiday に INDEX 張ってます。
5 回くらい explain analyze した結果。
■ IN
select count(shop.shop_id) from shop where shop_id not in (
select shop.shop_id from holiday,shop where shop.shop_id=holiday.shop_id and holiday.holiday = '2009/1/1');
→270ms
■ exsits
select count(s.*) from shop s where not exists (
select s.* from holiday where s.shop_id = holiday.shop_id and holiday.holiday = '2009/1/1');
→cost が IN の 900 倍あって計測中止
76:NAME IS NULL
09/09/24 15:10:15
select s.*
→
select *
でもおなじかの
77:51
09/09/24 15:27:56
>>76
同じでしたのう。
何か根本的に間違えているのかな。
78:NAME IS NULL
09/09/24 21:43:03
>>77
なんでnot inのサブクエリーを改悪する
つかそれでもnot inが900倍も速いのかｗ
existsのサブクエリのs.*を普通に*とかにしてもホントに遅いままか？
holiday.*とかholiday.shop_idとか1とかにしても遅いままか？
79:NAME IS NULL
09/09/24 22:24:43
最近のPostgreSQLでは (NOT) IN は速いよ。
でも900倍は変。
holiday.shop_id や holiday.holidayにインデックス貼ってみたらマシになるかも。
80:NAME IS NULL
09/09/24 22:28:18
holiday(shop_id,holiday)という複合INDEXがなければnot existsはそんなに
パフォーマンスが出ないというのは想像がつくが、この例ではholiday.shop_idの
holiday.holidayのどっちのINDEXが使われたのかねぇ。
それと、'2009/01/01'を休日に持つshopは10万件のうち何割だったんだろう？
普通に考えて、前者のnot inはshopとholidayのfull scanを1回ずつ、後者の
not existsはshopのfull scanを1回*nested loop joinだから、両方のテーブルが
メモリに収まらないくらい十分大きく、nested loop joinがindex scanできる
条件でならば通常後者のnot existsの方が速いはずだけど。
どっちにしても、せっかくexplainしても実行計画見ないんじゃ何もわからないね。
81:NAME IS NULL
09/09/24 22:41:13
>>80
テーブルの構成や行数にもよるが、サブクエリでユニークインデクスで検索する場合でも
EXISTS (インデックススキャンをメインテーブル行数回)
より
IN (両テーブルを各1回フルスキャン)
の方が速い場合もある。(つか、あった)
82:NAME IS NULL
09/09/24 23:01:00
どっちか一方が常に有利なんてことはないんだから、それは別に不思議じゃないと思うが。
83:NAME IS NULL
09/09/24 23:20:57
>>80
おれはPostgreSQL詳しくないので、特有な癖とかあったらわからんが
もともとnot inは、
>(Holiday,ShopID)で主キーにするなら
って前提だぜ。つまり(Holiday,ShopID)で複合インデックスがあれば
holidayテーブルのフルスキャン必要なくなるはずだ
84:81
09/09/24 23:31:01
すまん、ちょっと脊髄反射した...orz
85:NAME IS NULL
09/09/24 23:45:40
>>83
たしかに、holidayが十分選択性が高ければそこでフルスキャンはかかんないね。
その場合shop_idとの複合にする意味はあんまないと思うけど。
86:NAME IS NULL
09/09/25 00:31:30
>>85
カバードインデックスってPostgreSQLじゃ有効じゃないのか？
87:NAME IS NULL
09/09/25 04:37:07
not in と not existsだと調査するまでもなく後者の方が速いと思いこんでいた。
勉強が足りないなぁ俺。。。
88:51
09/09/25 10:31:33
みなさん、いろいろとアドバイスありがとうございます。
>>78
遅いっすね。
>>80
複合 INDEX を張ったら 1/30 になりました！
それでも IN に比べると 30 倍遅いです。
「'2009/01/01'を休日に持つshopは10万件」中 15% です。
explain 結果は分けてポストします。
89:NAME IS NULL
09/09/25 10:32:25
■ 複合 INDEX 前の exists
# explain select count(*) from shop s where not exists (
select 1 from holiday where s.shop_id = holiday.shop_id and holiday.holiday = '2009/1/1');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=28065024.77..28065024.78 rows=1 width=0)
-> Seq Scan on shop s (cost=0.00..28064899.76 rows=50003 width=0)
Filter: (NOT (subplan))
SubPlan
-> Bitmap Heap Scan on holiday (cost=276.60..280.62 rows=1 width=0)
Recheck Cond: (($0 = shop_id) AND (holiday = '2009-01-01'::date))
-> BitmapAnd (cost=276.60..276.60 rows=1 width=0)
-> Bitmap Index Scan on index_holiday_shop_id (cost=0.00..5.88 rows=71 width=0)
Index Cond: ($0 = shop_id)
-> Bitmap Index Scan on index_holiday_holiday (cost=0.00..270.47 rows=14550 width=0)
Index Cond: (holiday = '2009-01-01'::date)
90:NAME IS NULL
09/09/25 10:33:08
■ 複数 INDEX 後の exists
( vacuum full analyze してます )
# explain select count(*) from shop s where not exists (
select 1 from holiday where s.shop_id = holiday.shop_id and holiday.holiday = '2009/1/1');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Aggregate (cost=938409.27..938409.28 rows=1 width=0)
-> Seq Scan on shop s (cost=0.00..938284.26 rows=50003 width=0)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using index_holiday_shop_id_holiday on holiday (cost=0.00..9.37 rows=1 width=0)
Index Cond: (($0 = shop_id) AND (holiday = '2009-01-01'::date))
91:NAME IS NULL
09/09/25 10:34:18
■ in
# explain select count(shop.shop_id) from shop where shop_id not in (
select shop.shop_id from holiday,shop where shop.shop_id=holiday.shop_id and holiday.holiday = '2009/1/1');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=36525.88..36525.89 rows=1 width=4)
-> Seq Scan on shop (cost=34610.79..36400.87 rows=50003 width=4)
Filter: (NOT (hashed subplan))
SubPlan
-> Hash Join (cost=3459.70..34574.27 rows=14609 width=4)
Hash Cond: (holiday.shop_id = public.shop.shop_id)
-> Bitmap Heap Scan on holiday (cost=278.57..30593.96 rows=14609 width=4)
Recheck Cond: (holiday = '2009-01-01'::date)
-> Bitmap Index Scan on index_holiday_holiday (cost=0.00..274.91 rows=14609 width=0)
Index Cond: (holiday = '2009-01-01'::date)
-> Hash (cost=1540.06..1540.06 rows=100006 width=4)
-> Seq Scan on shop (cost=0.00..1540.06 rows=100006 width=4)
92:51
09/09/25 10:38:00
5 回くらい実行した explain analyze 結果。
in → 268ms
exists → 780ms
INDEX は下記。
・shop(shop_id)
・holiday(holiday)
・holiday(shop_id)
・holiday(shop_id, holiday)
こんな感じです。複合 INDEX をすっかり忘れていました。
やはり in がずっと成績がいいですね。
93:NAME IS NULL
09/09/25 10:55:28
>>92
だから、なんでnot inのサブクエリにshopテーブルが必要なんだ？
わざわざ改悪して速度調査ってなんなの？馬鹿なの？本気で馬鹿なの？
なんでおそらく最速になるであろう方法をためさないの？
ちゃんとnot inのサブクエリ直してholiday(holiday,shop_id) に
インデックはった上で実行した結果がみたいぞ
94:62
09/09/26 07:00:08 JyUvDari
>>62
６２ですが、解決しました。phpmyadminで確認したら、sjisで設定してありました。
utf-8に設定しなおして、文字化け、なくなりました。
初めての掲示板、ちっぽけな事ですが、私にとっては、ビッグニュースです。
mysqlとphp、これから猛勉強して、はまります。
95:NAME IS NULL
09/09/28 21:05:35
MySQL 5.1
週間日付テーブルがあります
week_tbl
w_sdate 週初日付
w_edate 週末日付
～
で、ある日付を元にそれが属している週のデータを知りたい。
例えばその日付が '2009-09-28'だとすると、
select w_sdate,w_edate from week_tbl
where w_sdate < '2009-09-28' and
'2009-09-28' < w_edate;
で良いのですが、この日付の部分を他のsqlで持って来よう
とする場合、どのように書けばいいのでしょうか？
96:NAME IS NULL
09/09/28 21:57:27
>>95
こういうこと？
select w_sdate, w_edate
from week_tbl, (select ほげほげ from ふがふが)
where w_sdate < ほげほげ
and ほげほげ < w_edate;
しかし週の初めと終わりを取りたいだけなら
select ほげほげ - interval dayofweek(ほげほげ) - 1 day,
ほげほげ + interval 7 - dayofweek(ほげほげ) day
from (select ほげほげ from ふがふが)
みたいな感じでいいような。
97:95
09/09/28 22:26:06
>>96
すんません、教えてください（ｱｾ
日付は
select max(in_date) from data_dailytbl
where code = 9999;
で出すmax(in_date)を元にしたいんですが、そのsqlにはどうあてはめれば
よろしいのでしょうか？
98:NAME IS NULL
09/09/29 00:11:39
これじゃいかんか？
select a.w_sdate,a.w_edate from week_tbl a,
(
select max(in_date) as maxdate from data_dailytbl
where code = 9999;
) as b
where a.w_sdate < b.maxdate and
b.maxdate < a.w_edate;
99:NAME IS NULL
09/09/29 06:47:26
>>98
9999;の";"を取り除いて動作しました。
ありがとうございました m(_ _)m
100:NAME IS NULL
09/10/01 08:33:30
失礼します
独習SQLのP67に書いてあるのですが、「WK_仕入先」テーブルに「仕入先テーブルの行を挿入する（テーブルのデータを別のテーブルにコピーする）」とあるのですが、
①CREATE TABLE WK_仕入先 (仕入先コード NUMBER(5), 仕入先名 CHAR(20) )
と
②CREATE TABLE WK_仕入れ先 AS SELECT * FROM 仕入先 WHERE 1 = 2
が同じとあります
それで分からない事があるのですが②の最後の部分、【WHERE 1 = 2】の部分の 1 = 2とは何を意味しているのでしょうか？
仕入価格 = 12000とかならば分かるのですが、1 = 2 というのは抽象的過ぎて何をやっているのかが分かりません
すみませんがご回答いただければと思います
101:NAME IS NULL
09/10/01 09:45:29
抽象的な訳じゃなくて、面食らっただけじゃないのかな
1と2は同じではないので、抽出結果は常に0件になる
ただ、WK_仕入れ先を作るために必要なカラム情報は取れる
102:NAME IS NULL
09/10/01 16:07:29
>>101
はい、面食らいました
未だに意味は分かっていないのですが、コレは特に意味の無い表記なのでしょうか？
ただ①の仕入先コードや仕入先名のようなカラム情報を得たいが為だけに記入されているのでしょうか？
この１や２が何を意味するのかも見当もつきません・・・
103:NAME IS NULL
09/10/01 16:46:36
１は数字の１、２は数字の２ そのままの意味
１と２を比べれば等しくないのでその条件は不成立となる
かならず不成立となる条件として書いてあるだけ
104:NAME IS NULL
09/10/01 17:21:45
>>103
説明有難うございました
105:NAME IS NULL
09/10/02 14:44:20 aRTkE45u
OracleとAccess（JetやSQL Server）はどちらがいいのでしょうか？
自分、まだ全然良く分かっていない初心者なのですが、最初からずっと使うであろう方を学んで使い続けたいと思っているのですが
本屋でパッと見たところSQLServerの本が多かったように思えますが、Oracleの方が有名な気がします
どのような違いがあって、どちらがいいのか教えていただけたらと思います
106:NAME IS NULL
09/10/02 15:00:56
>>105
どちらも体験版というか、ExpressEditionはある。ただ、SQLServerの方が判り易い（と俺は思う）
どちらを選ぶにしろ、解説本を読んで自分のPCで試してみたら？
OracleはOS毎にあるけど、SQLServerは当然のことながらWindows用しかない。
けど、今のレベルではそんなの関係ないだろ？
どちらで勉強しても基本的な所をしっかり押さえれば、大丈夫だと思うけどね。
107:NAME IS NULL
09/10/02 21:33:49
ほげ台帳
--------
ほげ名 テキスト
担当1コード 数値
担当2コード 数値
担当3コード 数値
担当者テーブル
----------
コード オートナンバー
氏名 テキスト
これを
----------
ほげ名
担当1氏名(ほげ台帳.担当1コード=担当者テーブル.コードになる担当者テーブル.氏名)
担当2氏名(ほげ台帳.担当2コード=担当者テーブル.コードになる担当者テーブル.氏名)
担当3氏名(ほげ台帳.担当3コード=担当者テーブル.コードになる担当者テーブル.氏名)
という形にするにはどうすれば良いでしょうか？
環境はAccessです。
108:NAME IS NULL
09/10/02 22:20:56
>>107
select T1.ほげ名,
T2.氏名 as 担当1氏名,
T3.氏名 as 担当2氏名,
T4.氏名 as 担当3氏名
from ほげ台帳 T1
inner join
担当者テーブル T2
on T1.担当1コード = T2.コード
inner join
担当者テーブル T3
on T1.担当2コード = T3.コード
inner join
担当者テーブル T4
on T1.担当3コード = T4.コード
Accessでどう書くかは知らない
109:NAME IS NULL
09/10/02 22:31:54 RteXPf/F
>>105
インストール後、使えるようになるまではSQL Server の方が少し楽な気がする
OracleXEは分かってる人が使うものって感じかなぁ>個人的な意見
Webのマニュアル(英語版)位しかないよ＞出た時ね
今本はあった気がするけど、内容は見たことがないので
＃保守契約ないと、パッチ手に入れられないから、業務には使いづらいし
＃SQLServerは、一応SPはでるからね
最初は「現場で使えるSQL 第2版」が個人的にお勧め(両方対応してるし)
＃10g、2005対応だけどね
Accessは悪くはないけど、最初に触るものとしては、薦めたくないな・・
最初にSQLをちゃんと使うことを覚えて欲しいと思うから
110:NAME IS NULL
09/10/02 22:49:08
.NET やるなら SQL Server で。
111:NAME IS NULL
09/10/03 01:12:59
SQL Serverはあまり使ったこと無いけど、ホスト言語も含めた開発環境で変な癖がつかないか?
MSにどっぷりはまるのなら別にいいけど、ここに書かれる質問で、SQL側とホスト言語側の
切り分けが出来ていない物がたまに見受けられる。
ま、問題の切り分けが出来ない人はMS絡みに限ったことじゃないし、SQL Serverも含め
MSに対して他意はない。
112:NAME IS NULL
09/10/03 01:46:14
.NETとMSSQLが相性いいのは間違いないだろ。
ORMapperにLINQtoSQL使ってみなさいよ。
113:NAME IS NULL
09/10/03 03:07:05
切り分けができてないだけなのか、がんばればSQLで解決できるのかもしれないと思っているのか
その判断が俺にはできないわ。
114:NAME IS NULL
09/10/03 07:49:05 jYp+tLjS
>>111
>ホスト言語も含めた開発環境で変な癖がつかないか?
あまり無いと思うよ>普段はSQL鯖使い
Linuxから、アクセスするときはFreeTDS使うけど、後はあまり無いんじゃね？
Oracleと比較しても、そう変わらん気がする
＃pl/sqlと比べてストアドがかなり弱い気がするけど
パフォチュー関係の情報があまりでてきてないくらいかな
115:111
09/10/03 09:52:16
相性がいいから癖がついちゃうとか。
SQLの中に割り当てるホスト言語の変数を埋め込んだままとか、
ホスト言語の一文そのままここに晒されても、とかって思うときがあった。
俺の偏見かな。
一応俺も.NET(C#)使うけど、LINQはまだだし、.NET+DBって機会がろくにないな。
やりたいんだけど、誰か仕事クレw
116:NAME IS NULL
09/10/03 18:04:22
>>108
Accessではこんな感じで出来ました、ありがとうございます
見かけのフィールド名(担当1～3氏名)については、プロパティで設定したためSQLにはありません
select ほげ台帳.ほげ名, T1.氏名, T2.氏名, T3.氏名
from ((ほげ台帳
inner join 担当者テーブル as T1 on ほげ台帳.担当1コード = T1.コード)
inner join 担当者テーブル as T2 on ほげ台帳.担当2コード = T2.コード)
inner join 担当者テーブル as T3 on ほげ台帳.担当3コード = T3.コード;
117:NAME IS NULL
09/10/03 20:47:59
116の追記ですが、最終的にはLEFT JOINになりました。
第二、第三担当がNULLの場合に対応するためです。
118:NAME IS NULL
09/10/03 21:41:20
＿＿＿
,r' `ヽ、
,i" ﾞ;
!.（●） （●）,!
ゝ_ _,r''
／ ;;;;;; ・・ ;;;;） ＜それは報告しなくてもいいです。
/ （＿
| f＼ ﾄｪｪｪｲﾉ ￣`丶.
| | ヽ＿＿ﾉー─-- ､_ ）
. ｜ | / /
｜ ｜ ,' /
／ ﾉ | ,'
/ ／ | /
_ノ ／ ,ﾉ 〈
( 〈 ヽ.__ ＼
ヽ.＿> ＼__)
119:NAME IS NULL
09/10/04 01:55:14
mysql 5.1.37
（問）
[groupテーブル]
group_id | group_name
---------+-----------
g1 | グループ１
g2 | グループ２
g3 | グループ３
[belongテーブル]
group_id | user_id | status
---------+---------+------
g1 | u1 | 1
g2 | u2 | 0
g2 | u3 | 1
g2 | u4 | 1
このようなテーブルから、下記のように
group_name | user_count
-----------+-----------
グループ１ | 1
グループ２ | 2
グループ３ | 0
各グループ毎の「belong.status = 1」の所属ユーザ数を取得したいです。
以下のようなsql文を考えてみましたが、
SELECT group.group_name as group_name, count(belong.user_id) as user_count
FROM group left join belong on group.group_id = belong.group_id
WHERE belong.status = '1'
GROUP BY belong.group_id
group_name | user_count
----------+-----------
グループ１ | 1
グループ２ | 2
と、グループ３に対してデータを取得することができませんでした...orz...
※外部結合している為、「belong.status = '1'」の部分で…
なにか良い方法はないでしょうか？
120:NAME IS NULL
09/10/04 01:57:26
グループ化した結果を、group テーブルに LEFT JOIN。
121:119
09/10/04 02:05:58
>> 120
レスありがとうございます。
アドバイスして頂いた内容ですが、「サブクエリを使って…」と
解釈しましたが、問題ないでしょうか？
できれば、サブクエリを使わない方法で取得したいのです。
122:119
09/10/04 02:39:28
>>120
アドバイスして頂いたことを元に、
SELECT
group.group_id,
group.group_name,
IFNULL(count_table.user_count, 0) as user_count
FROM
group left join
(SELECT group_id, count(*) as user_count FROM belong WHERE status='1' GROUP BY belong.group_id) as count_table
on group.group_id = count_table.group_id
と、sqlを発行することで、取得することができました。
やはり、サブクエリをつかってしか取得することはできないのでしょうか？
『サブクエリを使用すると、レスポンスが悪くなる』
と、聞いたことがあり、なるべくなら、使用したくありません。
※今回の場合、上記sqlを発行すると、都合、２回、sqlが発行されることになると思います。
123:NAME IS NULL
09/10/04 03:57:37
SELECT group.group_name as group_name, count(belong.user_id) as user_count
FROM group left join belong on group.group_id = belong.group_id
GROUP BY belong.group_id, belong.status
HAVING belong.status = '1'
124:NAME IS NULL
09/10/04 04:14:30
サブクエリなしのSQL（笑）
125:NAME IS NULL
09/10/04 05:14:07
SELECT group.group_name as group_name, sum(case when belong.status is null then 0 else 1 end) as user_count
FROM group left join belong on group.group_id = belong.group_id
GROUP BY belong.group_id, belong.status
HAVING belong.status = '1' or belong.status is null
グループ3を0人と出すならこうかな。
126:NAME IS NULL
09/10/04 07:02:54
>>123,125
アドバイスして頂き、ありがとうございます。
アドバイスして頂いた中にあった「case句」を使うことで取得することができました。
SELECT
group.group_id as group_id,
group.group_name as group_name,
CASE WHEN belong.status IS NULL THEN 0 ELSE COUNT(belong.user_id) END user_count
FROM
group left join belong on group.group_id = belong.group_id
WHERE
belong.status = '1' OR belong.status IS NULL
GROUP BY
group.group_id
>>123,125
少し気になったのですが、アドバイスして頂いたsql文で
>GROUP BY belong.group_id, belong.status
と、「belong.group_id」にて、GROUP BYされていますが、これは、「group.group_id」の
間違いではないでしょうか？
127:119
09/10/04 08:21:47
>>126のsqlでもダメでした。
【ダメな理由】
belongテーブルに「belong.status」が'0'のレコードのみ存在する場合、
対応するgroupテーブル中のレコードが表示されない。
[groupテーブル]
group_id | group_name
---------+-----------
g1 | グループ１
g2 | グループ２
g3 | グループ３
[belongテーブル]
group_id | user_id | status
---------+---------+------
g1 | u1 | 0
g2 | u2 | 0
g2 | u3 | 1
g2 | u4 | 1
[取得したい結果]
group_name | user_count
----------+-----------
グループ１ | 0
グループ２ | 2
[取得される結果]
group_name | user_count
----------+-----------
グループ２ | 2
もう少し、考えて見ます。m(_ _)m
128:NAME IS NULL
09/10/04 11:03:48
テーブルＡにdatetime型のカラムがあり、select結果として
date_split_by_year
--------------┤
y/m/d h:m:s 1 ｜
y/m/d h:m:s 2 ｜
y/m/d h:m:s 3 ｜
y/m/d h:m:s 4 ｜
y/m/d h:m:s 5 ｜
y/m/d h:m:s 6 ｜
y/m/d h:m:s 7 ｜
： ｜
： ｜
y/m/d h:m:s N ｜
y/m/d h:m:s 1 = テーブルＡの最小年月日（Minで取得？）
y/m/d h:m:s 2～N-1 = y/m/d h:m:s 1 に 1年ずつ加算していった年月日
y/m/d h:m:s N = 現在日時
という風に取得結果を取りたいのですが、どうすれば良いでしょうか？
おそらく問題は、y/m/d h:m:s 1とy/m/d h:m:s Nの値が常に変動するので
Nの数が決まっていないということです。
先生方よろしくお願いしますm(_ _)m
129:NAME IS NULL
09/10/04 11:05:02
ＤＢはSQL Server 2005 SP2 です
130:NAME IS NULL
09/10/04 11:14:57
もう少し具体的に。row_number() 使えばいいだけの話にも見える。
131:NAME IS NULL
09/10/04 11:21:25
テーブルに有る訳でもないレコードを取ってくるとか
自分ならわざわざSQLでやらないなぁ
132:NAME IS NULL
09/10/04 11:40:17
ありがとうございます。
具体的にご説明しますと、
①[テーブルＡ]
code_id | updt_time
------------+--------------------
code1 | 2003/01/01 00:00:00
code2 | 2004/07/01 12:30:00
code3 | 2005/07/01 22:00:00
現在日時 2009/10/04 11:20 にクエリ実行
[結果]
date_split_by_year
--------------------┤
2003/01/01 00:00:00 ｜
2004/01/01 00:00:00 ｜
2005/01/01 00:00:00 ｜
2006/01/01 00:00:00 ｜
2007/01/01 00:00:00 ｜
2008/01/01 00:00:00 ｜
2009/01/01 00:00:00 ｜
2009/10/04 11:20:00 ｜
---------------------
②6年後テーブルAに削除がかかっていたとして
[テーブルＡ]
code_id | updt_time
------------+--------------------
code2 | 2004/07/01 12:30:00
code3 | 2005/07/01 22:00:00
6年後 現在日時 2015/10/04 11:20 に再度クエリ実行
[結果]
date_split_by_year
--------------------┤
2004/07/01 12:30:00 ｜
2005/07/01 12:30:00 ｜
2006/07/01 12:30:00 ｜
2007/07/01 12:30:00 ｜
2008/07/01 12:30:00 ｜
2009/07/01 12:30:00 ｜
2010/07/01 12:30:00 ｜
2011/07/01 12:30:00 ｜
2012/07/01 12:30:00 ｜
2013/07/01 12:30:00 ｜
2014/07/01 12:30:00 ｜
2015/07/01 12:30:00 ｜
2015/10/04 11:20:00 ｜
---------------------
といった取得結果にしたいです。
>>131
素直にテーブルＡから最小日時をとって、コードで（開発言語はVB6.0です）
1年ずつ足して配列に格納とするほうが自然でしょうか。
意図としてはクエリ一発でできればやってしまおうー！と思ったしだいです。
ですが不自然なコードは今後のメンテナンスのためにも書くことは避けたいので、
そのやりかたは変だとあれば、この案はあきらめたいと思います。
133:NAME IS NULL
09/10/04 11:42:56
俺ならストアドにして終わり、だな。
134:NAME IS NULL
09/10/04 16:10:50
ストアドかアプリかな
135:NAME IS NULL
09/10/04 18:29:15
DBに存在しない値をSQLで生成するという考え方がおかしい
どうしてもクエリ１発でやりたかったらストアド書け
136:NAME IS NULL
09/10/04 23:05:08
SELECT A.風, A.林, B.火, B.山
FROM 南斗 A, 北斗 B
WHERE A.風 = B.風
AND A.風 IN ( SELECT C.風 FROM 北斗 C WHERE 山 >= 10000 )
すみません、このテーブル結合が分かりません
何故、「北斗(テーブル) B」があるのに、INの後に「北斗(テーブル) C」という記述があるのでしょうか？
同じ北斗テーブルなのにBというのとCと言う風に分けられていて、このテーブルBとテーブルCの違いは何なのでしょうか？
137:NAME IS NULL
09/10/04 23:14:14
書いた奴に聞けよ。
138:136
09/10/04 23:36:01
やはり書いた人間の意図によって分けられているだけなのですか・・・
では、すみませんがこの点を教えて下さい
上記のように北斗テーブルをBに設定しているのに、また別にCに設定すると言うことは出来るのでしょうか？
139:NAME IS NULL
09/10/04 23:37:03
できるよ。
140:NAME IS NULL
09/10/04 23:45:55
使い道はもちろんあるんだよ。
社員が給与振込口座を複数持つことが出来て、
銀行マスタをいくつも結合する場合、とか。
141:136
09/10/04 23:54:33
>>139
分かりました、有難うございます
>>140
具体的に例を挙げていただき有難うございます
で、今思いついたのですが、自分の考えとしては、>>136はおそらくBのまま使うと、山>=100000の条件が出てこないからかなと思いました。
BはAとの関連付けだけに設定し、CはBの中のC条件だけをAに付加すると言う意図で別に設定したのかな、と。
このような考えで宜しいのでしょうか？
142:NAME IS NULL
09/10/04 23:57:26
これってサブクエリー使う意味あんの？
SELECT A.風, A.林, B.火, B.山
FROM 南斗 A, 北斗 B
WHERE A.風 = B.風
AND B..山 >= 10000
でいいと思うんだけど。
143:NAME IS NULL
09/10/05 00:10:41
うん。それでいい。
みんないじわるして教えなかっただけで。
144:NAME IS NULL
09/10/05 01:09:50
常にそうとも言えないのではないだろうか。
145:NAME IS NULL
09/10/05 01:24:18
サブクエリで抽出してるのが風で、WHERE句の1つ目の条件が風なんだからこの場合は同じだよ。
146:NAME IS NULL
09/10/05 01:34:30
同じだねごめん
147:NAME IS NULL
09/10/05 03:36:21
普通に考えれば>>142で行けるだろうなぁ
セレクトリストに存在しない項目はWHERE条件に書けない、みたいな制約の
あるDBMSとか存在してるのかも
集約関数あるとGROUP BYに無い項目使うと怒られる、みたいな
もしくは、結合条件でNULL＝NULLを真とみなすDBMSとか
148:NAME IS NULL
09/10/06 09:04:06 1YwLzITZ
>集約関数あるとGROUP BYに無い項目使うと怒られる
仕様上はこれが正しい(らしい)のだが
MySQL以外にあるの？
149:NAME IS NULL
09/10/06 12:18:48
普通はNG
150:NAME IS NULL
09/10/06 12:34:29
誤読だな。
151:NAME IS NULL
09/10/08 11:06:45
すいません、教えてください。
・Oracle10gR2
・CREATE TABLE test (col_a varchar2(10);)
・PL/SQL記述の動的SQLで文字列の検索を正常に行いたい
・考えてみたSQL
declare
w_sql varchar2(1000);
w_wrk varchar2(10);
begin
w_wrk := 'aaa';
w_sql := 'update test set col_a = ''更新'' where col_a = :wrk';
execute immediate w_sql using w_wrk
end;
通常のSQLでSQLを書くと「update test set col_a = ''更新'' where col_a = 'aaa'」
質問内容：
col_aは文字列なので通常であれば右辺を'aaa'とするべきですが、
動的SQLでは上記のように書くことになる、もし'をつけたくて
w_sql := 'update test set col_a = ''更新'' where col_a = '':wrk''';
と書くと、バインドされなくなっている気がする。
ここはどう書くべきです？
ここを読んでみたけどよくわからない・・・。
URLﾘﾝｸ(www.shift-the-oracle.com)
152:NAME IS NULL
09/10/08 12:09:53
ストアド、PL/SQLはスレ違いじゃないかな
153:NAME IS NULL
09/10/08 12:22:28
スレチすいません。どこかわからず・・・誘導お願いします。
154:NAME IS NULL
09/10/08 13:03:42
>>153
ｽﾚﾘﾝｸ(db板)
155:NAME IS NULL
09/10/10 02:01:42 KRCSZ/Pg
SELECT
(SELECT DISTINCT 'X' FROM B WHERE EXISTS (SELECT 'X' FROM B WHERE A.ID = B.ID)
FROM A
SELECT
(SELECT MAX ('X') FROM B WHERE EXISTS (SELECT 'X' FROM B WHERE A.ID = B.ID)
FROM A
SELECT
(SELECT COUNT ('X') FROM B WHERE A.ID = B.ID)
FROM A
最も速いのはどれ？
156:NAME IS NULL
09/10/10 02:27:49
なにがしたいんだろう。
とりあえず実行計画みてみたら。
157:NAME IS NULL
09/10/11 09:38:51
MS SQLServerではupdate table set col1 = value from table A where A.col1 = value2のようにsetの後ろにfrom句が書けるけど、これって標準SQLなのかな？
他dbmsではどう？
158:NAME IS NULL
09/10/11 10:17:12
>>155
それ3つとも動くのか？
159:NAME IS NULL
09/10/11 11:27:12
>>157
標準SQLじゃない
他のDBMSで書けるかどうかは>>2
160:NAME IS NULL
09/10/11 19:06:57
>>159
ｔｈｘ。
161:NAME IS NULL
09/10/15 22:07:42
【質問テンプレ】
・DBMS名とバージョン
Microsoft Access 2000
・テーブルデータ
支店テーブル
ID | DATA
--+----------+-----
1 | 東京センター
2 | 大阪支店
3 | 名古屋△センター
4 | 福岡△博多△センター
※△は全角スペース
・やりたいこと
スペースを二つ以上含むデータの抽出
このテーブルに対して
SELECT * FROM 支店テーブル WHERE TRIM(DATA) LIKE '*△*△*';
というSQLを実行したところ
４番だけでなく３番のデータも抽出されてしまいました。
別の方法で目的は達成したのですが
なぜ、３番のデータも抽出されてしまったのか教えていただきたいです。
ちなみに
SELECT * FROM 支店テーブル WHERE TRIM(DATA) LIKE '*△';
でも同じ結果がでました。
よろしくお願いします。
162:NAME IS NULL
09/10/15 22:44:57
Trimして試してみて。
163:161
09/10/17 11:31:13
>>162
WHERE TRIM(DATA) LIKE '*△*△*'
としてるんですが、後はどこにいれるんでしょうか。
164:NAME IS NULL
09/10/17 11:57:37
ああ、もうしてたのか。
165:NAME IS NULL
09/10/17 18:52:23
スペースだからなのかな
'*屋*屋*'だと3は取れるの？
166:NAME IS NULL
09/10/17 19:40:03
想像するに全角と半角が区別されずに判定されてるのかもね。
replaceで置き換えるかなんかして工夫が必要かも。
167:161
09/10/18 09:22:12
>>165-166
Accessが会社にしかないので月曜日に試してみます。
168:NAME IS NULL
09/10/18 17:14:03
MicrosoftSQL Server2005 を使ってます。
とある鯖用のデータベースなのですが、手元にあるLevelList.sqlというスクリプトを使ってそれをaccountdbのテーブルにしろと言われました。
初心者なものでどうやるのかが全くわかりません。
どなたか教えていただけないでしょうか。
URLﾘﾝｸ(up3.viploader.net)
169:NAME IS NULL
09/10/18 17:19:51
>>168
スレ違い
170:NAME IS NULL
09/10/18 21:32:46
LevelList.sqlを実行すればいいだけじゃ？
171:NAME IS NULL
09/10/19 20:26:06 FLpLj6wo
おねがいします。
テーブルT1、T2があります。
T1には、[ID1] [ID2] [データ名]フィールドがあり、
T2には、[ID3] [日付]フィールドがあります。
ID1とID2の連結した値が、ID3に含まれていれば、日付とデータ名を抜き出すように
SELECT T1.データ名,T2.日付
From T1,T2
Where T1.ID1 + T1.ID2 = T2.ID3
としています。
ここにさらに、
日付が存在しない場合、
日付フィールドを空の状態で
全てのデータ名フィールドを表示させたいのですが、
どうすればよいのでしょうか。
出来るだけアクションクエリを利用せずに作成したいです。
172:NAME IS NULL
09/10/19 22:15:27
>>171
DB何かくらい書いたら？
でアクションクエリって何？
INSERTとかUPDATEのことか？
173:NAME IS NULL
09/10/19 22:18:55
失礼しました。
access2003を使っています。
はい、更新クエリや挿入クエリで複数のクエリを作ることを出来るだけ避けたいと思っています。
不一致クエリと選択クエリを結合させると実現は出来たのですが、
非常に効率が悪いとかんじていたので、他に手段がないかと考えています。
174:NAME IS NULL
09/10/19 22:34:52
外部結合すればいいだけのように思うけど、それをaccess2003でどう書くかは知らない
175:NAME IS NULL
09/10/19 22:49:36 FLpLj6wo
外部結合で実行は出来るのですが、全外部結合をアクセスがサポートしてないようで、
最低４つはクエリが必要ということなのです。
おとなしく４つ作るべきでしょうか・・
176:NAME IS NULL
09/10/19 22:55:01
何で4つ？日付が存在しないだけなら
T1 left join T2 on T1.ID1 + T1.ID2 = T2.ID3
where T2.ID3 is null
でいいじゃん
177:NAME IS NULL
09/10/19 22:55:36
>>171
とりあえず用語が曖昧でやりたいことがいまいち伝わらない。
具体例をあげて説明してみてはどうでしょ。
178:NAME IS NULL
09/10/19 23:06:36
説明に不自由ですいません。
明日一度見直した後、改めて整理したものを書きますね。
すいません、自分でもいまいちどうしたいか理解できていない気がします。
179:NAME IS NULL
09/10/19 23:10:06
ああ、全外部結合がしたいのか・・・（って>>171に書いてることとかなり違う気もするけど）
accessだったら確かにできないから右外部結合のクエリと左外部結合のクエリを
UNIONでくっつけるしかないと思うよ
180:161
09/10/19 23:18:25
>>165
とれないですねー
>>166
M＄だからってことで結論づけたほうがいいかもしれませんね（汗
みなさん、ありがとうございました。
181:NAME IS NULL
09/10/19 23:49:32
>>171
SELECT T1.データ名,T2.日付
From T1 LEFT JOIN T2 ON T1.ID1 + T1.ID2 = T2.ID3
でいいんじゃないの？
182:NAME IS NULL
09/10/20 18:17:47 NX+GWuNh
【質問テンプレ】
・DBMS名とバージョン
MySQL 5.x
・テーブルデータ
会員マスタテーブル
IDがユニークキーです。
ID | 登録日 | 退会日
--+----------+-----
1 | 2009-08-15 2009-09-10
2 | 2009-09-12 2009-10-10
3 | 2009-09-28 2006-01-01
4 | 2009-10-01 2006-01-01
5 | 2009-10-17 2006-01-01
・やりたいこと
現在月(CURRENT_DATE)までを対象にして、各月毎の登録者が何人いて
その月の登録者が現在月までの各月(当月を含む)で何人が退会したのか、そして何人が
退会していないで残存しているのか抽出したい。
※退会日の 2006-01-01は退会していない事を表しています
※現在月は10月とします。
・希望する結果
8月の登録数が1件、当月の退会が0件、一か月後の退会が1件、残存数が0件
9月の登録数が2件、当月の退会が0件、一か月後の退会が0件、残存数が1件
10月の登録数が2件、当月の退会が0件、一か月後の退会が0件、残存数が2件
色々と考えたのですがさっぱり回答が分からないです。
どなたか教えて頂けませんか。
よろしくお願いします！
183:NAME IS NULL
09/10/20 18:25:53
分けなさいな。
184:182
09/10/20 19:13:49
一発で取得するのが難しくてSQLを分けるにしても
せめて現在月までの各月に対して1回ずつ発行する
みたいな形にできないですかね？
細かく各月毎に対してばんばんSQLを発行したら課題でNG
にされました・・・
185:NAME IS NULL
09/10/20 19:34:28
select to_char(trunc(登録日, 'MONTH'), 'YYYY/MM') as 月,
count(*) as 登録,
count(case when trunc(登録日, 'MONTH') = trunc(退会日, 'MONTH') then 1 end) as 当月の退会,
count(case when trunc(登録日, 'MONTH') = trunc(add_months(退会日, -1), 'MONTH') then 1 end) as 一か月後の退会,
count(case when 退会日 = '2006-01-01' then 1 end) as 残存
from 会員マスタ
group by trunc(登録日, 'MONTH')
order by trunc(登録日, 'MONTH')
;
日付関数はDBMSによってだいぶ違うのでMySQLの場合は自分で調べて
186:NAME IS NULL
09/10/20 19:42:15
とりあえず分けて作って(考えて)から連結するとか。
MySQLのdate_trunc関数やinterval型ってどうやるんだっけ、
そこらヘンでこねてやれば一発(つってもサブクエリまみれ)でできるけど。
187:NAME IS NULL
09/10/20 19:49:47
一か月後の退会
これは何を指してるんだろう。
> その月の登録者が現在月までの各月(当月を含む)で何人が退会したのか
5月の登録者が6,7,8,9,10月で何人退会したか、という風に読めるので、これは
6月の登録者が7,8,9,10月それぞれの月で何人退会したか
7月の登録者が8,9,10月それぞれの月で何人退会したか
と出さないとダメ？
188:NAME IS NULL
09/10/20 20:40:59
>>187
それだと1SQLで無理じゃね？
まあそう読み取れるけど。
189:182
09/10/20 20:41:20
みなさんありがとうございます。
>>187
その通りです。
説明が下手くそですいません。
190:NAME IS NULL
09/10/21 09:04:15
質問です。
あるテーブルに登録日と更新日の列があります。
どちらか新しい日付だけ取り出す方法はありますか？
日付が両方ヌル値の場合は最後に表示したいです。
Web画面上に昇順、降順を選択して表示しようと考えています。
宜しくお願いします。
191:190
09/10/21 12:08:56
自己解決しました
192:182
09/10/21 12:52:23 ZbRjCu5t
んー色々と考えましたが無理でした・・・
せめて登録のあった月から現在月までの月数分SQLを発行
すればなんとかならないですかね？
5月の登録者が10人、5月に登録して5月に退会した人が5人、5月に登録して6月に退会した人が3人、
5月に登録した人の残存数が2人
↑このSQLを5月を基準として現在月まで発行する(現在月を10月とすると計6回発行)
書き込み多くて申し訳ありません。
193:NAME IS NULL
09/10/21 19:35:53
>>192
とりあえず月別の登録数、残存数と、その月の登録者の月別の退会数がわかれば良いならこんな感じか
select 月別入会.登録年,月別入会.登録月,月別入会.当月入会,残存数,月別退会.所属月数,月別退会.退会数 from
(select YEAR(登録日) as 登録年,MONTH(登録日) as 登録月,COUNT(*) as 当月入会,count( case when 退会日='2006-01-01' then 1 end ) as 残存数 from 会員マスタ group by YEAR(登録日),MONTH(登録日)) as 月別入会
left join
(select YEAR(登録日) as 登録年,MONTH(登録日) as 登録月,YEAR(退会日) as 退会年,MONTH(退会日) as 退会月,((YEAR(退会日)*12+MONTH(退会日))-(YEAR(登録日)*12+MONTH(登録日))) as 所属月数,COUNT(*) as 退会数
from 会員マスタ where 退会日<>'2006-01-01' group by YEAR(登録日),MONTH(登録日),YEAR(退会日),MONTH(退会日)) as 月別退会
on 月別入会.登録年=月別退会.登録年 and 月別入会.登録月= 月別退会.登録月
SQL Serverで作ったんで日付の扱いは適当になんとかしてくれ
入会者が一人もいなかったときの年月はしらん
月別入会と月別退会はViewなり別テーブルなりで作るべきだな
退会日付はなんで退会してないならNULLにしないんだ
これ、なんかの課題なんだとしたら、こんな設計する人間に課題出されたくはないな
194:NAME IS NULL
09/10/21 20:04:09
NULLにするぐらいなら退会日付テーブル作るべき
それは面倒なので、NULL不可にして、業務上あり得ない過去日を設定した
というのが関の山
195:182
09/10/21 20:31:11
>>193
ありがとうございます！mysql用に直します
そうです。課題なんですよ。
集計を便利にするために月別入会と月別退会は別テーブルで
作るのが本当の正解の用な気がしました。
課題とは関係ないですが会員マスタを元に月別入会者数と月別退会者数
を別テーブルに分けたデータ構造を考えてみたいと思います。
>>194
その通りだとおもいます。
196:NAME IS NULL
09/10/21 20:54:13
>>182
おまえら頭固いｗ
mssqlだけどこれでいいだろ。
登録期間0の人数がやめてない人数、
登録機関1の人数が登録後1ヶ月でやめた人数てなかんじでみてね。
select
t2.入会月,t2.登録期間,COUNT(t2.入会月)AS 人数
from
(
select
DATEPART(MONTH,t.Registdate) AS 入会月,
CASE WHEN DATEDIFF(DAY,t.RegistDate,t.ExitDate)>0
THEN DATEPART(MONTH,t.ExitDate) - DATEPART(MONTH,t.Registdate)
ELSE 0 END AS 登録期間
from SQL9_182 t
)t2
group by t2.入会月,t2.登録期間
197:NAME IS NULL
09/10/21 21:03:36
>>196
とりあえず年またがりが出来なさそう
198:NAME IS NULL
09/10/21 21:15:16
おおほんとだな。すっかりぬけてるｗ
まあやりたいことはわかるだろ。
199:NAME IS NULL
09/10/21 23:59:16
ID | 日付 | データ | 更新日
--+----------+-------+----------+
1 | 2008-02-23 | 0 | 2008-12-04 |
2 | 2008-10-21 | 112 | 2009-10-11 |
7 | 2008-12-11 | 10 | 2009-10-11 |
100| 2009-11-10 | 0 | 2009-10-10 |
日付が2008年のデータの大きい方から5件取得し
その5件で更新日が2009年でないものを取得したいのですが
mysqlではサブクリエでlimitが使えないようでエラーが出ます
1回で済ませたいのですがほかに方法はないでしょうか
例：(php5+mysql5)
SELECT *
FROM table1
WHERE id IN (
SELECT id
FROM table1 WHERE
更新日 >= '2008-01-01'
AND 更新日 <= '2008-12-31'
ORDER BY データ DESC limit 5
)
AND 更新日 < '2009-01-01'
200:NAME IS NULL
09/10/22 00:18:01
>>199
なんかサンプルデータが中途半端なんだが、
その例だと結局ID=1の行だけ抽出できたらいいの?
201:NAME IS NULL
09/10/22 00:40:19
>>200
はい
202:NAME IS NULL
09/10/22 02:05:47
>>199
select id, (
select count(*) from table1 as t2
where t2.data > t1.data
and 日付 >= '2008-01-01' and 日付 <= '2008-12-31'
) as rank
from table1 as t1
where rank < 5
and 更新日 < '2009-01-01'
mysql知らないけどこんな感じにできないかなー
203:NAME IS NULL
09/10/22 02:35:50
SELECT * FROM Table1 AS T1
WHERE (SELECT count(*) FROM Table1 WHERE 日付 BETWEEN '2008-01-01' AND '2008-12-31' AND T1.データ < データ) < 5
AND 更新日 < '2009-01-01';
204:199
09/10/22 12:53:58
>>202,203
ありがとうございました。
参考になりました
205:182
09/10/23 11:23:43 8hF/z7x3
課題の件ですがみなさんの意見を参考にしてテーブルを
分けてみました。
●入会月別の集計TBL
ID | 年月 | 入会数 |
--+----------+-------
1 | 2009-08 | 30 |
2 | 2009-09 | 20 |
3 | 2009-10 | 15 |
●入会月別の退会者集計TBL
ID | 入会月 | 退会月 |退会数
--+----------+-------+-----
1 | 2009-08 | 2009-08| 10
2 | 2009-08 | 2009-09| 15
3 | 2009-08 | 2009-09| 20
4 | 2009-09 | 2009-09| 5
5 | 2009-09 | 2009-10| 13
6 | 2009-10 | 2009-10| 3
これで8月に入会した人数と8月に入会した人が8月に何人
退会したか9月に何人退会したかを現在年月まで抽出するSQLを
取得したいのですが方法は無いでしょうか？
みなさんよろしくお願いします。
206:NAME IS NULL
09/10/23 12:12:31
>>205
何の冗談だよこれｗ
もう集計されちゃってるじゃんｗ
207:182
09/10/23 12:39:34
>>206
えっ？!
この二つのTBLを元にこんな結果を抽出するSQＬが知りたかった
んですけど変なことしてます？
●入会月別の集計TBL
ID | 年月 | 入会数 |
--+----------+-------
1 | 2009-08 | 30 |
2 | 2009-09 | 20 |
3 | 2009-10 | 15 |
●入会月別の退会者集計TBL
ID | 入会月 | 退会月 |退会数
--+----------+-------+-----
1 | 2009-08 | 2009-08| 10
2 | 2009-08 | 2009-09| 15
3 | 2009-08 | 2009-10| 5
4 | 2009-09 | 2009-09| 5
5 | 2009-09 | 2009-10| 13
6 | 2009-10 | 2009-10| 3
▲抽出したい結果
登録月 ｜入会数|退会数(当月)｜退会数(翌月)｜退会数(翌々翌月から現在月まで対象)
--------+------+------------+------------+----------------------------------
2009-08｜30 ｜10 ｜15 ｜5
2009-09｜20 ｜5 ｜13 |0
2009-10｜15 ｜3 ｜0 |0
208:NAME IS NULL
09/10/23 13:31:12
横列固定になったのかな？
「翌々月から現在まで」ってひっくるめるの？
209:NAME IS NULL
09/10/23 13:47:46
いや、横可変の仕様は変わってないんじゃないかな。
>>196のように行で考える風にしないとダメなのがわかってない。
210:182
09/10/23 13:57:06
>>208
翌々月から現在までの意味は5月に登録した人が6月以降から現在月
までに退会したかを対象とする意味です。
>>209
何か根本的に分かってないですかね？
頭が固いから横列固定の考えになっているのかもです。
抽出したい結果があっているのならば横列固定とかは問いません・・・
211:NAME IS NULL
09/10/23 14:10:58
今月抽出する結果と来月抽出する結果では列数が1つ増えるわけでしょ？
列可変は普通にやったら無理じゃないかな。
212:NAME IS NULL
09/10/23 15:07:41 NlrCHYmt
よくある質問1>>4に近いのですが、どうしても上手くいかないので質問させてください。
【質問テンプレ】
・DBMS名とバージョン：MySQL 5.1
・テーブルデータ
テーブルA
A_ID PRIMARY
DATA1
DATA2
DATA3
DATA4
テーブルB
B_ID PRIMARY AUTO_INCREMENT
A_ID
MESSAGE
DATE
・欲しい結果
テーブルAとテーブルBを結合し、以下のように表示させたいです。
A.A_ID (B.A_IDを参照し、テーブルBにあるMESSAGEを反映させたい)
A.DATA1
A.DATA2
A.DATA3
A.DATA4
B.MESSAGE (A.A_ID=B.B_IDと対応させ、NULLの場合はそのままNULLを表示、メッセージが存在する場合はDATEが最新の物のみ表示)
・説明
現在、LEFT JOINでテーブルAとテーブルBを結合した物を表示させ、
テーブルBのMESSAGEにメッセージがあろうとNULLであろうと結合したテーブルの結果をすべて表示し、
GROUP BY A.A_IDで、A.A_IDのエントリーは1つしか表示されないようにしているのですが、
この状態だと、一番古いMESSAGEが表示されてしまいます。
よろしくお願いします。
213:NAME IS NULL
09/10/23 15:44:05
mysqlの環境がないのでmssql2008
SELECT
A.A_ID,A.DATA1,A.DATA3,A.DATA4,D.DATE,D.MESSAGE
FROM
Table_A A LEFT JOIN
(SELECT B.A_ID, MAX(B.DATE)AS DATE FROM Table_B B GROUP BY B.A_ID) AS C
ON A.A_ID = C.A_ID
INNER JOIN Table_B D ON C.A_ID = D.A_ID AND C.DATE = D.DATE
エイリアエスのつけ方はてきとう。
Joinもきれいじゃないけど参考程度に。
214:NAME IS NULL
09/10/23 15:58:44 NlrCHYmt
>>213
ありがとうございます。
書かれたコードを参考にSQL文を入力しているのですが、Table_A A、Table_B Dとは何を指すのでしょうか？
215:NAME IS NULL
09/10/23 16:01:49
別名定義。エイリアス。
長いテーブル名なんかを短縮して書けると便利だと思わない？
216:NAME IS NULL
09/10/23 16:23:26 NlrCHYmt
>>215
ありがとうございます。意味が分かりました。
>>213のSQL文を実行したら、>>4と同じ結果を得る事ができたのですが
私の欲しい結果は以下のような状態なので、どのようにすれば良いのでしょうか？
具体的なテーブル
テーブルA
A_ID | DATA1...DATA4
--+-----------+
1 | datadatadata |
2 | datadatadata |
3 | datadatadata |
4 | datadatadata |
5 | datadatadata |
6 | datadatadata |
7 | datadatadata |
テーブルB
B_ID | A_ID | MESSAGE| DATE|
----+-----+---------+-----+
1 | 1 | OK | 10/19
2 | 1 | エラー有 | 10/21
3 | 2 | OK | 10/17
4 | 4 | OK | 10/18
5 | 4 | 謎です | 10/22
6 | 6 | OK | 10/14
7 | 5 | ムリポ | 9/9
8 | 6 | エラー有 | 10/20
9 | 5 | OK | 10/9
結果テーブル
A_ID | DATA1...DATA4 | MESSAGE
----+--------------+----------
1 |datadatadata | エラー有
2 |datadatadata | OK
3 |datadatadata | NULL
4 |datadatadata | 謎です
5 |datadatadata | OK
6 |datadatadata | エラー有
7 |datadatadata | NULL
よろしくお願いします。
217:NAME IS NULL
09/10/23 19:24:21
SELECT A.A_ID, A.DATA1, A.DATA3, A.DATA4, D.DATE, D.MESSAGE
FROM Table_A AS A LEFT OUTER JOIN
(SELECT A_ID, MAX(DATE) AS DATE
FROM Table_B AS B
GROUP BY A_ID) AS C ON A.A_ID = C.A_ID LEFT OUTER JOIN
Table_B AS D ON C.A_ID = D.A_ID AND C.DATE = D.DATE
これでいける。
最後のINNER JOINをLEFT JOINにかえる。
218:NAME IS NULL
09/10/24 06:24:50 kglbEO/C
>>217
ありがとうございました。
求めている結果になりました。
219:NAME IS NULL
09/10/26 13:34:17 GG3WejBx
SQL+VBSです。
データベースの更新を開いているプラウザでF5や更新じゃなくて
更新をトリガーとして自動的に画面に追加された情報を表示する方法はどんな方法がありますか？
なるべく簡単なほうがいいです。
220:NAME IS NULL
09/10/26 14:41:54
鼬害
221:NAME IS NULL
09/10/27 22:04:11
MySQL 5.1.34
id|date
--+-----
1 |10/25
--+-----
1 |10/26
--+-----
2 |10/25
--+-----
2 |10/26
SELECT * FROM table
WHERE id = 1 AND date
= (SELECT date FROM
table WHERE id = 1
ORDER BY date ASC
LIMIT 0,1);
id = 2だったり
order by が descだったりします。
このような自己相関サブクエリを用いて
データの取得を行っていました。
しかし同じサブクエリでDELETEは
MySQLの仕様上できないようで
一時テーブルを使わないとダメらしいのですが
どうにもうまく動きません。
アドバイスをお願いいたします
222:NAME IS NULL
09/10/27 23:24:39
>>221
delete from table
where id =1 and date =
(select max(t2.date) from table t2 where t2.id = id)
223:NAME IS NULL
09/10/30 18:57:55 iB0kBy0C
どうかよろしくお願いします。
MySQL 5.1
>>5と似ているのですが
data
-----
いちご
メロン
ぶどう
ぶどう
イチゴ
バナナ
いちご
アボガド
というようなテーブルの中から
data｜count
ーー＋ーーー
いちご｜2
メロン｜1
ぶどう｜2
イチゴ｜1
バナナ｜1
アボガド｜1
というデータが取得したいです。
data列は何が増えるか分からないので、>>5のようにdataの値を固定してcountを取るという方法が使えないのですが…。
224:NAME IS NULL
09/10/30 19:37:59
普通に SQL 勉強しろよ・・・。
Group by するだけだろ？
225:NAME IS NULL
09/10/30 19:57:43 iB0kBy0C
げ、マジだ…。なんでこんなに悩んでたんだろう。失礼しました…。
ありがとう>>224さん。
226:NAME IS NULL
09/10/31 05:16:03 IQbi1b6x
id|flag|...
-+---
1|1
2|1
3|5
....
上記sqliteのテーブルに、30件より古いデータを削除する
DELETE FROM table WHERE id < (SELECT min(id) FROM (SELECT id FROM table WHERE flag > 1 ORDER BY id DESC LIMIT 30));
って書いてみたんですが、もっとスマートな記法があるのでしょうか？
ちなみに
DELETE FROM table WHERE id < min(SELECT id FROM table WHERE flag > 1 ORDER BY id DESC LIMIT 30);
ってやったらエラーになりました。。。orz
227:NAME IS NULL
09/10/31 09:32:24
>>226
flag > 1の扱いがよく分からないが、こうゆうの？
delete from table
where
id in (
select id from table
where flag > 1
order by id desc limit 30
)
ちなみに
DELETE FROM table WHERE id < (SELECT min(id) FROM (SELECT id FROM table WHERE flag > 1 ORDER BY id DESC LIMIT 30))
で、データ削除された？
なんとなく、一件も削除さえ無いような希ガス
228:NAME IS NULL
09/10/31 12:35:06
>>226 のやりたいことは、直近の30件だけ残して
それ以外を削除したいんじゃないの
>>227 だと真逆じゃね
229:NAME IS NULL
09/10/31 13:58:39
>>222
返事が遅くなり申し訳ございません。
そちらも自己相関サブクエリとみなされるのか
230:NAME IS NULL
09/10/31 14:00:15
>>222
返事が遅くなり申し訳ございません。
そちらも自己相関サブクエリとみなされるのか、実行できませんでした。
PHPからの操作だったのでSQL文を二つに分けることで解決いたしました。
ありがとうございました。
231:226
09/10/31 15:29:11
説明が不十分だったようですみません。
>>228さんの書いてる通り、直近の30件(flag=0が間に挟まってたら＋α)だけ残して
それ以前を削除しようとしてます。
>>227
>ちなみに
>DELETE FROM table WHERE id < ～略～
>で、データ削除された？
これが最適解なのか判りませんが一応テストでは希望通り削除されてます。
232:NAME IS NULL
09/11/03 17:36:01 m1YxAjhk
数値Aを基準に、小さくても大きくても、近い値を、近い順に並べるsqlはどう書けば良いでしょうか？
233:NAME IS NULL
09/11/03 18:15:17
SELECT ABS(対象値 - 数値A) as hoge FROM T1 ORDER BY hoge
234:NAME IS NULL
09/11/05 15:29:53 JywnUDtm
あるカラムがいずれかの値をとっているかを調べるのに
カラム IN (値1, 値2, ...)
のような書き方があると思いますが、いずれかのカラムがいずれかの値かどうかを調べるにはどんな書き方があるでしょうか。
イメージは
(カラム1, カラム2, ...) IN (値1, 値2, ...)
です。
今のところはこんな書き方しか思いつきません。
( カラム1 IN (値1, 値2, ...) OR カラム2 IN (値1, 値2, ...) OR ... )
235:NAME IS NULL
09/11/05 21:11:48
そんな感じ。
ある値、なら、
値 in (カラム1, カラム2, カラム3) みたいに書けるけど。
あとは、連結して文字列から取ってくるとかかね。
236:NAME IS NULL
09/11/06 00:25:02
>>234
標準SQLだけで解決したい場合、カラム数が少なければ、
select * from テーブル A
where
exists (
select 1 from (
select id, カラム1 as カラム from テーブル
union all
select id, カラム2 from テーブル
union all ...
) B
where
B.id = A.id
and B.カラム in (値1, 値2, ...)
)
って、やっちゃうかな
237:234
09/11/06 18:24:29
>>235
やっぱそんなもんですか...
>>236
うーん、ぱっと見コストが大きそうですけどどんなもんですかね。
ちなみに非標準ならうまい方言があったりするんですかね？
まあなんにせよ、設計がよくない気がするのでそっちを見直す方がいい気がしてきました。
ありがとうございました。
238:NAME IS NULL
09/11/06 18:25:08
漠然とした質問で申し訳ないんですが・・・
プライマリキーをintegerにするのとcharにするのとでは、integerのほうが速いんでしょうか。
今、言語を表すテーブルを作っているんですが、プライマリキーとして、
id integer primary key auto_increment,
code char(2) not null, -- 'ja', 'en', 'fr' など
にするか、
code char(2) primary key -- 'ja', 'en', 'fr', など
にするか迷ってます。
charにしたほうが、テーブルを覗いたときにわかりやすいし、個人的に気に入っているんですが、
やはり文字列より数値のほうがなにかと高速なように思うし、どうしたもんでしょうか。
239:NAME IS NULL
09/11/06 19:21:37
>>238
「テーブルには必ず数値のプライマリキーをつける」とかの設計思想なら
それはそれでアリだと思うけど「なにかと高速」なんていう曖昧な理由で
意味のない属性を追加しちゃうのはどうかと思う
240:NAME IS NULL
09/11/06 22:13:53
>>238
そのレベルでパフォーマンス論じても意味ねぇ。誤差。
ただあえて言うなら、前者の方がタプルサイズが大きくて
意味のあるデータがリーフにしか存在しないから多分遅い。
でもやっぱり誤差。
241:NAME IS NULL
09/11/07 20:34:59 /yykcTFm
SELECT した全レコードの特定カラムを文字列連結したものを得る方法はありますか？
SELECT C FROM T
とやったときに AAA、BBB、CCC といった値が得られる時、AAABBBCCC のような 1つの値が欲しいです。
さらにそれらの間に任意の区切り文字を入れて AAA:BBB:CCC のような値を得ることはできるでしょうか。
242:NAME IS NULL
09/11/07 20:53:37
concatとか||とかそういうの。
243:NAME IS NULL
09/11/08 00:45:26
ソートに関する質問です。
データ(文字列)として'1'・'2'・'3'・'1～2'・'1～3'・'2～3'があるのですが、
これを'1'・'1～2'・'1～3'・'2'・'2～3'・'3'の順に
出力する方法はないでしょうか。
＃普通にorder byで並べると、
'1～2'・'1～3'・'1'・'2～3'・'2'・'3'順になってしまいます。
244:241
09/11/08 01:03:09
>>242
イメージとしては
SELECT CONCAT(SELECT C FROM T)
とか
SELECT ～, CONCAT(C) FROM T GROUP BY ～
みたいな感じです。
なんとなく伝わるでしょうか？
245:NAME IS NULL
09/11/08 01:28:17
取得してからアプリ側でやれよ。
246:NAME IS NULL
09/11/08 01:33:32
>>244
あ、ごめん。俺バカス。
それは標準だと無理なのでストアド作るか、アプリでやるのがいいと思うです。
247:NAME IS NULL
09/11/08 02:13:53
DBMSがなにかによるけど’～’を’’に置換する関数があればそれでorder byすればできんじゃね？
248:NAME IS NULL
09/11/08 02:24:29
見た感じ文字列長が短いもののほうが後ろに行ってることが問題のように思うけど。
なんか設定あったじゃん。順序決めるやつの。
あれJAPANESE CIなんたらかんたらになってる？
249:244
09/11/08 03:17:57
>>245,246
ですよね。アプリでやります。
>>243
文字列の種類が本当にそれだけしか無いなら、
order by concat(カラム, '～0')
ってのはどうだろう。
250:NAME IS NULL
09/11/08 03:23:26 57QroYvr
使用DB sqlite3
key data
----------------
1 a
2 b
3 c
4 d
5 a
6 b
7 a
8 d
↑のtableテーブルからdataがaなのが何個あるか、bが何個あるか、cが何個あるか…
を調べるSQLがよくわかりません。
select data , count(*)
from table
where data = a
union
select data , count(*)
from table
where data = b
union
:
などとやっていくとdataの種類が700近くあるため
SQL文がものすごく長くなって実行出来なくなってしまいます。
欲しい結果は以下のような感じです。
data count
-----------
a 3
b 5
c 3
d 4
e 2
すみませんがどのように書けばよいか教えていただけないでしょうか。お願いします。
251:249
09/11/08 03:28:12
あれごめん、間違ったかも。
普通に order by しても >>243 の欲しい通りの結果が返ってくるみたい。
そんな環境で実験したから >>249 の案も間違ってるかも。
それでも
order by substr(concat(カラム, '～0'), 0, 3)
ならいけるんじゃないかとおもうけど。
252:NAME IS NULL
09/11/08 03:31:09
>>250
つ group by
253:NAME IS NULL
09/11/08 04:52:28
>>241,246
標準的なSQLでどこまで使えるのかは知らんが、再帰SQLが使えるなら可能かもしれない。
ただし、俺もホストアプリでやるべきだと思う。が、一応考えてみた
結合する順序をどうするかって問題もあるが、とりあえずCの順序で結合する
SQL Server2008で実験
with
R as (select ROW_NUMBER() over (order by C) as rownum,convert(varchar(max),C) as C from T),
TEMP as (
select rownum,C from R where rownum=1
union all
select r.rownum,(t.C+r.C) from R r join temp t on r.rownum = t.rownum+1)
select MAX(C) from temp;
なんかできたっぽいｗ
大量のデータで実行するとえらいことになりそうだ
254:NAME IS NULL
09/11/08 07:17:37
>>252
すいません。ありがとうございます。早速勉強します。
255:NAME IS NULL
09/11/08 10:00:51
そのunion並べる発想がすげえ・・・
select data,count(data) from table group by data order by data;
256:NAME IS NULL
09/11/08 14:46:09
r1 r2 r3
--------
a 1 aaa
b 1 aaa
a 1 bbb
a 1 ccc
b 1 bbb
c 1 aaa
↑のようなデータを
↓のように、r1の件数が多い順に並べ替える方法はありますか？
Mysql 5.1
r1 r2 r3
-------
a 1 aaa
a 1 bbb
a 1 ccc
b 1 aaa
b 1 bbb
c 1 ccc
257:NAME IS NULL
09/11/08 14:53:34
select r1, count(r1) from table
group by r1
と結合してソートすればいいんかね。
258:NAME IS NULL
09/11/08 15:09:23
やってみた
select table.* from table join (select r1 from table group by r1 order by count(r1) desc) a on a.r1 = table.r1 order by a.r1;
259:NAME IS NULL
09/11/08 15:13:52
何か違うだろｗ
260:NAME IS NULL
09/11/08 16:01:11
foo22
foo5
foo11
というデータで
foo5
foo11
foo22
とソートするにはどうすればいいですか
261:NAME IS NULL
09/11/08 16:45:18
>>260
foo の部分が常に一定なら、まず文字列長でソートしちゃうとか？
262:NAME IS NULL
09/11/08 16:47:15
右側二桁切り取って数値に変換してソート。
263:260
09/11/08 17:56:08
右側二桁ですか？
fo o5
foo 11
foo 22
264:NAME IS NULL
09/11/08 17:58:07
右側二桁じゃダメだな。ごめんごめん。
左側三桁の残り。
265:NAME IS NULL
09/11/08 18:20:47
PostgreSQLでやってみた
select * from table order by (substr(data, 4))::INT;
266:260
09/11/08 18:38:20
済みません
実際はfoo以外もあります
foo22
foo11
hoge1
foo5
hoge15
267:NAME IS NULL
09/11/08 20:23:14
SQLServerかつ、hogeやfooのところに数字が混じってないと仮定して。
SELECT
A + B
FROM
(
SELECT
LEFT(COL, PATINDEX('%[0-9]%', COL) - 1) A,
SUBSTRING(COL, PATINDEX('%[0-9]%', COL), LEN(COL)) B
FROM
TBL
) TBL2
ORDER BY A, CAST(B AS int)
OracleならREGEXP_SUBSTRでも使えばいいさ。DBMS書いてねーから後はｼﾗﾈ
>>1を音読してからｒｙ
268:243
09/11/09 22:34:58
>>251
ありがとう。
アイデアを元に、以下でいけたっぽいです。
# 実際には'1個'・'1～2個'というデータだったので、個を消してます。
order by substr((translate(Kosu, '個', '') || '～0'), 0, 4);
269:NAME IS NULL
09/11/09 22:51:15
>>268
'個' を消して order by するだけでよくね？
270:243
09/11/10 01:04:28
>>269
確かに。
おっしゃる通り、以下でよさそうです。
order by translate(Kosu, '個', '')
# そもそも、>>243の質問の仕方が悪かったですね。申し訳ない…
271:NAME IS NULL
09/11/12 20:42:44 61MaVw/S
select sum(price) from table limit 10;
ってするとsum(price)の結果から先頭10行を取ってくる(結果的にlimit意味なし)んだが、
これをlimit 10で抽出した結果をsumしたい場合はどうすればいいのでしょう
select sum(select price from table limit 10)
みたいなことをしたいのだが、こう書いたら当然だがエラーになった
mysql5
272:NAME IS NULL
09/11/12 21:15:40
select sum(price) from (select price from table order by price desc limit 10)
じゃないの？
273:NAME IS NULL
09/11/12 21:24:30
>>271
select
key1,
sum(price) price
from table t1
group by key1
having (select count(*) from table where key1 < t1.key1) < 10
order by key1
動作確認はしていない。
274:NAME IS NULL
09/11/14 10:35:28 /EMexGow
test
275:NAME IS NULL
09/11/20 00:54:43 C7l1oPDg
環境 SQL Server 2005
次のようなテーブルがあります。
テーブル：TEST
A | B |
------------------
1 | 2 |
このテーブルには列（A,B)があります。
データにはAに'1', Bに'2'という値があります。
質問
次の二つのSQL文を実行すると同じ結果になるのはどうしてですか？
SELECT * FROM TEST WHERE A = '1'
SELECT * FROM TEST WHERE A = '1 '
276:NAME IS NULL
09/11/20 01:32:52
>>275
DDLでてないから、憶測だけど、
列Aの定義が、char(4)である可能性に100ジンバブエドル
277:NAME IS NULL
09/11/22 14:31:47
列Aの定義がint（とかの数値系）であった場合、
SQL Serverの暗黙の型変換により文字列'1 'が数値1と判定されるため、に500000000ジンバブエドル
278:NAME IS NULL
09/11/23 11:37:46
安い掛け金だなあ
279:NAME IS NULL
09/11/25 10:00:25 NcYOsBxp
ここで聞くのが適切かわからんが・・・
SQLをselect,from,whereとかいう配列にいれて、実行時に組み立てる利点がよくわからない。面倒だし、サブクエリーしづらいし意味わからん。
利点を説明してください。
↓こういうの。
$params = array(
'from' => array(
'hoge' => array(
'*'
),
),
'join' => array(
'hogehoge' => array('hoge.hogeid = hogehoge.hogeid', array('*')),
),
'where' => array(
'hoge.foo = ?' => 't',
'hoge.bar = ?' => '3',
),
'order' => $options['sort']
);
280:NAME IS NULL
09/11/25 12:38:10
>>279
自由なクエリを組み立てづらいというのは置いといて、そのようにするメリットは、
同じ条件なら完全に同一なSQL文になること。パラメタライズドクエリーが使える
DBMSなら使うのを強制できるし。
「同じ条件なら完全に同一なSQL文」を発行することのメリットは、DBMSが
SQLのパースと実行計画立案の結果をキャッシュしているはずなので、
そのキャッシュを使い回し、結果的に全体のパフォーマンスが上がること。
281:NAME IS NULL
09/11/25 16:04:13
>>280
おお！なるほど！
それは一理ですね！
282:NAME IS NULL
09/11/25 20:58:17
>>280
ホストアプリでの配列や文字列の扱いがよくわからんが、
同一条件で完全に同一なSQLになるのは、「配列」に入れることとは
あんまり関係ないと思うんだが
>>279
そのホストアプリでの（SQL）文字列の生成について、配列にすることに
メリットがあるか？って話だろう
最終的にSQLとして発行するってだけで、ホストアプリでの文字列操作の話なんだから
ここではスレ違いだ。そのホストアプリの言語のスレで聞くべきだな
283:NAME IS NULL
09/11/25 22:23:51
教えてください。
DB に対するクエリを効率よく実行できるようにしたいと思います。
そこで「プリミティブSQL」？と言う言葉を聞きました。
実行する SQL の条件部分以外を先に定義しておいて？、条件をパラメータのような形で
引き渡すことと伺いました。
ようするに、
SELECT * FROM table WHERE id = ?
のような SQL を定義し、実行時に ? の部分だけを指定してあげること、と。
このような形で実行すれば DB 上のキャッシュに情報が残るので検索が速くなる、と。
うえのようなコトをするためのものとしては SQL Server であればストアドプロシージャを
連想するのですが、これとは違う物なのでしょうか。
自分は DB の経験はほとんどなく、上記の話を聞いたときも慌ただしかったため十分には
理解できませんでした。ですのでもしかしたら言葉も間違っているかも知れません。
※ぐーぐる先生に上の用語で質問したのですが、思ったような記事にたどり着けませんでした。
教えていただけると幸いです。
284:NAME IS NULL
09/11/25 22:29:22
パラメータクエリーだな。
検索条件の値だけが違うんで、キャッシュされたクエリーが実行される。
なので、ちょっとだけ速い。
ストアドの単純版みたいなイメージかね。
285:NAME IS NULL
09/11/25 23:00:13
>>283
実行プランまで出しておいて、パラメータが渡されたときに即実行される。
だけど、パラメータによっては予定されたプランが最良と限らない時もある。
複雑なSQLほど有用だと思うけど、思わぬ落とし穴にはまった記憶が。
286:NAME IS NULL
09/11/25 23:07:56
SQLに負担をかけないようにクライアント側で処理したほうが
速くなる可能性大
287:>>283
09/11/25 23:20:55
ありがとうございます。
一応、「DB のプロ」の方に伺った話ではあったんですが。。。
やはり自分でもちゃんと調べて納得しないとダメみたいですね。当然ではありますが。
配慮すべき点の注意もありがとうございました。
また自分で調べてみますが、もし分からないことがあったら教えていただけると幸いです。
288:NAME IS NULL
09/11/25 23:25:12
>>283
おそらくプリペアドSQLだと思われ
同じSQLを大量に発行する場合に有効
厳密にはプリペアドでサーバが「データを検索」する時間は短くならない
SQLがDBサーバに渡された場合、まずDBサーバはそのSQLが間違ってないかチェックして
実際にデータを検索する方法を決定する。プリペアドにすれば、２回目以降は
この解析処理を（キャッシュを利用することで）省略できるので、結果として
データを返すのが速くなる、って理屈
SQL ServerでADO.NETだとかだと、プリペアドは一時ストアドプロシジャ使ってるんじゃなかったかな
289:283
09/11/25 23:36:35
>>288
ありがとうございます。
「プリペアドSQL」言われてみればそんな言葉だった気も。。。
そう言う理屈なんですね。助かります。
こちらのキーワードでももう一回調べ直してみます。
290:NAME IS NULL
09/11/26 14:33:33 9DZPU7hh
・DBMS名とバージョン
Microsoft SQL Server 2005
・テーブルデータ
tblA
pkey|value
----+-----
1 |a
2 |b
tblB
pkey|value
----+-----
1 |c
・欲しい結果
tblA
pkey|value
----+-----
1 |c
2 |b
・説明
両テーブルとも、pkeyが主キーです。
pkeyを条件に、tblAのvalueを、tblBのvalueで上書きしたいのですが、
どの様に書くのが一般的なのでしょうか。
UPDATE tblA
SET tblA.value = tblB.value
WHERE tblA.pkey = tblB.pkey
↑だと、「マルチパート識別子 "tblB.pkey" をバインドできませんでした。」
とエラーメッセージが出てしまいます。
UPDATE tblA
SET tblA.value = (SELECT MAX(tblB.value) FROM tblB WHERE tblA.pkey = tblB.pkey)
WHERE EXISTS
( SELECT *
FROM tblB
WHERE tblA.pkey = tblB.pkey
)
↑とすると、ほしい結果は得られるのですが、
非常に複雑な書き方の様な気がします。
291:NAME IS NULL
09/11/26 15:12:15
>>290
pkeyが主キーならMAX()はなくてもいいんじゃね。
MS-SQLで実行可能かどうか不明だが、
UPDATE tblA SET value = tblB.value FROM tblB where tblA.pkey=tblB.pkey;
292:NAME IS NULL
09/11/26 15:15:34 2WFte6Oi
前者は tblB がSQL内で解決されないから、駄目じゃない？
後者は SELECT * -> SELECT '適当な文字でも可' の方がいい気がする
＃ 自分は 'A' が多いかな＞対象数が増えると、きいてくるよ
293:290
09/11/26 15:35:52
>>291の様な書き方が出来たんですね。
知りませんでした。
>>292
EXISTSの場合は*を使うのがいいよ。
って聞いていたので、いつも*でした。
調べてみます。
どうもありがとうございました。
294:NAME IS NULL
09/11/26 15:58:47
今は、* で十分。
下手に定数を仕込むより、プランナやオプチマイザがちゃんと判断する。
と、思ってます。
295:NAME IS NULL
09/11/26 17:30:00
>>290
ためしてないけど、SQL Serverならたぶんこう
update tblA set tblA.value = tblB.value
from tblA join tblB on tblA.pkey = tblB.pkey
>>292
existsのサブクエリに定数書けなんて、昔に通用してたバッドノウハウ
定数じゃなくて主キー項目セレクトした方が早いとかいうのもあったな
どっちも今じゃ不要の、単なる昔の高速化テクニック
今のＤＢＭＳのオプティマイザはかなり賢いぞ
296:NAME IS NULL
09/11/26 17:34:05
ストアドでやるな
297:NAME IS NULL
09/11/26 20:07:21
>>295
「今のDBMS」にMSSQL2005は含まれますか？