このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ:
SQL質疑応答スレ 8問目
http://pc11.2ch.net/test/read.cgi/db/1236253554/
よくある質問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
よくある質問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
;
よくある質問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 :
1:2009/09/09(水) 19:40:02 ID:???
なんか落ちてたんで立てた
後悔はしていない
おつ
SQL の SELECT で取得する個数を指定することはできますか?
例えば、ソートした結果の100番目から150番目を個取得するなどです。
11 :
10:2009/09/10(木) 20:14:00 ID:???
「個数を指定する」というよりも、「範囲を指定する」の方が言葉として適切でした _o_
13 :
10:2009/09/10(木) 20:24:15 ID:???
>>12 ■件数指定 LIMIT
・先頭から 3 件問い合わせるには、次のようにする。
access の top と同じことができる。
(TOP 10 取得、表示行制限、レコード数限定、件数指定、表示件数)
(1 レコード取得、1 件取得)
select * from testm
order by key1
limit 0, 3
;
みたいな感じでしょうか?
「access の top と同じことができる。」の意味が解らないのですが。。
count(1)とcount(*)の違いを教えていただけないでしょうか?
15 :
NAME IS NULL:2009/09/10(木) 21:21:19 ID:91yFw6iE
お願いします。
テーブル1(t1)には
[ID][受け渡し区分][客着日]フィールドがあります。
主キー 数値 日付型です
テーブル2(t2)には
[ID][データ内容][受け渡し日]フィールドがあります。
主キー テキスト 日付型です。
主キーで結合し、[データ内容][受け渡し日][客着日]の選択クエリを作ろうとしています。
[受け渡し日]の日付に[受け渡し区分]に入った数値を加算した日付を、
[客着日]に入るようなイメージなのですが、
どうもうまく作れません。
分かりにくいかもしれませんが、どうぞよろしくお願いします。。
>>14 その違いは、使ってるデータベスのマニュアルに書いてあるはずだから、
マニュアル読んでくるといいよ
>>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:2009/09/10(木) 22:15:32 ID:91yFw6iE
>>17 すいません。
access2000とかいう化石みたいなもの使ってます。
大まかにご提示いただいたようなテーブルを作成したのですが、
select t2.データ内容, t2.受け渡し日, t1.客着日
from t1,t2
join t2 on t1.id = t2.id
where t1.客着日 = t2.受け渡し区分 + t2.受け渡し日
where指定のあたりが間違ってるとのことなのです・・。
日付演算の方法がわからんというオチだったりしてなw
>>21 親切にありがとうございました
結論として、まったく変わらないということですね
勉強になりました
だめだこりゃ・・・
おまえら自信なさげに不親切な回答してるけど
count(1)とcount(*)の結果は同じだぜ?
26 :
NAME IS NULL:2009/09/13(日) 14:43:18 ID:BgFEKAE5
Oracle10gでスキーマ内で更新されたレコードを抽出することはできますでしょうか?
判別としては各テーブルにUPDDATE項目があります。
DBA_TAB_COLUMNSあたりを使用して抽出できないでしょうか?
27 :
NAME IS NULL:2009/09/13(日) 16:06:40 ID:olkIEqmS
お願いします。
access2003です。
あるテーブルに日付フィールドがあり、
そのフィールドの日付が土日であれば、
翌営業日(月)の日付を返してくれるクエリを作成したいと思っています。
どうぞよろしくお願いします。
とゆうか、sql単体では出来ないのでしょうか
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使ったほうが絶対良いよ
>>26 LogMinerを使うか、FlashBack Queryでもどうにかできるかも
まあトリガーをあらかじめ仕掛けておくのが常道だけど
30 :
NAME IS NULL:2009/09/13(日) 20:28:08 ID:olkIEqmS
ありがとうございます。
UPDATE T1 SET [土日後]=[加算前]+2 WHERE WEEKDAY(加算前)=1;
これで、T1テーブルの、
加算前フィールドの日付が日曜の場合、
+土日後フィールドに2日を加算した値を入れるようには出来ました。
ここから、スウィッチを文に含めると構文エラーと怒られるんですが、
何かケアレスしてそうな所ございませんか・・・
vbaは読みづらい書きづらいで、sqlで済ませたいんです。。
31 :
NAME IS NULL:2009/09/13(日) 20:51:49 ID:mrtjO9XU
>>29 既存のシステムの保守にあたり、テストを行いたいのですが、
機能を処理してメインとなるテーブルはわかるのですが、その他にもないか
判別したいのです。
トリガーとなるとすべてのテーブルに仕掛けておかないといけないですよね
それしか方法はないですか・・・
>>31 じゃあやっぱLogMinerで掘るしかないんじゃね?
>>30 無理して1SQLでやらなくても、土曜の場合+2するUPDATE文と
日曜の場合+1するUPDATE文2回実行すればいいんじゃないかね
余計なお世話かもしれないが
お前のとこの営業日は、祝祭日も年末年始も考慮しないのか
注意: 他のSQLデータベース管理システムでの作業に親しんだユーザは、count集約関数がテーブル全体に適用される場合の性能に失望するかも知れません。
SELECT count(*) FROM sometable;のような問い合わせはテーブル全体を逐次スキャンを用いてPostgreSQLにより実行されます。
うわ、今まさに失望した。
ちなみにpostgresqlのdistinct速くなったらしいね、というのはもう古い情報だろうか
37 :
NAME IS NULL:2009/09/15(火) 19:01:50 ID:nAU+9E+9
複数のテーブルから同条件で抽出したレコードのカウントを取得したいのですが、
スマートな方法はありませんか?
テーブルをUNION ALLで連結し、
countした結果をさらに合計する方法を考えたんですが、
他にいい方法はありますか?
例)
select count as (cnt) from ta union all select count as (cnt) from tb
これの結果をさらに合計↑
UNION ALLしてからcountすればいいじゃない
39 :
37:2009/09/15(火) 22:23:28 ID:nAU+9E+9
>>38 すみません、よかったら具体例書いていただけませんか?
select count(*) from (select * from ta union all select * from tb) where 条件
ということだったんじゃないの?
41 :
37:2009/09/15(火) 23:53:26 ID:nAU+9E+9
サブクエリーですね。
試してみます。ありがとうございました!
今PHPを学習していて、SQLite使ってますが、やはりMySQLの方がいいのでしょうか?
ユーザ管理機能などの点でSQLiteは駄目だとか読みました
しかし何か、SQLiteの方がPHP本体では推奨しているとか、MySQLを禁止しているところもあるとか聞いたりしたものでどうなのかなぁ、と
それでも学習するという意味でも、実用的という意味でもMySQLの方がいいのかなぁと思い始めてきたのですが、どういう違いがあるのでしょうか?
コンパクトで、別途サービスなりデーモンなりを設定しなくていいのがSQLiteの優位性かなあと
44 :
37:2009/09/17(木) 16:47:58 ID:k//XEl+s
37ですが、残念なことにMySQLではサブクエリーが使用できませんでした。
37のはなんとか他の方法でいけたのですが、もう一つ質問があります。
複数のテーブルから同条件で抽出した結果セットの並び替えを行いたいのですが、
サブクエリーを使用せず並び替える方法はありませんか?
select * from ta union all select * from tb order by 条件
とするとテーブルtbのみ並び替えが行われると思うのですが、
taとtbテーブルを合わせた結果セットの並び替えを行いたいのです。
どうすればいいのでしょう?
>select * from ta union all select * from tb order by 条件
>とするとテーブルtbのみ並び替えが行われると思うのですが、
思うだけではなく、実行してみれば?
実行環境がないのかな?
46 :
37:2009/09/17(木) 17:18:33 ID:k//XEl+s
すみません、whereが各select個々に記述しないとだめだったので、
orderも個々じゃないと駄目なのかなと。
確かに推測ではなく確認を取るべきですね。
動作させて出直してきます。
>>44 > MySQLではサブクエリーが使用できませんでした
半端な情報を流すのはやめた方がいいです。
同じような言い方をするならば、MySQLはサブクエリをサポートしています。
× MySQLはサブクエリをサポートしています
○ MySQLはバージョン4.1からサブクエリをサポートしています
両方にorder byかけたっけ?
と思いSQL Server2008で試したけど無理だった。
unionの外でかけるしかないね
order byの評価順は一番最後
unionよりも後
複数店舗の定休日を管理するテーブル設計はどうしたらいいでしょうか。
定休日は「毎週土日、第三木曜日、祝日、毎月15日のみ」などいろいろあります。
また、どの店が定休日かを抽出する必要があります。
( 来週の金曜日が定休日でなく、かつ予約がない店など )
一番先に思いついたのは例えば「毎週土曜日」というデータを持ち、そこから今月であれば 9/5, 9/12, 9/19, 9/26 という日付データを持とうかと考えましたが、来月や年末を見ることを考えると、データ量が膨大になります(^^;。
お知恵を貸してください!
テーブル設計はすれ違いな気もするが
日付、店舗ごとに休みの日もてばいいんじゃね
1店舗最大366件/年だぜ。それで膨大ってことはないだろ
で、バッチでレコード作ればいいよねきっと。
54 :
51:2009/09/18(金) 17:43:05 ID:???
>>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 はどのようになるでしょうか。
select * from ショップマスタ where not exists (
select * from 休日マスタ where 休日マスタ.ShopID = ショップマスタ.ShopID and Holiday = '2009/1/1');
こんな感じで取れない?
56 :
51:2009/09/18(金) 18:07:45 ID:???
>>55 ありがとうございます!ばっちりです。
先の話ですが、ショップマスタが 1000 件、
休日マスタが 10 万件の場合、
ShopID と Holyday へのインデックス適用以外に
パフォーマンスで考えることはなんでしょうか。
SQL もこのままでいけるものでしょうか。
速度はある程度出ると思うけど、一応実測してみてね
>>57 そうですね。ありがとうございます。
0.3s くらい出ているので問題なさそうです。
オプティマイザとかによるんだが、(Holiday,ShopID)で主キーにするなら
select * from ショップマスタ where ShopID not in
(select ShopID from 休日マスタ where Holiday = '2009/1/1')
のが速い気がする
>>59 ああ、やっぱりいろいろあるのですね。
ありがとうございます。
PostgreSQL 8.3 を考えています。
データ件数を 100 万件など増やして試してみます。
inとexistsでの速度計測したらはってほしいな。
62 :
NAME IS NULL:2009/09/20(日) 08:41:22 ID:OTmXdOaQ
このスレの存在をしらずに、別のスレで同じ書き込みをしました。
すみません。
質問は、、、
Mampを使って、php、mysqlとつないで簡単な掲示板を作ったのですが、
文字化けだらけです。utf-8に設定してるのですが、、
どこが問題なのでしょうか。
この問題を解決された方、アドバイスをよろしくお願いします。
>どこが問題なのでしょうか。
macじゃね?
>>61 ちなみにSQL Server2008だとどっちも全く同じ実行計画だった
データ量は?
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32))
というので、usernameにPRIMARY KEYを設定したいのですが、
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32) PRIMARY KEY(username))
のように設定すればいいのでしょうか?
主キーの設定の仕方が分からないもので教えていただけたらと思います
>>66 使ってるDB書いてくれないと答えられないよ!!!!
68 :
66:2009/09/23(水) 13:43:47 ID:1c6MbXvq
すみませんでした
リレーショナルデータベースで、抽象化レイヤはSQLiteです
PEAR::Authを利用前にユーザ情報テーブルを定義しようと思ったのです
CREATE TABLE test (username VARCHAR(50),password VARCHAR(32),PRIMARY KEY(username))
でいけるんじゃない
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値を格納)。
のような場合を考えているのですが、どのような方法があるでしょうか?
ご教授よろしくお願いします。
CASE でできるだろ。
select
a.1列目,
a.2列目,
case when a.1列目<>0 then null
else a.1列目+a..列目 end as 3列目
table a
73 :
70:2009/09/24(木) 02:21:50 ID:???
>71,72
さっそくやってみたらできました!本当にありがとうございます!!
74 :
NAME IS NULL:2009/09/24(木) 13:15:44 ID:Z8PuTcTT
MYSQL5使用してます。
テーブル
id code date
1 abc 2009-09-01 00:00:00
2 def ...
3 abc ...
4 abc ...
・欲しい結果
1日範囲で個別コードごとの総カウント数(COUNT)を取得し返したい。
・説明
アクセス集計していますが、コードがバラバラのため
個別にどれだけ該当コードでアクセスされたのか集計したいのです。
SQL文2回なら出来ますが、出来れば1度にやりたいのです。
よろしくお願いします。
75 :
51:2009/09/24(木) 13:49:14 ID:???
>>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 倍あって計測中止
select s.*
→
select *
でもおなじかの
77 :
51:2009/09/24(木) 15:27:56 ID:???
>>76 同じでしたのう。
何か根本的に間違えているのかな。
>>77 なんでnot inのサブクエリーを改悪する
つかそれでもnot inが900倍も速いのかw
existsのサブクエリのs.*を普通に*とかにしてもホントに遅いままか?
holiday.*とかholiday.shop_idとか1とかにしても遅いままか?
最近のPostgreSQLでは (NOT) IN は速いよ。
でも900倍は変。
holiday.shop_id や holiday.holidayにインデックス貼ってみたらマシになるかも。
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しても実行計画見ないんじゃ何もわからないね。
>>80 テーブルの構成や行数にもよるが、サブクエリでユニークインデクスで検索する場合でも
EXISTS (インデックススキャンをメインテーブル行数回)
より
IN (両テーブルを各1回フルスキャン)
の方が速い場合もある。(つか、あった)
どっちか一方が常に有利なんてことはないんだから、それは別に不思議じゃないと思うが。
>>80 おれはPostgreSQL詳しくないので、特有な癖とかあったらわからんが
もともとnot inは、
>(Holiday,ShopID)で主キーにするなら
って前提だぜ。つまり(Holiday,ShopID)で複合インデックスがあれば
holidayテーブルのフルスキャン必要なくなるはずだ
84 :
81:2009/09/24(木) 23:31:01 ID:???
すまん、ちょっと脊髄反射した...orz
>>83 たしかに、holidayが十分選択性が高ければそこでフルスキャンはかかんないね。
その場合shop_idとの複合にする意味はあんまないと思うけど。
>>85 カバードインデックスってPostgreSQLじゃ有効じゃないのか?
not in と not existsだと調査するまでもなく後者の方が速いと思いこんでいた。
勉強が足りないなぁ俺。。。
88 :
51:2009/09/25(金) 10:31:33 ID:???
みなさん、いろいろとアドバイスありがとうございます。
>>78 遅いっすね。
>>80 複合 INDEX を張ったら 1/30 になりました!
それでも IN に比べると 30 倍遅いです。
「'2009/01/01'を休日に持つshopは10万件」中 15% です。
explain 結果は分けてポストします。
■ 複合 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)
■ 複数 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))
■ 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:2009/09/25(金) 10:38:00 ID:???
5 回くらい実行した explain analyze 結果。
in → 268ms
exists → 780ms
INDEX は下記。
・shop(shop_id)
・holiday(holiday)
・holiday(shop_id)
・holiday(shop_id, holiday)
こんな感じです。複合 INDEX をすっかり忘れていました。
やはり in がずっと成績がいいですね。
>>92 だから、なんでnot inのサブクエリにshopテーブルが必要なんだ?
わざわざ改悪して速度調査ってなんなの?馬鹿なの?本気で馬鹿なの?
なんでおそらく最速になるであろう方法をためさないの?
ちゃんとnot inのサブクエリ直してholiday(holiday,shop_id) に
インデックはった上で実行した結果がみたいぞ
94 :
62:2009/09/26(土) 07:00:08 ID:JyUvDari
>>62 62ですが、解決しました。phpmyadminで確認したら、sjisで設定してありました。
utf-8に設定しなおして、文字化け、なくなりました。
初めての掲示板、ちっぽけな事ですが、私にとっては、ビッグニュースです。
mysqlとphp、これから猛勉強して、はまります。
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で持って来よう
とする場合、どのように書けばいいのでしょうか?
>>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:2009/09/28(月) 22:26:06 ID:???
>>96 すんません、教えてください(アセ
日付は
select max(in_date) from data_dailytbl
where code = 9999;
で出すmax(in_date)を元にしたいんですが、そのsqlにはどうあてはめれば
よろしいのでしょうか?
これじゃいかんか?
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;
>>98 9999;の";"を取り除いて動作しました。
ありがとうございました m(_ _)m
失礼します
独習SQLのP67に書いてあるのですが、「WK_仕入先」テーブルに「仕入先テーブルの行を挿入する(テーブルのデータを別のテーブルにコピーする)」とあるのですが、
@CREATE TABLE WK_仕入先 (仕入先コード NUMBER(5), 仕入先名 CHAR(20) )
と
ACREATE TABLE WK_仕入れ先 AS SELECT * FROM 仕入先 WHERE 1 = 2
が同じとあります
それで分からない事があるのですがAの最後の部分、【WHERE 1 = 2】の部分の 1 = 2とは何を意味しているのでしょうか?
仕入価格 = 12000とかならば分かるのですが、1 = 2 というのは抽象的過ぎて何をやっているのかが分かりません
すみませんがご回答いただければと思います
抽象的な訳じゃなくて、面食らっただけじゃないのかな
1と2は同じではないので、抽出結果は常に0件になる
ただ、WK_仕入れ先を作るために必要なカラム情報は取れる
>>101 はい、面食らいました
未だに意味は分かっていないのですが、コレは特に意味の無い表記なのでしょうか?
ただ@の仕入先コードや仕入先名のようなカラム情報を得たいが為だけに記入されているのでしょうか?
この1や2が何を意味するのかも見当もつきません・・・
1は数字の1、2は数字の2 そのままの意味
1と2を比べれば等しくないのでその条件は不成立となる
かならず不成立となる条件として書いてあるだけ
105 :
NAME IS NULL:2009/10/02(金) 14:44:20 ID:aRTkE45u
OracleとAccess(JetやSQL Server)はどちらがいいのでしょうか?
自分、まだ全然良く分かっていない初心者なのですが、最初からずっと使うであろう方を学んで使い続けたいと思っているのですが
本屋でパッと見たところSQLServerの本が多かったように思えますが、Oracleの方が有名な気がします
どのような違いがあって、どちらがいいのか教えていただけたらと思います
>>105 どちらも体験版というか、ExpressEditionはある。ただ、SQLServerの方が判り易い(と俺は思う)
どちらを選ぶにしろ、解説本を読んで自分のPCで試してみたら?
OracleはOS毎にあるけど、SQLServerは当然のことながらWindows用しかない。
けど、今のレベルではそんなの関係ないだろ?
どちらで勉強しても基本的な所をしっかり押さえれば、大丈夫だと思うけどね。
ほげ台帳
--------
ほげ名 テキスト
担当1コード 数値
担当2コード 数値
担当3コード 数値
担当者テーブル
----------
コード オートナンバー
氏名 テキスト
これを
----------
ほげ名
担当1氏名(ほげ台帳.担当1コード=担当者テーブル.コードになる担当者テーブル.氏名)
担当2氏名(ほげ台帳.担当2コード=担当者テーブル.コードになる担当者テーブル.氏名)
担当3氏名(ほげ台帳.担当3コード=担当者テーブル.コードになる担当者テーブル.氏名)
という形にするにはどうすれば良いでしょうか?
環境はAccessです。
>>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:2009/10/02(金) 22:31:54 ID:RteXPf/F
>>105 インストール後、使えるようになるまではSQL Server の方が少し楽な気がする
OracleXEは分かってる人が使うものって感じかなぁ>個人的な意見
Webのマニュアル(英語版)位しかないよ>出た時ね
今本はあった気がするけど、内容は見たことがないので
#保守契約ないと、パッチ手に入れられないから、業務には使いづらいし
#SQLServerは、一応SPはでるからね
最初は「現場で使えるSQL 第2版」が個人的にお勧め(両方対応してるし)
#10g、2005対応だけどね
Accessは悪くはないけど、最初に触るものとしては、薦めたくないな・・
最初にSQLをちゃんと使うことを覚えて欲しいと思うから
.NET やるなら SQL Server で。
SQL Serverはあまり使ったこと無いけど、ホスト言語も含めた開発環境で変な癖がつかないか?
MSにどっぷりはまるのなら別にいいけど、ここに書かれる質問で、SQL側とホスト言語側の
切り分けが出来ていない物がたまに見受けられる。
ま、問題の切り分けが出来ない人はMS絡みに限ったことじゃないし、SQL Serverも含め
MSに対して他意はない。
.NETとMSSQLが相性いいのは間違いないだろ。
ORMapperにLINQtoSQL使ってみなさいよ。
切り分けができてないだけなのか、がんばればSQLで解決できるのかもしれないと思っているのか
その判断が俺にはできないわ。
114 :
NAME IS NULL:2009/10/03(土) 07:49:05 ID:jYp+tLjS
>>111 >ホスト言語も含めた開発環境で変な癖がつかないか?
あまり無いと思うよ>普段はSQL鯖使い
Linuxから、アクセスするときはFreeTDS使うけど、後はあまり無いんじゃね?
Oracleと比較しても、そう変わらん気がする
#pl/sqlと比べてストアドがかなり弱い気がするけど
パフォチュー関係の情報があまりでてきてないくらいかな
115 :
111:2009/10/03(土) 09:52:16 ID:???
相性がいいから癖がついちゃうとか。
SQLの中に割り当てるホスト言語の変数を埋め込んだままとか、
ホスト言語の一文そのままここに晒されても、とかって思うときがあった。
俺の偏見かな。
一応俺も.NET(C#)使うけど、LINQはまだだし、.NET+DBって機会がろくにないな。
やりたいんだけど、誰か仕事クレw
>>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.コード;
116の追記ですが、最終的にはLEFT JOINになりました。
第二、第三担当がNULLの場合に対応するためです。
___
,r' `ヽ、
,i" ゙;
!.(●) (●),!
ゝ_ _,r''
/ ;;;;;; ・・ ;;;;) <それは報告しなくてもいいです。
/ (_
| f\ トェェェイノ  ̄`丶.
| | ヽ__ノー─-- 、_ )
. | | / /
| | ,' /
/ ノ | ,'
/ / | /
_ノ / ,ノ 〈
( 〈 ヽ.__ \
ヽ._> \__)
mysql 5.1.37
(問)
[groupテーブル]
group_id | group_name
---------+-----------
g1 | グループ1
g2 | グループ2
g3 | グループ3
[belongテーブル]
group_id | user_id | status
---------+---------+------
g1 | u1 | 1
g2 | u2 | 0
g2 | u3 | 1
g2 | u4 | 1
このようなテーブルから、下記のように
group_name | user_count
-----------+-----------
グループ1 | 1
グループ2 | 2
グループ3 | 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 | 1
グループ2 | 2
と、グループ3に対してデータを取得することができませんでした...orz...
※外部結合している為、「belong.status = '1'」の部分で…
なにか良い方法はないでしょうか?
グループ化した結果を、group テーブルに LEFT JOIN。
121 :
119:2009/10/04(日) 02:05:58 ID:???
>> 120
レスありがとうございます。
アドバイスして頂いた内容ですが、「サブクエリを使って…」と
解釈しましたが、問題ないでしょうか?
できれば、サブクエリを使わない方法で取得したいのです。
122 :
119:2009/10/04(日) 02:39:28 ID:???
>>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を発行すると、都合、2回、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
GROUP BY belong.group_id, belong.status
HAVING belong.status = '1'
サブクエリなしのSQL(笑)
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人と出すならこうかな。
>>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:2009/10/04(日) 08:21:47 ID:???
>>126のsqlでもダメでした。
【ダメな理由】
belongテーブルに「belong.status」が'0'のレコードのみ存在する場合、
対応するgroupテーブル中のレコードが表示されない。
[groupテーブル]
group_id | group_name
---------+-----------
g1 | グループ1
g2 | グループ2
g3 | グループ3
[belongテーブル]
group_id | user_id | status
---------+---------+------
g1 | u1 | 0
g2 | u2 | 0
g2 | u3 | 1
g2 | u4 | 1
[取得したい結果]
group_name | user_count
----------+-----------
グループ1 | 0
グループ2 | 2
[取得される結果]
group_name | user_count
----------+-----------
グループ2 | 2
もう少し、考えて見ます。m(_ _)m
テーブルAに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 = テーブルAの最小年月日(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
DBはSQL Server 2005 SP2 です
もう少し具体的に。row_number() 使えばいいだけの話にも見える。
テーブルに有る訳でもないレコードを取ってくるとか
自分ならわざわざSQLでやらないなぁ
ありがとうございます。
具体的にご説明しますと、
@[テーブルA]
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 |
---------------------
A6年後テーブルAに削除がかかっていたとして
[テーブル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 素直にテーブルAから最小日時をとって、コードで(開発言語はVB6.0です)
1年ずつ足して配列に格納とするほうが自然でしょうか。
意図としてはクエリ一発でできればやってしまおうー!と思ったしだいです。
ですが不自然なコードは今後のメンテナンスのためにも書くことは避けたいので、
そのやりかたは変だとあれば、この案はあきらめたいと思います。
俺ならストアドにして終わり、だな。
ストアドかアプリかな
DBに存在しない値をSQLで生成するという考え方がおかしい
どうしてもクエリ1発でやりたかったらストアド書け
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の違いは何なのでしょうか?
書いた奴に聞けよ。
138 :
136:2009/10/04(日) 23:36:01 ID:???
やはり書いた人間の意図によって分けられているだけなのですか・・・
では、すみませんがこの点を教えて下さい
上記のように北斗テーブルをBに設定しているのに、また別にCに設定すると言うことは出来るのでしょうか?
できるよ。
使い道はもちろんあるんだよ。
社員が給与振込口座を複数持つことが出来て、
銀行マスタをいくつも結合する場合、とか。
141 :
136:2009/10/04(日) 23:54:33 ID:???
>>139 分かりました、有難うございます
>>140 具体的に例を挙げていただき有難うございます
で、今思いついたのですが、自分の考えとしては、
>>136はおそらくBのまま使うと、山>=100000の条件が出てこないからかなと思いました。
BはAとの関連付けだけに設定し、CはBの中のC条件だけをAに付加すると言う意図で別に設定したのかな、と。
このような考えで宜しいのでしょうか?
これってサブクエリー使う意味あんの?
SELECT A.風, A.林, B.火, B.山
FROM 南斗 A, 北斗 B
WHERE A.風 = B.風
AND B..山 >= 10000
でいいと思うんだけど。
うん。それでいい。
みんないじわるして教えなかっただけで。
常にそうとも言えないのではないだろうか。
サブクエリで抽出してるのが風で、WHERE句の1つ目の条件が風なんだからこの場合は同じだよ。
同じだねごめん
普通に考えれば
>>142で行けるだろうなぁ
セレクトリストに存在しない項目はWHERE条件に書けない、みたいな制約の
あるDBMSとか存在してるのかも
集約関数あるとGROUP BYに無い項目使うと怒られる、みたいな
もしくは、結合条件でNULL=NULLを真とみなすDBMSとか
148 :
NAME IS NULL:2009/10/06(火) 09:04:06 ID:1YwLzITZ
>集約関数あるとGROUP BYに無い項目使うと怒られる
仕様上はこれが正しい(らしい)のだが
MySQL以外にあるの?
普通はNG
誤読だな。
すいません、教えてください。
・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''';
と書くと、バインドされなくなっている気がする。
ここはどう書くべきです?
ここを読んでみたけどよくわからない・・・。
ttp://www.shift-the-oracle.com/plsql/native-dynamic-sql.html
ストアド、PL/SQLはスレ違いじゃないかな
スレチすいません。どこかわからず・・・誘導お願いします。
155 :
NAME IS NULL:2009/10/10(土) 02:01:42 ID: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
最も速いのはどれ?
なにがしたいんだろう。
とりあえず実行計画みてみたら。
MS SQLServerではupdate table set col1 = value from table A where A.col1 = value2のようにsetの後ろにfrom句が書けるけど、これって標準SQLなのかな?
他dbmsではどう?
【質問テンプレ】
・DBMS名とバージョン
Microsoft Access 2000
・テーブルデータ
支店テーブル
ID | DATA
--+----------+-----
1 | 東京センター
2 | 大阪支店
3 | 名古屋△センター
4 | 福岡△博多△センター
※△は全角スペース
・やりたいこと
スペースを二つ以上含むデータの抽出
このテーブルに対して
SELECT * FROM 支店テーブル WHERE TRIM(DATA) LIKE '*△*△*';
というSQLを実行したところ
4番だけでなく3番のデータも抽出されてしまいました。
別の方法で目的は達成したのですが
なぜ、3番のデータも抽出されてしまったのか教えていただきたいです。
ちなみに
SELECT * FROM 支店テーブル WHERE TRIM(DATA) LIKE '*△';
でも同じ結果がでました。
よろしくお願いします。
Trimして試してみて。
163 :
161:2009/10/17(土) 11:31:13 ID:???
>>162 WHERE TRIM(DATA) LIKE '*△*△*'
としてるんですが、後はどこにいれるんでしょうか。
ああ、もうしてたのか。
スペースだからなのかな
'*屋*屋*'だと3は取れるの?
想像するに全角と半角が区別されずに判定されてるのかもね。
replaceで置き換えるかなんかして工夫が必要かも。
167 :
161:2009/10/18(日) 09:22:12 ID:???
LevelList.sqlを実行すればいいだけじゃ?
171 :
NAME IS NULL:2009/10/19(月) 20:26:06 ID:FLpLj6wo
おねがいします。
テーブルT1、T2があります。
T1には、[ID1] [ID2] [データ名]フィールドがあり、
T2には、[ID3] [日付]フィールドがあります。
ID1とID2の連結した値が、ID3に含まれていれば、日付とデータ名を抜き出すように
SELECT T1.データ名,T2.日付
From T1,T2
Where T1.ID1 + T1.ID2 = T2.ID3
としています。
ここにさらに、
日付が存在しない場合、
日付フィールドを空の状態で
全てのデータ名フィールドを表示させたいのですが、
どうすればよいのでしょうか。
出来るだけアクションクエリを利用せずに作成したいです。
>>171 DB何かくらい書いたら?
でアクションクエリって何?
INSERTとかUPDATEのことか?
失礼しました。
access2003を使っています。
はい、更新クエリや挿入クエリで複数のクエリを作ることを出来るだけ避けたいと思っています。
不一致クエリと選択クエリを結合させると実現は出来たのですが、
非常に効率が悪いとかんじていたので、他に手段がないかと考えています。
外部結合すればいいだけのように思うけど、それをaccess2003でどう書くかは知らない
175 :
NAME IS NULL:2009/10/19(月) 22:49:36 ID:FLpLj6wo
外部結合で実行は出来るのですが、全外部結合をアクセスがサポートしてないようで、
最低4つはクエリが必要ということなのです。
おとなしく4つ作るべきでしょうか・・
何で4つ?日付が存在しないだけなら
T1 left join T2 on T1.ID1 + T1.ID2 = T2.ID3
where T2.ID3 is null
でいいじゃん
>>171 とりあえず用語が曖昧でやりたいことがいまいち伝わらない。
具体例をあげて説明してみてはどうでしょ。
説明に不自由ですいません。
明日一度見直した後、改めて整理したものを書きますね。
すいません、自分でもいまいちどうしたいか理解できていない気がします。
ああ、全外部結合がしたいのか・・・(って
>>171に書いてることとかなり違う気もするけど)
accessだったら確かにできないから右外部結合のクエリと左外部結合のクエリを
UNIONでくっつけるしかないと思うよ
180 :
161:2009/10/19(月) 23:18:25 ID:???
>>165 とれないですねー
>>166 M$だからってことで結論づけたほうがいいかもしれませんね(汗
みなさん、ありがとうございました。
>>171 SELECT T1.データ名,T2.日付
From T1 LEFT JOIN T2 ON T1.ID1 + T1.ID2 = T2.ID3
でいいんじゃないの?
182 :
NAME IS NULL:2009/10/20(火) 18:17:47 ID: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件
色々と考えたのですがさっぱり回答が分からないです。
どなたか教えて頂けませんか。
よろしくお願いします!
分けなさいな。
184 :
182:2009/10/20(火) 19:13:49 ID:???
一発で取得するのが難しくてSQLを分けるにしても
せめて現在月までの各月に対して1回ずつ発行する
みたいな形にできないですかね?
細かく各月毎に対してばんばんSQLを発行したら課題でNG
にされました・・・
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の場合は自分で調べて
とりあえず分けて作って(考えて)から連結するとか。
MySQLのdate_trunc関数やinterval型ってどうやるんだっけ、
そこらヘンでこねてやれば一発(つってもサブクエリまみれ)でできるけど。
一か月後の退会
これは何を指してるんだろう。
> その月の登録者が現在月までの各月(当月を含む)で何人が退会したのか
5月の登録者が6,7,8,9,10月で何人退会したか、という風に読めるので、これは
6月の登録者が7,8,9,10月それぞれの月で何人退会したか
7月の登録者が8,9,10月それぞれの月で何人退会したか
と出さないとダメ?
>>187 それだと1SQLで無理じゃね?
まあそう読み取れるけど。
189 :
182:2009/10/20(火) 20:41:20 ID:???
みなさんありがとうございます。
>>187 その通りです。
説明が下手くそですいません。
質問です。
あるテーブルに登録日と更新日の列があります。
どちらか新しい日付だけ取り出す方法はありますか?
日付が両方ヌル値の場合は最後に表示したいです。
Web画面上に昇順、降順を選択して表示しようと考えています。
宜しくお願いします。
191 :
190:2009/10/21(水) 12:08:56 ID:???
自己解決しました
192 :
182:2009/10/21(水) 12:52:23 ID:ZbRjCu5t
んー色々と考えましたが無理でした・・・
せめて登録のあった月から現在月までの月数分SQLを発行
すればなんとかならないですかね?
5月の登録者が10人、5月に登録して5月に退会した人が5人、5月に登録して6月に退会した人が3人、
5月に登録した人の残存数が2人
↑このSQLを5月を基準として現在月まで発行する(現在月を10月とすると計6回発行)
書き込み多くて申し訳ありません。
>>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にしないんだ
これ、なんかの課題なんだとしたら、こんな設計する人間に課題出されたくはないな
NULLにするぐらいなら退会日付テーブル作るべき
それは面倒なので、NULL不可にして、業務上あり得ない過去日を設定した
というのが関の山
195 :
182:2009/10/21(水) 20:31:11 ID:???
>>193 ありがとうございます!mysql用に直します
そうです。課題なんですよ。
集計を便利にするために月別入会と月別退会は別テーブルで
作るのが本当の正解の用な気がしました。
課題とは関係ないですが会員マスタを元に月別入会者数と月別退会者数
を別テーブルに分けたデータ構造を考えてみたいと思います。
>>194 その通りだとおもいます。
>>182 おまえら頭固いw
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.登録期間
おおほんとだな。すっかりぬけてるw
まあやりたいことはわかるだろ。
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'
>>199 なんかサンプルデータが中途半端なんだが、
その例だと結局ID=1の行だけ抽出できたらいいの?
>>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知らないけどこんな感じにできないかなー
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:2009/10/22(木) 12:53:58 ID:???
>>202,203
ありがとうございました。
参考になりました
205 :
182:2009/10/23(金) 11:23:43 ID: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を
取得したいのですが方法は無いでしょうか?
みなさんよろしくお願いします。
>>205 何の冗談だよこれw
もう集計されちゃってるじゃんw
207 :
182:2009/10/23(金) 12:39:34 ID:???
>>206 えっ?!
この二つのTBLを元にこんな結果を抽出するSQLが知りたかった
んですけど変なことしてます?
●入会月別の集計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
横列固定になったのかな?
「翌々月から現在まで」ってひっくるめるの?
いや、横可変の仕様は変わってないんじゃないかな。
>>196のように行で考える風にしないとダメなのがわかってない。
210 :
182:2009/10/23(金) 13:57:06 ID:???
>>208 翌々月から現在までの意味は5月に登録した人が6月以降から現在月
までに退会したかを対象とする意味です。
>>209 何か根本的に分かってないですかね?
頭が固いから横列固定の考えになっているのかもです。
抽出したい結果があっているのならば横列固定とかは問いません・・・
今月抽出する結果と来月抽出する結果では列数が1つ増えるわけでしょ?
列可変は普通にやったら無理じゃないかな。
212 :
NAME IS NULL:2009/10/23(金) 15:07:41 ID: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が表示されてしまいます。
よろしくお願いします。
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:2009/10/23(金) 15:58:44 ID:NlrCHYmt
>>213 ありがとうございます。
書かれたコードを参考にSQL文を入力しているのですが、Table_A A、Table_B Dとは何を指すのでしょうか?
別名定義。エイリアス。
長いテーブル名なんかを短縮して書けると便利だと思わない?
216 :
NAME IS NULL:2009/10/23(金) 16:23:26 ID: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
よろしくお願いします。
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:2009/10/24(土) 06:24:50 ID:kglbEO/C
>>217 ありがとうございました。
求めている結果になりました。
219 :
NAME IS NULL:2009/10/26(月) 13:34:17 ID:GG3WejBx
SQL+VBSです。
データベースの更新を開いているプラウザでF5や更新じゃなくて
更新をトリガーとして自動的に画面に追加された情報を表示する方法はどんな方法がありますか?
なるべく簡単なほうがいいです。
鼬害
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の仕様上できないようで
一時テーブルを使わないとダメらしいのですが
どうにもうまく動きません。
アドバイスをお願いいたします
>>221 delete from table
where id =1 and date =
(select max(t2.date) from table t2 where t2.id = id)
223 :
NAME IS NULL:2009/10/30(金) 18:57:55 ID:iB0kBy0C
どうかよろしくお願いします。
MySQL 5.1
>>5と似ているのですが
data
-----
いちご
メロン
ぶどう
ぶどう
イチゴ
バナナ
いちご
アボガド
というようなテーブルの中から
data|count
ーー+ーーー
いちご|2
メロン|1
ぶどう|2
イチゴ|1
バナナ|1
アボガド|1
というデータが取得したいです。
data列は何が増えるか分からないので、
>>5のようにdataの値を固定してcountを取るという方法が使えないのですが…。
普通に SQL 勉強しろよ・・・。
Group by するだけだろ?
225 :
NAME IS NULL:2009/10/30(金) 19:57:43 ID:iB0kBy0C
げ、マジだ…。なんでこんなに悩んでたんだろう。失礼しました…。
ありがとう
>>224さん。
226 :
NAME IS NULL:2009/10/31(土) 05:16:03 ID: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
>>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))
で、データ削除された?
なんとなく、一件も削除さえ無いような希ガス
>>226 のやりたいことは、直近の30件だけ残して
それ以外を削除したいんじゃないの
>>227 だと真逆じゃね
>>222 返事が遅くなり申し訳ございません。
そちらも自己相関サブクエリとみなされるのか
>>222 返事が遅くなり申し訳ございません。
そちらも自己相関サブクエリとみなされるのか、実行できませんでした。
PHPからの操作だったのでSQL文を二つに分けることで解決いたしました。
ありがとうございました。
231 :
226:2009/10/31(土) 15:29:11 ID:???
説明が不十分だったようですみません。
>>228さんの書いてる通り、直近の30件(flag=0が間に挟まってたら+α)だけ残して
それ以前を削除しようとしてます。
>>227 >ちなみに
>DELETE FROM table WHERE id < 〜略〜
>で、データ削除された?
これが最適解なのか判りませんが一応テストでは希望通り削除されてます。
232 :
NAME IS NULL:2009/11/03(火) 17:36:01 ID:m1YxAjhk
数値Aを基準に、小さくても大きくても、近い値を、近い順に並べるsqlはどう書けば良いでしょうか?
SELECT ABS(対象値 - 数値A) as hoge FROM T1 ORDER BY hoge
234 :
NAME IS NULL:2009/11/05(木) 15:29:53 ID:JywnUDtm
あるカラムがいずれかの値をとっているかを調べるのに
カラム IN (値1, 値2, ...)
のような書き方があると思いますが、いずれかのカラムがいずれかの値かどうかを調べるにはどんな書き方があるでしょうか。
イメージは
(カラム1, カラム2, ...) IN (値1, 値2, ...)
です。
今のところはこんな書き方しか思いつきません。
( カラム1 IN (値1, 値2, ...) OR カラム2 IN (値1, 値2, ...) OR ... )
そんな感じ。
ある値、なら、
値 in (カラム1, カラム2, カラム3) みたいに書けるけど。
あとは、連結して文字列から取ってくるとかかね。
>>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:2009/11/06(金) 18:24:29 ID:???
>>235 やっぱそんなもんですか...
>>236 うーん、ぱっと見コストが大きそうですけどどんなもんですかね。
ちなみに非標準ならうまい方言があったりするんですかね?
まあなんにせよ、設計がよくない気がするのでそっちを見直す方がいい気がしてきました。
ありがとうございました。
漠然とした質問で申し訳ないんですが・・・
プライマリキーを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にしたほうが、テーブルを覗いたときにわかりやすいし、個人的に気に入っているんですが、
やはり文字列より数値のほうがなにかと高速なように思うし、どうしたもんでしょうか。
>>238 「テーブルには必ず数値のプライマリキーをつける」とかの設計思想なら
それはそれでアリだと思うけど「なにかと高速」なんていう曖昧な理由で
意味のない属性を追加しちゃうのはどうかと思う
>>238 そのレベルでパフォーマンス論じても意味ねぇ。誤差。
ただあえて言うなら、前者の方がタプルサイズが大きくて
意味のあるデータがリーフにしか存在しないから多分遅い。
でもやっぱり誤差。
241 :
NAME IS NULL:2009/11/07(土) 20:34:59 ID:/yykcTFm
SELECT した全レコードの特定カラムを文字列連結したものを得る方法はありますか?
SELECT C FROM T
とやったときに AAA、BBB、CCC といった値が得られる時、AAABBBCCC のような 1つの値が欲しいです。
さらにそれらの間に任意の区切り文字を入れて AAA:BBB:CCC のような値を得ることはできるでしょうか。
concatとか||とかそういうの。
ソートに関する質問です。
データ(文字列)として'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:2009/11/08(日) 01:03:09 ID:???
>>242 イメージとしては
SELECT CONCAT(SELECT C FROM T)
とか
SELECT 〜, CONCAT(C) FROM T GROUP BY 〜
みたいな感じです。
なんとなく伝わるでしょうか?
取得してからアプリ側でやれよ。
>>244 あ、ごめん。俺バカス。
それは標準だと無理なのでストアド作るか、アプリでやるのがいいと思うです。
DBMSがなにかによるけど’〜’を’’に置換する関数があればそれでorder byすればできんじゃね?
見た感じ文字列長が短いもののほうが後ろに行ってることが問題のように思うけど。
なんか設定あったじゃん。順序決めるやつの。
あれJAPANESE CIなんたらかんたらになってる?
249 :
244:2009/11/08(日) 03:17:57 ID:???
>>245,246
ですよね。アプリでやります。
>>243 文字列の種類が本当にそれだけしか無いなら、
order by concat(カラム, '〜0')
ってのはどうだろう。
250 :
NAME IS NULL:2009/11/08(日) 03:23:26 ID: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:2009/11/08(日) 03:28:12 ID:???
あれごめん、間違ったかも。
普通に order by しても
>>243 の欲しい通りの結果が返ってくるみたい。
そんな環境で実験したから
>>249 の案も間違ってるかも。
それでも
order by substr(concat(カラム, '〜0'), 0, 3)
ならいけるんじゃないかとおもうけど。
>>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;
なんかできたっぽいw
大量のデータで実行するとえらいことになりそうだ
>>252 すいません。ありがとうございます。早速勉強します。
そのunion並べる発想がすげえ・・・
select data,count(data) from table group by data order by data;
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
select r1, count(r1) from table
group by r1
と結合してソートすればいいんかね。
やってみた
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;
何か違うだろw
foo22
foo5
foo11
というデータで
foo5
foo11
foo22
とソートするにはどうすればいいですか
>>260 foo の部分が常に一定なら、まず文字列長でソートしちゃうとか?
右側二桁切り取って数値に変換してソート。
263 :
260:2009/11/08(日) 17:56:08 ID:???
右側二桁ですか?
fo o5
foo 11
foo 22
右側二桁じゃダメだな。ごめんごめん。
左側三桁の残り。
PostgreSQLでやってみた
select * from table order by (substr(data, 4))::INT;
266 :
260:2009/11/08(日) 18:38:20 ID:???
済みません
実際はfoo以外もあります
foo22
foo11
hoge1
foo5
hoge15
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を音読してからry
268 :
243:2009/11/09(月) 22:34:58 ID:???
>>251 ありがとう。
アイデアを元に、以下でいけたっぽいです。
# 実際には'1個'・'1〜2個'というデータだったので、個を消してます。
order by substr((translate(Kosu, '個', '') || '〜0'), 0, 4);
>>268 '個' を消して order by するだけでよくね?
270 :
243:2009/11/10(火) 01:04:28 ID:???
>>269 確かに。
おっしゃる通り、以下でよさそうです。
order by translate(Kosu, '個', '')
# そもそも、
>>243の質問の仕方が悪かったですね。申し訳ない…
271 :
NAME IS NULL:2009/11/12(木) 20:42:44 ID: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
select sum(price) from (select price from table order by price desc limit 10)
じゃないの?
>>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:2009/11/14(土) 10:35:28 ID:/EMexGow
test
275 :
NAME IS NULL:2009/11/20(金) 00:54:43 ID: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 '
>>275 DDLでてないから、憶測だけど、
列Aの定義が、char(4)である可能性に100ジンバブエドル
列Aの定義がint(とかの数値系)であった場合、
SQL Serverの暗黙の型変換により文字列'1 'が数値1と判定されるため、に500000000ジンバブエドル
安い掛け金だなあ
279 :
NAME IS NULL:2009/11/25(水) 10:00:25 ID: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']
);
>>279 自由なクエリを組み立てづらいというのは置いといて、そのようにするメリットは、
同じ条件なら完全に同一なSQL文になること。パラメタライズドクエリーが使える
DBMSなら使うのを強制できるし。
「同じ条件なら完全に同一なSQL文」を発行することのメリットは、DBMSが
SQLのパースと実行計画立案の結果をキャッシュしているはずなので、
そのキャッシュを使い回し、結果的に全体のパフォーマンスが上がること。
>>280 ホストアプリでの配列や文字列の扱いがよくわからんが、
同一条件で完全に同一なSQLになるのは、「配列」に入れることとは
あんまり関係ないと思うんだが
>>279 そのホストアプリでの(SQL)文字列の生成について、配列にすることに
メリットがあるか?って話だろう
最終的にSQLとして発行するってだけで、ホストアプリでの文字列操作の話なんだから
ここではスレ違いだ。そのホストアプリの言語のスレで聞くべきだな
教えてください。
DB に対するクエリを効率よく実行できるようにしたいと思います。
そこで「プリミティブSQL」?と言う言葉を聞きました。
実行する SQL の条件部分以外を先に定義しておいて?、条件をパラメータのような形で
引き渡すことと伺いました。
ようするに、
SELECT * FROM table WHERE id = ?
のような SQL を定義し、実行時に ? の部分だけを指定してあげること、と。
このような形で実行すれば DB 上のキャッシュに情報が残るので検索が速くなる、と。
うえのようなコトをするためのものとしては SQL Server であればストアドプロシージャを
連想するのですが、これとは違う物なのでしょうか。
自分は DB の経験はほとんどなく、上記の話を聞いたときも慌ただしかったため十分には
理解できませんでした。ですのでもしかしたら言葉も間違っているかも知れません。
※ぐーぐる先生に上の用語で質問したのですが、思ったような記事にたどり着けませんでした。
教えていただけると幸いです。
パラメータクエリーだな。
検索条件の値だけが違うんで、キャッシュされたクエリーが実行される。
なので、ちょっとだけ速い。
ストアドの単純版みたいなイメージかね。
>>283 実行プランまで出しておいて、パラメータが渡されたときに即実行される。
だけど、パラメータによっては予定されたプランが最良と限らない時もある。
複雑なSQLほど有用だと思うけど、思わぬ落とし穴にはまった記憶が。
SQLに負担をかけないようにクライアント側で処理したほうが
速くなる可能性大
287 :
>>283:2009/11/25(水) 23:20:55 ID:???
ありがとうございます。
一応、「DB のプロ」の方に伺った話ではあったんですが。。。
やはり自分でもちゃんと調べて納得しないとダメみたいですね。当然ではありますが。
配慮すべき点の注意もありがとうございました。
また自分で調べてみますが、もし分からないことがあったら教えていただけると幸いです。
>>283 おそらくプリペアドSQLだと思われ
同じSQLを大量に発行する場合に有効
厳密にはプリペアドでサーバが「データを検索」する時間は短くならない
SQLがDBサーバに渡された場合、まずDBサーバはそのSQLが間違ってないかチェックして
実際にデータを検索する方法を決定する。プリペアドにすれば、2回目以降は
この解析処理を(キャッシュを利用することで)省略できるので、結果として
データを返すのが速くなる、って理屈
SQL ServerでADO.NETだとかだと、プリペアドは一時ストアドプロシジャ使ってるんじゃなかったかな
289 :
283:2009/11/25(水) 23:36:35 ID:???
>>288 ありがとうございます。
「プリペアドSQL」言われてみればそんな言葉だった気も。。。
そう言う理屈なんですね。助かります。
こちらのキーワードでももう一回調べ直してみます。
290 :
NAME IS NULL:2009/11/26(木) 14:33:33 ID: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
)
↑とすると、ほしい結果は得られるのですが、
非常に複雑な書き方の様な気がします。
>>290 pkeyが主キーならMAX()はなくてもいいんじゃね。
MS-SQLで実行可能かどうか不明だが、
UPDATE tblA SET value = tblB.value FROM tblB where tblA.pkey=tblB.pkey;
292 :
NAME IS NULL:2009/11/26(木) 15:15:34 ID:2WFte6Oi
前者は tblB がSQL内で解決されないから、駄目じゃない?
後者は SELECT * -> SELECT '適当な文字でも可' の方がいい気がする
# 自分は 'A' が多いかな>対象数が増えると、きいてくるよ
293 :
290:2009/11/26(木) 15:35:52 ID:???
>>291の様な書き方が出来たんですね。
知りませんでした。
>>292 EXISTSの場合は*を使うのがいいよ。
って聞いていたので、いつも*でした。
調べてみます。
どうもありがとうございました。
今は、* で十分。
下手に定数を仕込むより、プランナやオプチマイザがちゃんと判断する。
と、思ってます。
>>290 ためしてないけど、SQL Serverならたぶんこう
update tblA set tblA.value = tblB.value
from tblA join tblB on tblA.pkey = tblB.pkey
>>292 existsのサブクエリに定数書けなんて、昔に通用してたバッドノウハウ
定数じゃなくて主キー項目セレクトした方が早いとかいうのもあったな
どっちも今じゃ不要の、単なる昔の高速化テクニック
今のDBMSのオプティマイザはかなり賢いぞ
ストアドでやるな
>>295 「今のDBMS」にMSSQL2005は含まれますか?
SQLDBのaddNEWでのインサートが100万件ぐらいあるのでループで50行追加に時間がかかるようになります。
どうやって、対処するか説明があるようなところ知りませんか?
・DBMS名とバージョン
SQLite3
・テーブルデータ
[table_A]
key valueA
------------
1 5
2 2
[table_B]
key valueB
------------
1 2
3 1
・欲しい結果
key valueA valueB
-----------------------
1 5 2
2 2 0 <-「NULL」じゃなくて「0」
3 0 1
・説明
table_A、table_BともにUNIQUE(key)になっています。
単純に結合しようとすると、片方だけに存在するkeyはどちらかがNULLになってしまいます。
それを「NULL」ではなく「0」にする方法はありませんでしょうか?
#「NULL」は「0」に自動変換うんぬんではなく、「0」にする方法です。
>>283 効率の問題だけでなく、安全性やデバッグ効率からも評価してあげてください><
#アドホッククエリ濫用しまくってる奴にサニタイジング云々言われると(検閲削除)したくなるよね('A`)
今更だけどSQLServer2005においてはPreparedなクエリとParameterizedなクエリは区別されている。
単にキャッシュ云々ということであれば、ADO.NETであれば
適切にパラメータを設定してやれば勝手にキャッシュされる。
(内部的にストアドsp_executesqlで処理される。
preparedはsp_prepexecという、また別のストアドで処理される。)
PreparedはSqlCommand.Prepare()、またはsp_prepxecを実行することでなる。
MSDNによると単一のコネクション中で、
パラメータ部分のみ違うクエリを4回以上流して、
ようやくPrepareする効果があると言えるそうだ。
mysql 5.1.34
table test_data
id int
vol int
title_sentence varchar(50)
title_sentenceがnullでなく、かつ関数と言う語句が含まれているものと言う事なら
select id, vol from vba
where
title_sentence is not null and
title_sentence like '%関数%';
で良いと思うのですが、nullでなく、かつ関数という語句が含まれていないものを
抽出したい場合、どのように書けばいいのでしょうか?
not like
>>304 thx なんか考えすぎていたみたいですねw
>>302 ん、Oracleでも違うし、どんなRDBMSでも違うんじゃないの?
質問させてください。 DBMS:mysql5.0.45
業務テーブル G_info
業務ID | 業務名 |
G0001 | ○○設計 |
G0002 | ××調査 |
G0003 | ○△設計 |
業務担当テーブル G_charge
業務ID | 役割 | 社員ID
G0001 | 管理 | P101
G0001 | 検査 | P103
G0001 | 設計 | P105
G0002 | 管理 | P102
G0002 | 検査 | P103
G0002 | 設計 | P106
G0003 | 管理 | P101
G0003 | 検査 | P104
G0003 | 設計 | P107
こんなテーブル構成だったとして、
設計 = P101 かつ 検査 = P103 の担当者を持つ業務名を取り出すSQLはどのようにすれば良いのでしょうか?
308 :
NAME IS NULL:2009/12/01(火) 18:36:45 ID:y01OwToP
質問です。
SQL文の中に@を入れて項目を繋げて書くのは、
どんな処理をするためなのでしょうか。
例をあげてみ。
>>307 とりあえず脊髄反射で書くとこうなる
select I.業務名
from G_info I
inner join
G_charge C1
on I.業務ID = C1.業務ID
inner join
G_charge C2
on I.業務ID = C2.業務ID
where C1.役割 = '設計'
and C1.社員ID = 'P101'
and C2.役割 = '検査'
and C2.社員ID = 'P103'
;
オレは条件反射してみた。
SELECT 業務名 FROM G_info
WHERE 業務ID IN (
SELECT 業務ID FROM G_charge
GROUP BY 業務ID
HAVING 2 = SUM(CASE
WHEN 役割 = '設計' AND 社員ID = 'P101' THEN 1
WHEN 役割 = '検査' AND 社員ID = 'P103' THEN 1
ELSE 0 END))
例に挙げたデータに一致するものがないのは愛嬌か?
312 :
307:2009/12/02(水) 10:49:11 ID:???
310さん311さんありがとうございました。
ちょとSQLをゴテゴテ書いたらタイムオーバーになってしまったので(業務担当テーブルが2万行ぐらい)
自分なりに方法工夫していこうと思います。
>例に挙げたデータに一致するものがない
昨日は相当テンパっていたぽいのでご容赦下さい^^;
インデックス使ってるか見てみたら?
314 :
NAME IS NULL:2009/12/05(土) 14:34:03 ID:zE+CTdjd
簡単そうな気がするのですが、どうしてもうまくいかないので、分かる方がいれば教えてください。
ユーザーが複数の資格を持っているというありがちなテーブル構成があるのですが、
「資格1または資格2を持っているユーザー」の検索はできるのですが、
「資格1と資格2」を両方持っているユーザー」の検索ができません。
データベースはpostgresです。
テーブル構成はこんな感じです。
ユーザーテーブル
[ユーザーID、名前]
001,山田
002,鈴木
資格テーブル
[ユーザーID、資格]
001,資格1
001,資格2
002,資格1
「資格1または資格2を持っているユーザー」であれば、
select distinct ユーザーテーブル.ユーザーID、名前 from ユーザーテーブル
inner join 資格テーブル on (ユーザーテーブル.ユーザーID = 資格テーブル.ユーザーID)
where 資格テーブル.資格 = '資格1'
or 資格テーブル.資格 = '資格2'
で取得できました。
「資格1と資格2」を両方持っているユーザーとして山田さんを検索したいのですが、
単純にorをandにすると、対象が0件になってしまいます。
とても困っているので、よろしくお願いします。
307と同じ問題じゃないの
select 名前 from ユーザーテーブル
where ユーザーID in (
select ユーザーID from 資格テーブル
group by ユーザーID
having 2 = sum(
case when 資格 in ('資格1','資格2')
then 1 else 0 end))
他にもいろいろ解があるからためしてみ
317 :
NAME IS NULL:2009/12/06(日) 00:17:28 ID:u5F21TQj
ストアドである値が入ってきた時だけ、それを検索するというものを作りたいのですが
たとえば input が
客番号
電話番号
顧客氏名のとき
デフォルトは空で、値があるときだけ
where句に
custnum = @in_custnum
という条件が入るような。
入ってきたパラメータで、検索条件をストアド内で変える書きかた教えてください
@in_custnum is null or custnum = @in_custnum
MySQL 5.1.35 WinXP
table book
title varchar(50) cp932
このカラムで先頭1文字が漢字のもの、ひらがなのものとかって
言う風に取出したいのですが、どう書けばいいのでしょうか?
質問があります。
sqlite2を使用しています。
スコアランキングで、自分の上下4人を含めた9人分の情報を取ってきたいのですが、どのようにすればよいでしょうか?
(自分が1位などの場合は自分と下位8人分を取得します)
SELECT T3.*
FROM score_tbl as T1, score_tbl as T2, score_tbl as T3
WHERE T1.userno = 5(自分のユーザーナンバー)
AND T1.score >= T2.score
GROUP BY T3.userno
ORDER BY T3.score DESC
LIMIT 0,9;
試行錯誤しながら上の文まで書きました。
あとLIMITの0の部分に COUNT(T2.userno) - 4 が指定できればOKなんですが…
もっと別の考え方が必要なのでしょうか。
よろしくお願いします。
自分と、上4人と、下4人を UNION したらいいんじゃないのか?
SQL Server2008
ばらばらの金額データが1000行くらい入っているテーブル(主キーは別にある)で
例えば1531円になる組み合わせをすべて抜き出すとかできますか?
Excelとかでできるならそれでもいいんだけど
プログラム書いて総当りでやったほうがいいかな?
1円から1000円までで1000行だとすると、膨大な計算量になるね。
その組み合わせってのは、2個の組み合わせなのか、任意の個数の組み合わせなのか
二つで1531円なら直積取れば簡単にできるな
任意の個数で1531円になるのは、再帰SQL書けばできるかも
>323
そもそももし任意個数だと、それって部分和問題(ナップザック問題の限定パターン)に
なるから相当力業になるような……
いや早く解くためのアルゴリズム(動的計画法)はあるけど。
327 :
321:2009/12/09(水) 00:48:16 ID:???
>>322 UNIONの使い方を知らなかったので調べて試してみたのですが、LIMITの後ろにUNIONを置くことが出来ないので、上4人と下4人をSELECTしてUNIONするっていうのがうまくできませんでした。
「LIMIT clause should come after UNION not before」と出ます。
難しい…
sqliteて使ったこと無いけど
limitを含むselect分を丸ごと括弧でくくってもう一回selectすりゃいいんじゃないの
329 :
321:2009/12/09(水) 13:07:51 ID:???
>>328 丸ごと括弧でくくって試してみたのですが、
(自分が1位などの場合は自分と下位8人分を取得します)
などの取り方が分かりませんでした。
ムリに1つのSQL文で書こうとせず、自分より上位8人と下位8人をそれぞれ
取得して、それぞれ取得出来た数などを計算して結果を作成しようかと考え
てます。
トランザクションしておけば上位を取得中にデータが更新されることもない
と思いますし。
■DBMS
PostgresSQL 8.3以上
■テーブル定義
・IDテーブル
id_1 integer,
id_2 integer,
id_3 integer
・マスターテーブル
id integer,
class char(1)
■テーブルデータ
・IDテーブル
1, 2, 3
1, 2, 4
5, -1, -1
-1, 1, 2
2, -1, 5
・マスターテーブル
1, A
2, A
3, A
4, B
5, B
-1, Z
■やりたいこと
マスターテーブルを参照してIDテーブルの各IDにクラス(A, B, Z)を振り当て、
各レコードが同一クラスに属するか否かを判定し、同一クラスに属するレコード
だけクラス付で抽出する。
ここで「同一クラスに属する」とは、クラスA, B, Zからなるグループに対して、
Z以外の要素が一種類しか存在しない場合に、グループはそのクラスに属すると定める。
【例】
A, A, B, Z -> 属するクラス無し
Z, Z, A, A, Z -> クラスAに属する
B, A -> 属するクラス無し
B -> クラスBに属する
今の場合、
1, 2, 3 -> A, A, A -> A
1, 2, 4 -> A, A, B -> 属するクラス無し
5, -1, -1 -> B, Z, Z -> B
-1, 1, 2 -> Z, A, A -> A
2, -1, 5 -> A, Z, B -> 属するクラス無し
であり、最終的に欲しいデータは
1, 2, 3, A
5, -1, -1, B
-1, 1, 2, A
となります。よろしくお願いします。
※IDテーブルの列数=3、クラス数=3 というのは揃っている必要はなく
IDが5列ありクラスが10種類あるという場合でも適用可能な回答をお願いしたいです。
331 :
330:2009/12/09(水) 14:12:56 ID:???
なお、自力で
1, 2, 3, A, A, A
5, -1, -1, B, Z, Z
-1, 1, 2, Z, A, A
というデータを返すSQLを作成することはできたのですが、その先がわからない状態です。
またその作成したSQLもあまり(全然?)スマートではなく、できるだけ簡潔な構成のSQLを
利用したいと考えています。どうかみなさんのお知恵をお貸しくださいm(_ _)m
■作成したSQL
SELECT
id_1,
id_2,
id_3,
class_1,
class_2,
class_3
FROM
(
SELECT
id_1,
id_2,
id_3,
m_1.class as class_1,
m_2.class as class_2,
m_3.class as class_3
FROM
ID_TABLE,
MASTER_TABLE m_1,
MASTER_TABLE m_2,
MASTER_TABLE m_3
WHERE
ID_TABLE.id_1 = m_1.id
AND
ID_TABLE.id_2 = m_2.id
AND
ID_TABLE.id_3 = m_3.id
) AS TMP_TABLE
WHERE
CASE
WHEN (class_1 != 'Z' AND class_2 != 'Z') THEN class_1 = class_2
ELSE class_1 = class_1
END
AND
CASE
WHEN (class_2 != 'Z' AND class_3 != 'Z') THEN class_2 = class_3
ELSE class_2 = class_2
END
AND
CASE
WHEN (class_3 != 'Z' AND class_1 != 'Z') THEN class_3 = class_1
ELSE class_3 = class_3
END
332 :
330:2009/12/09(水) 14:17:04 ID:???
連投すみません。
>>331のSQLを修正して欲しいというわけではなく、
初めから作り直していただいて一向に構いません。
また
> ※IDテーブルの列数=3、クラス数=3 というのは揃っている必要はなく
> IDが5列ありクラスが10種類あるという場合でも適用可能な回答をお願いしたいです。
これは一般形でも通用するようなSQLそのものが欲しいというわけではなく、
この例の個数に適用できるもので構いません。(それが完全に個数依存のもので
他の個数の場合に応用が利かないものだと困る、という意味で書きました)
長々と失礼しました。
テーブルを始めから作り直せ
>>331 テーブル設計やり直せって意見に激しく同意する
PostgresSQLは使ったことないし手元にないので、SQL Server2008でやってみた
with
DATA_TABLE as (
select
i1.id_1,i1.id_2,i1.id_3,i1.id,m1.class as class
from
( select id_1,id_2,id_3,id_1 as id from ID_TABLE
union
select id_1,id_2,id_3,id_2 as id from ID_TABLE
union
select id_1,id_2,id_3,id_3 as id from ID_TABLE ) as i1
join MASTER_TABLE m1 on i1.id=m1.id
),
CLASS_TABLE as (
select distinct
id_1,id_2,id_3,class
from
DATA_TABLE
where
class <> 'Z'
)
select
id_1,id_2,id_3,max(class) as class
from
CLASS_TABLE
group by
id_1,id_2,id_3
having
COUNT(*)=1
withって便利だなぁw
with使えないならCLASS_TABLEとDATA_TABLEをビューで作るか、
fromに直接カッコつきでselect文書けば行けると思うが
335 :
NAME IS NULL:2009/12/10(木) 17:33:52 ID:MkNFz2CF
営業時間を入れて、ある特定の時間にオープンしているかどうか検索するにはどうしたらいいのでしょうか?
最初考えたのは
CREATE TABLE shop(
id INT AUTO_INCREMENT PRIMARY KEY,
open_time TIME,
close_time TIME
)Type=InnoDB;
こんなテーブルで
SELECT * FROM shop WHERE (open_time <= CURRENT_TIME) AND (close_time >= CURRENT_TIME);
と考えたのですが、これだと、日をまたいだ、0時を過ぎる深夜営業が管理できないです。
なんかちょっとひらめいたのは、close_dateの代わりに何時間オープンしているかというINTERVALをしまえば検索出来そうな気もしたのですが
型を何にすればいいのか、又、どんな演算・関数で条件を記述するのか、いろいろ試してもうまくいきません。
使用DBはMySQLです。
なにか、アイデア、アドバイスあったらお願いします。
closeのほう、日をまたぐときは24時間足して登録するとか
あるいは取得時に
open > closeならcloseに24時間足すか
338 :
NAME IS NULL:2009/12/11(金) 01:12:53 ID:hK/pkhDF
>> 335
レスポンスを考えないで良いなら。
SELECT *
FROM shop
WHERE (open_time < close_time AND CURRENT_TIME BETWEEN open_time AND close_time)
OR (open_time = close_time)
OR (open_time > close_time AND (CURRENT_TIME <= close_time OR open_time <= CURRENT_TIME));
で取得可能かと。
ただ、TIME型は,'00:00:00'-'23:59:59'の間を必ず取ると仮定しております。
datetimeにして、25時とかで登録して、検索するときも '21:00:00' as datetimeとかにすればだいじょぶだよきっと
話の流れをぶった切ってスマソ
ネットワークエンジニアなんだが、昨日、会社からデータベーススペシャリストの
資格取れと言われた。
Mysqlからはじめようと思うのだが、Mysqlの良い基本書はないかな?
できればコマンドラインからの入力から説明している基本書がいい。
Webの説明は見にくいのが辛い…。
すまん、誤字った。
Mysql→MySQL
343 :
340:2009/12/12(土) 11:08:20 ID:???
誘導サンクス!
初歩的な内容だと思うのですが・・・・
(問)
ID | NAME | DATA
--+-------+-----
1 | tanaka | aa
2 | satou | bb
3 | suzuki | cc
例えばこんなテーブルがあったときに
IDとNAMEに関してマッチするレコードを
取得するとしたら
WHERE ID = 1 AND NAME = 'tanaka'
としますけど、マッチングの条件が複数あるときって
WHERE (ID = 1 AND NAME = 'tanaka')
OR (ID = 2 AND NAME = 'satou')
こんなふうに単純に書くしかないでしょうか?
マッチング条件の対象列がひとつなら
WHERE NAME in ('satou', 'suzuki')
とできますが、複数列の場合にもこういうふうに
まとめて書く方法がないかと思いまして・・・
ない。
そうですか・・
どうもありがとうございました。
少なくともOracleなら
where (ID, NAME) in ((1, 'tanaka'), (2, 'satou'))
とか書けるけどな。
他は知らない。
>>347 おお!それで出来ました!
ありがとうございます!
349 :
NAME IS NULL:2009/12/13(日) 16:57:39 ID:6mnFzqzE
MYSQL5.1使ってます。
結合しつつ、最新データの抽出がうまぐできないのでよろしくお願いします。
(idlist テーブル)
id name taxfree
1 A 1
2 B 0
3 C 1
4 D 0
5 E 0
6 F 1
(sales テーブル)
id data price
1 12/8 3000
1 12/7 2500
2 12/8 2000
2 12/7 2800
3 12/8 150
3 12/7 200
6 12/5 500
上記2つのテーブルから、
下記の形式のように、idlistから taxfree=1 のものをまず表示し、
最新日付のデータか、最新日付が無い場合一番近い日付のデータを表示したいと考えています。
id name data price
1 A 12/8 3000
3 C 12/8 150
6 F 12/5 500
SELECT
idlist.id AS id ,idlist.name AS name ,sales.data AS data ,sales.price AS price
FROM idlist
LEFT JOIN sales
ON idlist.id=sales.id
AND sales.data=(SELECT max(DATA) FROM sales )
WHERE idlist.taxfree=1
これだとやたら重いのと、最新日付以外は表示されません。
どうぞよろしくお願いします。
要はid毎の最新日付のレコードを取得すればいいわけだから、
>>4にtaxfreeの条件を
追加すればいいはず。
ただ確かに、
>>4はカラムやテーブルの追加などの応用がしにくいという問題がある。
個人的には not exists を使った相関サブクエリがお勧め。
select idlist.id, idlist.name, sales.data, sales.price
from idlist A
join sales B on B.id = A.id
where A.taxfree = 1
-- ここまでは単なるjoin
and not exists (
select * from sales
-- グルーピングに用いるキー属性は'='で…(a)
where id = B.id
-- それ以外は同一キーを排除できる何らかの条件を指定…(b)
and data > B.data
)
ここでBのキー属性がもっと多い場合など、グルーピングの単位を変更する必要がある
ならば(a)にその属性を追加すればよいし、(b)は同一値を排除できればよいので、'>'でも
'<'でも、あるいは必要に応じてその他の条件に変更してよい。
「属性Xが最大のもののうち属性Yが最小のもの」などという複合条件でも同じ考え方で
容易に応用できる。
なんで
>>4じゃダメだって言ってるのかイマイチワカンネ
select C.ID,
C.NAME,
A.DATA,
A.PRICE
from SALES A
inner join
(select ID,
max(DATA) as MAX_DATA
from SALES
group by ID
) B
on A.ID = B.ID
and A.DATA = B.MAX_DATA
right outer join
IDLIST C
on A.ID = C.ID
where C.TAXFREE = 1
;
>>353 さんのselect文でうまくいきました。
ありがとうございました。
初心者なのでよくわからないんですが、外部結合をするとコストかかるのは常識なんですかね。
1000×20万レコードくらいで10秒ほどかかってしまいます。(ローカル環境)
さくらサーバーで実行すると2秒くらいなので気にはしてないんですが。
>>352 のほうは何故かうまくいかずで・・・(わたしの書き方がマズかった可能性大です。)
外部結合のコスト云々より、
>>4の処理で最新行だけを取得するのに2回スキャンかかるからだろうな。
高速化するのなら各RDBMSにあわせた書き方がある。
>>4は汎用。
MySQLならこんなのはどぉ? 推奨できないけどw
SELECT A.id,A.name,C.data,C.price FROM
(SELECT * FROM idlist WHERE taxfree=1)AS A
JOIN (SELECT id,max(data)AS data,price FROM
(SELECT * FROM sales ORDER BY id,data DESC) AS B GROUP BY id
) AS C USING(id);
MySQLの仕様って細かく追っていないけど、PostgreSQLのDISTINCT ONと同等になったのかな。
4.xの時代はORDER BY句はなし(2重目のサブクエリが不要)でもいけてた気がするんだけどなぁ。
>>355 レスどうもです。
MySQL5.1でやりましたが、結果・・・爆速(0.2秒)でした!
有難うございます!
PostgresSQL 8.3 での全文検索についてですが、
http://www.postgresql.jp/document/current/html/textsearch-tables.html ここの冒頭にある例文を参考にして、
CREATE TABLE hoge (
name TEXT
);
INSERT INTO hoge (name) VALUES ('aaa');
INSERT INTO hoge (name) VALUES ('abc');
INSERT INTO hoge (name) VALUES ('aac');
INSERT INTO hoge (name) VALUES ('bbb');
を実行した後に
SELECT * FROM hoge
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'a');
を実行したのですが、結果が返ってきません。
(aaa, abc, aacの3レコード返ってきてほしい)
条件を変えて
SELECT * FROM hoge
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'aaa');
とすると1レコード(aaa)返ってくるのですが…
何か勘違いや基本的な見落としをしているでしょうか?
スレ違いっぽいな。
Posgreは知らんけど、全文検索って、形態素解析とかでキーワードに分割して、
そのキーワードで検索するんじゃないの?
となると、'a' なんてのはキーワードとして登録されないと思うよ。
なるほど、そういうことだったのですね。
ありがとうございます。
なお LIKE を使うことで当初の目的は果たせました。
# すみません、確かにスレ違いな気がします
360 :
NAME IS NULL:2009/12/18(金) 12:00:33 ID:GtpUvhEu
すみません
AccessのSQLで、日付を絞り込む構文教えてください
絞り込み対象日付、開始日付、終了日付
ただし終了日付がnullの場合があり、代わりに2999年12月31を代入するといった処理をしたいです。
アドバイスをお願いします。
PostgreSQL8.3
word varchar256
全文検索についてですが、varchar256 のフィールドに対して検索を
行っています。
SELECT * FROM hoge WHERE
to_tsvector('english', word) @@ to_tsquery('english', 'aaa');
検索自体は問題ないのですが、ストップワードに登録されているもの
(a や i など)が破棄されてしまい困っています。
ネットで調べたところ、english.stop でストップワードを設定して
いるようでしたので、ここから該当のものを削除しました。しかし、
to_tsvector()では削除したものが破棄されずに残り、to_tsquery()
では削除したものが破棄されて検索ができません。
現状
to_tsvector('english', 'a pen') > 'a' 'pen'
to_tsquery('english', 'a pen') > 'pen'
to_tsquery()のストップワードはどこで設定されているのでしょうか?
よろしくお願いします。
362 :
361:2009/12/18(金) 20:35:45 ID:???
すみません、追記です。
希望
to_tsvector('english', 'a pen') > 'a' 'pen'
to_tsquery('english', 'a pen') > 'a' 'pen'
363 :
361:2009/12/18(金) 22:29:18 ID:???
自己解決しました。
よかったね。スレ違いだから、二度と来んな。
>>360 AccessだとCOALESCEが無いみたいだから、代わりにIIF()使ってみては
あるテーブルからランダムに5つ選択する必要があるんですけど、SQLでの書き方がわかりません。
select *, rand() randval from employees where deleted is false order by randval limit 5;
なんてやってるんですけど、たくさんのレコードがある時に、たかがランダムに5つ選ぶためだけにたくさんのレコードをソートするのもばからしいように思います。
なんかかっこいい方法あれば教えてください。
連番のキーがあれば、それでやることはできるだろうけど、そういうの(ROWNUM とか含む)
がなければ、ソートするしかないだろう。
>>367 おお、連番のキーがあればできるんですか?
今は
id integer primary key auto_increment
というのがあるので、これでできますか?おしえてください!
DBMS がわからんので、rand() 関数の仕様がわからないけど、
where id in (rand(), rand(), rand(), rand(), rand())
みたいなイメージでやればいい。当然、rand() から、id の範囲内に収まるように
計算することを忘れずに。
>>369 うーん、その方法だと、現時点でのidの最大値がわかってないといけなさそうだし、
なにより where 句での条件指定ができなくないですか?
今回は
>>366に書いたように、where deleted = false みたいな条件がはいるので、
where id in (rand(), rand(), rand(), ... ) の条件と deleted = false が重なると、
検索結果が5件より少なくなる可能性があります。
後付君ウザイです。
まぁまぁ、deleted is false は最初から書いてあったし。
ただ、DBMSが何かは書いて欲しいところ。auto_incrementってあるからMySQLだとは思うけどね。
んで、where句でrand()を使うとインデックスを利用しなかったりするから、
ソートしてlimit 5でいいんじゃね。他にいい方法は無いと思うよ。
>>372 > ただ、DBMSが何かは書いて欲しいところ。auto_incrementってあるからMySQLだとは思うけどね。
DBはMySQL4です。DBを限定すると、もっといい方法があったりするんでしょうか。
> 他にいい方法は無いと思うよ。
やっぱりそうですか。まあ、ないということがわかっただけでもありがたいです。
ありがとうございました。
特にMySQLとかだったらバージョンによってはサブクエリが使えないとか、あるいはDBMS毎に固有の関数・句が
有るとかないとかで書き方が変わる
と言う事は方法を提示してもそちらでは使えない…なんて事があると言う事は容易に想像できると思うのだが。
DBMSによる独自拡張があるからね
MySQL4なら拡張以前の問題があるけど
376 :
372:2009/12/23(水) 23:52:30 ID:???
ちょっと補足(半分言い訳)
「他にいい方法は無いと思うよ」とは書いたが、あくまで「思うよ」だからな。
ま、SQLだけでしかもMySQL4なら間違ってはいないと"思う"けどね。
ただ、乱数を付加しつつ全行取得してソートのコスト(行数による)が非常に高いのであれば、
ホスト言語などで乱数を生成して
SELECT * FROM employee WHERE deleted is false AND id = 乱数;
を5件取得するまで繰り返した方が速いかもね。
auto_incrementの現在値(=現在の最大値)を取り出す方法も全スキャンmax(id)じゃなく
関数かシステムカタログからで取り出せるんじゃね。MySQLでの方法を俺は知らないけど。
WITH RECURSIVEをサポートするDBMSならSQLだけで済みそうだがな。
377 :
NAME IS NULL:2009/12/26(土) 19:04:21 ID:dwahwS+t
oracle 10gのUPDATE文で質問です。
Aテーブル
aaa|ddd
Bテーブル
aaa|bbb
Cテーブル
bbb|ccc
Aテーブルに対して、カラムaaaでBテーブルが紐づき、
Bテーブルに対して、カラムbbbでCテーブルが紐づくとします。
(つまり、Aテーブルのレコード1件は、Bテーブルを経由してCテーブルのレコード1件と紐づく)
ここで、A.ddd = 'X'のとき、C.ccc = 'Y'に更新したいのですが
下記の文であってますでしょうか?
また、もっと効率のいい方法はありますでしょうか?
update C set C.ccc = 'Y' where C.bbb in
( select B.bbb from B where B.aaa in
( select A.aaa from A where A.ddd = 'X')
)
連結しちゃった方が速そうな気もする。
>>377 update C set C.ccc = 'Y'
where exists (select *
from A inner join B on A.aaa = B.aaa
where A.ddd = 'X' and B.bbb = C.bbb
)
動かせる環境が無いので、typoはご容赦。
380 :
377:2009/12/27(日) 16:44:31 ID:???
>>378,379
ありがとうございます!
参考にさせていただきます。
弁護士の名前(name)とその人の所属法律事務所の名称(ofic)から成るテーブルがあるとして、
法律事務所は弁護士がひとりしかいないところから300人以上いるところまでさまざまなので、
「所属する弁護士の数が多い事務所トップ10」 の事務所名と人数を select一発で表示させるには
どうすればいい?
自分で考えたのはサブクエリが二重になってしまったが、うまくやれば一重で済む気がする。
それはギャグで言っているのか?
いやマジ
サブクエリなんていらんよ。
自己解決しますた。マジでサンクス
あ、サブクエリ一重じゃなくて全然使わない方法でです
どうやって解決したかを書くならともかく、そんな報告なんの意味もないからしなくていいよ。
そんな解決策書かれたって、なんの足しにもならんだろ。
select top 5 ofic, COUNT(name) from table group by ofic order by COUNT(name) desc
同人数の時にどうするかが書かれていないので考慮なしで。
多いのベスト10じゃなくて200人以上の事務所とかいうんだったらサブクエリが必要だな
いらないだろ・・・。
having
SELECT文について質問です。
【質問テンプレ】
・PostgreSQL8
・テーブルデータ
テストデータ
名前 |1回目点数|2回目点数|
----+--------+--------|
田中 | 100 | 90 |
鈴木 | 80 | 80 |
成績マスタ
成績 | 点数 |
----+-------|
A | 100 |
B | 90 |
C | 80 |
・欲しい結果
名前 |成績(1回目)|成績(2回目)|
----+---------+---------|
田中 | A | B |
鈴木 | C | C |
・説明
2TABLEを連結してSELECTしたいのですが、テストデータの点数を下に、成績マスタから(1回目)と(2回目)の値をひっぱってくるにはどう書けばよいでしょうか。
手元に実行環境がないので机上で↓のようなSQLを考えてみました。ただ正常に動くとは思えませんorz
これでOKか、違っていたら教えてもらえませんか?
select 名前,成績 AS 成績(1回目),成績 AS 成績(2回目)FROM テストデータ
LEFT JOIN 成績マスタ.点数 ON テストデータ.点数
それじゃ無理。成績マスタを二回連結すればいい。
>>393 95点とかを考慮せず単純に考えた。
SELECT 名前,T2.成績,T3.成績 FROM テストデータ AS T1
JOIN 成績マスタ AS T2 ON T1.1回目点数 = T2.点数
JOIN 成績マスタ AS T3 ON T1.2回目点数 = T3.点数;
成績マスタ
成績|点数FROM|点数TO|
----+--------+------+
A | 91| 100|
B | 81| 90|
C | 71| 80|
本当はこんなことしたいんじゃないの?
基本的なことで申し訳ないんですけど、
あるテーブルから特定のデータが1件でも存在するかどうかの判断をしたいのですが、
COUNT()はテーブル内の全部のレコードを検索してしまうので、もっと高速でスマートな方法は無いか存じないでしょうか。
>>397 PostgreSQLならLIMIT
FirebirdならTOP
OracleならROWNUM
ほかのDBはシラネ。調べるのも面倒
400 :
398:2010/01/07(木) 22:43:21 ID:???
何となく脊髄反射してしまったが、
「特定の条件」でインデックス検索するようであれば、COUNT(*)でも全部のレコードを検索しないし、
インデックス検索しなければ、EXISTSでも全部のレコードを検索してしまうわな。
401 :
398:2010/01/07(木) 22:47:01 ID:???
何度もゴメソ。
EXISTSは1レコード見つかった時点で検索をやめるから、常に全レコードという分けじゃないな。
そういう点ではCOUNT(*)より速いか。
どうやらこれが爆速みたい。でもtrue/false返しているわけじゃない(0/1が返ってくる)からなんか怪しいなあ。
SELECT
EXISTS(SELECT * FROM ユーザ WHERE ユーザID IN('id'))
>>402 それが早いかどうかは実際のデータ量とインデックスの有無とデータの分布とオプティマイザの賢さによるので
お前が実験したときにたまたま早くてもあまり意味がない
ちなみにexistsは通常、真か偽か判定するのに使う。0/1が帰ってくるのは
お前の実験した処理系で真と偽が1と0に評価されてるだけだ
一般的に言えるのは、行があるかないかだけ判断すればよく、その行数まで必要ないなら
count使う必要はないだろう、ってぐらい
exists使ったことないってやつ結構多いんだろうなぁ
多いよ。そして、existsとかいうやつを使わないように書き直してよ、とお願いされたりする。
知らないものにはフタだね・・・
どうして勉強しようとしないのだろう
406 :
NAME IS NULL:2010/01/12(火) 15:06:26 ID:5/3GZMUC
申し訳ありませんが、質問させてください
CREATE TABLE dog_cat (
dog_id INT(11),
cat_id CHAR(10),
PRIMARY KEY (dog_id, cat_id),
KEY dog_id (dog_id, cat_id) //【A】
);
CREATE TABLE dog (
dog_id INT(11) AUTO_INCREMENT,
〜
PRIMARY KEY (dog_id),
UNIQUE dog_id (dog_id), //【B】
KEY dog_id_2 (dog_id) //【C】
);
の【A】〜【C】の意味がわかりません
【A】 : KEYというものが分かりません。
自分の勉強不足でしょうが、持ってる本にも載っていないし、ネットで検索しても主キー(PRIMARY KEY)ばかり出てきます。
dog_id,とcat_idをKEYというデータ型に設定しているのでしょうが、その前(2つの引数の前)に書かれているdog_idとは何を意味しているのでしょうか?
【B】 : 上記【A】と同じく、dog_idをUNIQUE型に設定しているのでしょうが、その前に書かれているdog_idというのが分かりません。
例えばココ(
http://www.techscore.com/tech/sql/03_03.html)などのUNIQUEの項目を参照してもUNIQUEの後には文字が無く説明もありません。
【C】 : 今度はdog_id_2と、2が付いていますが、この一文が何を意味したいのかとか、何の役割があるのかが分かりません
すみませんが教えていただければと思います
PRIMARY KEYってnvarchar型にも設定していいんですか?
いいよ。
ありがとう!
お勧めしないけどな。
2chのスレタイ一覧を格納してるんだけど
1263039170.dat<>gyuttoについて語るスレ (3)
9240912012.dat<>【2ちゃんねる10周年】1000人BINGOでお試し●ゲット!!【無料】 (2)
9240912701.dat<>【設定無料】ドメイン移転を歓迎するレンタルサーバー【無制限】 (1)
これの .datはずしてintで格納したほうがいいかね?
こんな感じで書いてみたんだけど・・・
CREATE TABLE subject (
dat char(15)
, title nvarchar(256)
, response int(4)
, rank int(4)
, bbs nvarchar(20)
, PRIMARY KEY (dat)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL初挑戦なんでよくわからんw
intに収まるのならどうぞ
あ(・∀・)り(・∀・)が(・∀・)と(・∀・)う!
Primary Keyが重複ってエラーでるの無視するにはどうすればいいですか?
DBD::mysql::st execute failed: Duplicate entry '1111'' for key 'PRIMARY'
418 :
417:2010/01/13(水) 10:49:33 ID:???
ごめん
間違ってたんで無視してくださいな
メモリーに入りきらないデータの検索ってどうやってやってるのですか?
420 :
NAME IS NULL:2010/01/13(水) 18:50:41 ID:4+ds+P+d
質問させてください。今130万×130万くらいの巨大な行列があります。
ただ要素は、ほとんどが0で一行に数字が入っているのは最大32までで、
負の数もありません。
これで集合知プログラミング10章の非負値行列因子分解をしたいのですが、
あまりにも大きいのでとりあえずデータベース板に来ましたが、
MySQLの勉強がてらこの問題を解けないかと思っているのですが、
MySQLはこのような用途に向いていますか?またよさげなライブラリみたいなのは
ありますか?どこから手を付けていいか分からないのですがよろしくお願いします。
>>419 ディスクを使うに決まってるじゃない
>>420 よく分からんけど向いてないんじゃね?
一般的に言ってRDBMSは分析とか計算は苦手
特定の問題を解くために特別な機能が用意されてる場合もあるけど
それは本職じゃない
むしろ、Rとかみたいな統計ソフトの出番じゃね?
423 :
420:2010/01/13(水) 21:09:08 ID:???
質問です。
以下のようなテーブルをもとに、
id , name , date , 売上
1 , りんご , 2010-01-12 , 350
1 , りんご , 2010-01-07 , 250
2 , みかん , 2010-01-12 , 450
2 , みかん , 2009-05-05 , 550
それを、売上があった日の1営業日後と3営業日後のそれぞれのidの価格も取得したいと考えております。
id , name , date , 売上 , 1営業日後売上 , 3営業日後売上
1 , りんご , 2010-01-12 , 350 , 400 , 500
1 , りんご , 2010-01-07 , 250 , 200 , 250
2 , みかん , 2010-01-10 , 450 , 500 , 300
2 , みかん , 2009-05-05 , 550 , 220 , 350
(ちなみに実際のdateはUNIXTIMEで格納しています。)
1営業日後にしているのは、休日の関係で1日後とできないからです。
ここまでやるのは厳しいでしょうか?
MYSQL 5.1.3
PHP 5.2.7
すいません上の質問はミスです。
改めまして・・・
SELECT * FROM tableA WHERE s_id = 7
というSQLで以下のように表示されるのですが、
id , code , name , date , 売上
1 , 7 , りんご , 2010-01-12 , 350
1 , 7 , りんご , 2010-01-07 , 250
2 , 7 , みかん , 2010-01-12 , 450
2 , 7 , みかん , 2009-05-05 , 550
それを、売上があった日の1営業日後と3営業日後のそれぞれのidの価格も取得したいと考えております。
id , code , name , date , 売上 , 1営業日後売上 , 3営業日後売上
1 , 7 , りんご , 2010-01-12 , 350 , 400 , 500
1 , 7 , りんご , 2010-01-07 , 250 , 200 , 250
2 , 7 , みかん , 2010-01-10 , 450 , 500 , 300
2 , 7 , みかん , 2009-05-05 , 550 , 220 , 350
(ちなみに実際のdateはUNIXTIMEで格納しています。)
1営業日後にしているのは、休日の関係で1日後とできないからです。
ここまでやるのは厳しいでしょうか?
MYSQL 5.1.3
PHP 5.2.7
3行目 SELECT * FROM tableA WHERE code = 7
でしたね・・・。一服してきます・・・。
table sample
id int(11)
code int(11)
name varchar(10)
uriage_date date
uriage int(11)
select id,code,name,
sum(
case uriage_date
when ADDDATE(sysdate(),interval 1 day) then
uriage
else 0
end ) as day_after_1uriage,
sum(
case uriage_date
when ADDDATE(sysdate(),interval 3 day) then
uriage
else 0
end ) as day_after_3uriage
from sample
where
code=7;
みたいな?
428 :
427:2010/01/14(木) 12:20:13 ID:???
営業日の所は判らんw
>>419 421
検索した情報によると
メモリーに入りきらない場合は
メモリー一杯によみこんで全部なめてから
次の領域をメモリーに読み込んで
同じようになめてくそうな
検索方法を自分で作って組み込む場合は、
この辺も気にして組まないといけないんだろうか
>>427 返信どうもです。
試したのですが1日後と3日後を取得してくれず、それぞれ0と表示されました。
あと、code=7 が複数該当していても1件しか表示してくれませんでした。
>>424,425
1 , 7 , りんご , 2010-01-12 , 350 , 400 , 500
この、400とか500とかいう数字はどっからでてきたんだ?
>>431 id , code , name , date , 売上
1 , 7 , りんご , 2010-01-12 , 350
のような形式のデータがたくさんあって、
>>425 に書いてるのはその一部だけで、
1営業日後売上 , 3営業日後売上のデータはサンプルです。
>432
足りないのはやりたいことをまとめる能力か他人に伝える能力か
営業日カウントの方法がまったく不明なので
とりあえず、該当id,codeでdateが1日後の売上の合計と、同3日後だとして
select
id,code,name,date,売上,
(select sum(売上) from tableA A1 WHERE A1.id=A.id and A1.code=A.code and A1.date=ADDDATE(A.date,interval 1 day))as 1営業日後売上,
(select sum(売上) from tableA A3 WHERE A3.id=A.id and A3.code=A.code and A3.date=ADDDATE(A.date,interval 3 day))as 3営業日後売上
from tableA A
where code = 7
でできるんじゃないかな
MYSQLはよく知らないし持ってないから試してない
日付の加算は>427から推測した
パフォーマンスとか言い出すなよ
これたぶんTableAなんて実表存在してないよな
■DBMS名とバージョン
Oracle10g
■テーブルデータ
○荷物テーブル
id 中身id 荷受け
1 1 2009/12/11
2 1 2009/12/21
3 2 2010/12/25
○中身テーブル
id 品物 重さ
1 本 500
2 テレビ 1000
○作業テーブル
荷物id 作業項目id 作業者名 作業状況
1 1 佐藤 完了
1 3 志村 作業中
2 1 鈴木 完了
2 2 鈴木 未
2 3 田中 未
3 1 佐藤 完了
3 3 田中 未
○作業項目テーブル
id 作業内容
1 品物を入れる
2 領収書を入れる
3 封をして発送する
■欲しい結果
荷物id 品物 重さ 作業者名※1 作業内容※1 作業状況 対応班※2
2 本 500 鈴木 領収書を入れる 未 2
3 テレビ 1000 田中 領収書を入れる 未 1
※1 作業状況が完了以外かつ作業項目idが一番小さいもの
※2 重さが1000以上→作業班1、作業者名が鈴木かつ作業状況が未→作業班2、作業状況にかかわらず、作業者名に田中がある荷物→作業班3
(班1 > 班2 > 班3の順で優先し、荷物idを重複させない)
■説明
荷受けが2009/12/31以前の荷物かつ対応班が存在するものを抽出したいです。
荷物は沢山ありますのでパフォーマンスもできれば考慮したいです。
「パッとわからないけどちょっと考えればわかるだろJK」→「2時間考えてもできないorz」(今ここ)
そんなに難しくないだろ。
少しは自分で考えろよ。
面倒なのは、作業テーブルの部分と対応班のところか。
1. まず、荷物テーブルと中身テーブルをjoinする。
2. 作業テーブルと作業内容テーブルをjoinする
3. 2を荷物idでグループ化し、作業状況が完了以外で一番作業項目idが小さい奴を抽出する。
4. 1と3をjoinする
5. 対応班はCASEとか使って出力する
って感じでいいんかな。
437 :
434:2010/01/15(金) 00:00:13 ID:???
ありがとうございます。
対応班1、対応班2、対応班3とバラバラにならばそれぞれSQLを書いて出すことはできました。
あとはそれぞれをまとめて重複しているidを削除すればなんとかなりそうです。
重複削除の処理ですが、例えば
table
id 対応班 value1 value2 ...
1 1 ...
1 2 ...
2 2 ...
2 3 ...
3 1 ...
というテーブルがある場合、
id 対応班 value1 value2 ...
1 1 ...
2 2 ...
3 1 ...
と最小の対応班をもつ行を出力するのは
SELECT 略 FROM table main JOIN (SELECT id, min(対応班) AS 対応班 FROM table GROUP BY id) sub
ON main.id = sub.id AND main.対応班 = sub.対応班
とすればできそうですが、もう少しスマートな方法はないでしょうか?
439 :
NAME IS NULL:2010/01/15(金) 01:59:13 ID:e1+mTjHW
<DB名>
mysql
<バージョン>
Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2
<テーブルデータ>
データなし。
create table table1 (u_id int(4) not null);でテーブルを作成した状態。
<ほしい結果>
+------+
| u_id |
+------+
| 1 |
| 3 |
+------+
<質問内容>
http://www.postgresql.jp/document/pg800doc/html/sql-savepoint.htmlから下記のコマンドコピーして実行した。
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;
なぜか下記のようになってしまう。
+------+
| u_id |
+------+
| 1 |
| 2 |
| 3 |
+------+
問題はどこにあるでしょうか。
>>438 営業日カレンダーとはどんなものでしょう?
日付をunixtime(int型)で格納してあるので、1営業日後や3営業日後の判定がうまくできずで・・
1日後でいいなら
FROM_UNIXTIME(200201696)+86400
とかでいいんでしょうけど。
442 :
NAME IS NULL:2010/01/15(金) 12:25:00 ID:7GILZKdj
Postgres8.3.xを使ってます。
DB:hogeDbA
Schema:hogeScA
Table:hogeTableA
DB:hogeDbB
Schema:hogeScB
Table:hogeTableB
hogeTableAとhogeTableBのjoinはできますか?
>>439 MyISAM使ってました
なんて落ちは無いよね?
MySQL 5.1での質問です。
A B C
1 q name1
2 w name2
3 e name3
4 q name4
5 t name5
上記テーブルのB列の重複を除いたA,B,C列がほしい場合
SELECT DISTINCT B FROM table_name;
ではB列の重複を除くだけで他の列がとれません。他の列も併せて取得する場合はどのような解決策がありますか?
望んでる結果は以下のものです。
A B C
1 q name1
2 w name2
3 e name3
5 t name5
よろしくおねがいします
>>446 ありがとうございます。
>>4 を参考に組み立てると以下でいいんでしょうか?
カラム名のせいで見づらいですが
SELECT A.A,
A.B
A.C
FROM tablename AS A
INNER JOIN
(SELECT A,
MAX(B) AS TEST,
C
FROM tablename
GROUP BY A
)B
ON A.A = B.A
AND A.B = B.TEST;
上記SQLだと全A(カラム)を拾ってきてしまいます。
>>4は「各idに対してdateが最大」の行を抽出している
>>445なら「各Bに対してAが最小」とか「各Bに対してCが最大」とか
になるんじゃないの?
sql初心者です。次のような統計を出したいのですが、行き詰まっています。
存在する列 pref_no(char) ,city_name<char), population(var_char)
表名 japan
何をしたいか?:各都道府県列(pref_no)ごとに人口(population)最大の都市名を降順に
一都市ずつ出したい。
打ち込んだ構文
select pref_no,city_name,population from japan
order by population desc;
たとえば
pref_no city_name population
25 otsu 330000
26 kyoto 1480000
27 osaka 2630000
27 sakai 820000
28 kobe 1540000
28 himeji 530000
29 nara 370000
というデータがあれば
27 osaka 2630000
28 kobe 1540000
26 kyoto 1480000
29 nara 370000
25 otsu 330000
というデータを出したいわけです。ちなみに使用している環境はmysqlで、INTERSECTが使えませんorz
mysql使ったこと無いが
population(var_char)であれば
25 otsu 330000
26 kyoto 1480000
だとotsu 330000の方が大きいと判断されるんじゃねーの
>>445 列A、列Cが昇順であるかどうかも本当のところわからない状態では、
RDBとしての設計が間違っているかも知れない。
重複した場合は最初のものでいい、と考えるなら完全に間違っている。
そういうのは設計スレでやれ。
>>450 ありがとうございます!
>>4でうまくいかなかったのですが、改めて見直せばうまくいきました。
自己結合する表の中の列をMAXにするとうまくいくことを知り、とても勉強になりました。
>>451 すみません。int型です。別データと勘違いしてました。
>>453 設計の話ではなくて質問として成立しないと言っているような。
テンプレは役にたつなぁ
458 :
449:2010/01/17(日) 18:59:09 ID:???
先程はどうもありがとうございます。あれから頑張って先頭列にrankという順位を付けてみたのですが、
この構文(成功はしてます)をこれ以上省略可能でしょうか?
select (select count(C.population)
from (select pref_no,max(population) as population from japan group by pref_no) as C
where C.population > A.population)+1 as rank,pref_no,city_name,population
from japan A join (select pref_no,max(population) as population from japan group by pref_no)
as B on (A.pref_no=B.pref_no and A.population=B.population)
order by rank;
すみません。列を表別名で修飾してませんでしたorz
select (select count(C.population)
from (select pref_no,max(population) as population from japan group by pref_no) as C
where C.population > A.population)+1 as rank,A.pref_no,A.city_name,A.population
from japan A join (select pref_no,max(population) as population from japan group by pref_no)
as B on (A.pref_no=B.pref_no and A.population=B.population)
order by rank;
DBMSによるがrankを簡単につけられる構文はあるよ。
またはCTEのように共通するsqlを1つにまとめたりできる。
>>456 うん、「設計」は適切でなかった。 name1が選択されてname4が排除される理由が見出せないというだけのことだね。
462 :
445:2010/01/17(日) 22:20:33 ID:???
>>461 すみません、間違いでした。実際はこうです。
A B C
1 q name1
2 w name2
3 e name3
4 q name1
5 t name4
で、以下の結果がほしい。
A B C
1 q name1
2 w name2
3 e name3
5 t name4
なんとか、がんばってみます。
>>462 まだ解決してなかったのかw
>>4そのまんまでIDをB、DATEをA、DATAをCに書き換えるだけだろ
(MAXをMINに書き換えるかどうかはご自由に)
OSQLGRIDを使っているのですが
DOSバッチでSQL文を実行できるソフト(できればフリー)
ありませんか?
OSQLGRIDでバッチを利用できるならば一番楽なんですが。
465 :
464:2010/01/25(月) 04:28:55 ID:???
できれば実行後にCSVファイルにしてくれるものがいいです
スレ違い
SQL*Plus使えばいいじゃん。「フリー」だからw
いじわゆ!
あっちょんぶりけ
あちょぷまうまう
471 :
471:2010/01/29(金) 11:59:51 ID:???
【質問テンプレ】
・DBMS名とバージョン: SQLite 3
・テーブルデータ:長いため下記に記載
・欲しい結果:RSSリーダ(brief)のデータ(*.sqlite)から特定の記事データを削除したいです。
・説明:長いため下記に記載
●テーブルデータ
=======================================================================================================================
■entries
| id | feedID | primaryHash | secondaryHash | providerID | entryURL | date | read | updated | starred | deleted | bookmarkID |
-----------------------------------------------------------------------------------------------------------------------
■entries_text
| title | content | authors | tags |
-----------------------------------------------------------------------------------------------------------------------
■entries_text_content
| docid | c0title | c1content | c2authors | c3tags |
-----------------------------------------------------------------------------------------------------------------------
■sqlite_sequence
| name | seq |
=======================================================================================================================
472 :
471:2010/01/29(金) 12:18:28 ID:???
1.【entries】テーブルの[deleted]フィールドが「 2 」の場合に、そのレコードを削除。
2.同時に、【entries_text_content】テーブルの[docid]フィールドが、1.で削除した
[id]フィールドと同じ値のレコードも削除。
3.更に同時に、【entries_textテーブル】から、1.で削除した[idフィールド]の値(数字)と
同じレコード番号のレコードを削除。 ※idフィールドがない為
4.その後、下記のフィールドの値を、1・2・3〜とリナンバリングしたい。
【entries】テーブルの[id]フィールド
【entries_text_content】テーブルの[docid]フィールド
5.そして、1.2.3.の三つのテーブルのレコード数がどれも同じ数だけあるか確認。
6.最後に、【sqlite_sequence】テーブルの[seq]フィールドに5.で確認したレコード数を
設定したい。
お手数ですが、以上よろしくお願いします。
トランザクションがかからんとか、そういう話?
SQL Server Compact 3.5 で、a_tbl と b_tbl のコードが一致した場合に
a_tbl に、b_tblの名前をセットするみたいなことしたいのですが無理ですか?
UPDATE a_tbl
SET name = b_tbl.name
FROM b_tbl
WHERE a_tbl.no = b_tbl.no
実行すると、「FROM 句はこのクエリの種類では使用できません」ってエラーになります。
SQL Server 2008 だと問題ないのですが・・・
コンパクト使ったことないからこれが通るかどうかわからんが
UPDATE a_tbl
SET a_tbl.name = b_tbl.name
FROM a_tbl JOIN b_tbl on a_tbl.no = b_tbl.no
が正解じゃないのか?
Fromにa_tblがないのにそれ通す2008も凄いと思うが
ちなみにUPDATEのFROM使わないでこんな書き方もある
UPDATE a_tbl
SET name = (select name FROM b_tbl WHERE no = a_tbl.no )
これもコンパクトで動くかどうかしらないが
こっちの方が通すシステムは多い気がする
まあ、これはデータ次第でエラー出る可能性はあるが
そんときは適当にmaxとっとけwそもそもの仕様がおかしいけどな
msdnみろ。compactにはupdateにfrom句は使えなかったはずだ。
477 :
471:2010/01/29(金) 21:45:02 ID:???
>>473 自分にレスしてくれてるのかな!?
SQLについてよく分からないもので・・・。
>>471の質問で自分で分かるのは、下の二つくらいでしてidフィールドが
ないテーブルからレコード削除するのにどうやるのがいいかとか、トリガ
とか、フィールドに1から番号を振りなおすとかが分からないのです。
DELETE FROM entries WHERE deleted = '2'
UPDATE sqlite_sequence SET seq = (SELECT count(*) FROM entries)
番号の振りなおしとかは、DOSでバッチとかJavaScriptやVBSとか、その他
を使用しないとできないのかな?
>>477 お前さんにだよ。
トランザクションは使えるみたいだけど、ストアドはないみたいだな。
普通にトランザクションかけて、プログラムで制御しかないだろ。
479 :
471:2010/01/29(金) 22:52:07 ID:???
>>478 レスどうもです。
SQLさっぱりなもので、調べても
>>477が限界で・・・。 _| ̄|●lll
よろしければ
>>471を実現するSQLを書いていただけると助かります。
だからプログラムでやれ、って言ってんじゃん。
どうせSQL単体でできるもんじゃないんだから、SQLにこだわる理由がわからん。
481 :
471:2010/01/29(金) 23:07:44 ID:???
>>480 レスありがとうございます。
SQLだけだと
>>471-472は、できないんですね。
>>472で不可能な部分は除いて、可能な部分だけでも、SQLでどう書けばいいのか知りたいです。
SQL+JavaScript、SQL+VBS/WSH?で
>>472を全て可能にするSQL+スクリプトを書いていただけると尚助かります。
ご存知の方いましたら、どうかよろしくお願いします。
不可能ではない。が、あんまり意味はなさげ。
ところで、レコード番号って何?
484 :
471:2010/01/29(金) 23:25:12 ID:???
>>482 レスありがとうございます。
【entries】と【entries_text_content】は、[id]と[docid]を元に消せると思うのですが、
【entries_text】には同じ数のレコードがあっても番号のフィールドがないので、
行番号というか、例えば【entries】の[id]が「 83 」のレコードを消したら、
【entries_text】の83番目のレコードも消したいという事で、レコード番号と書きました。
ただ、これも83番目を消したあとに、さらに【entries】の[id]が「 84 」を消そうとした場合
【entries_text】の83番目を消すと、その後の83番目は以前の84番目なのでその辺も
どうするのがいのか分からず・・・。
(番号の多い方から消していくとか、別にフィール作ってidを作成しておくとか!?)
RDBMSとしてありえない設計。普通にIDつけろよ。
連番の振り直しって何で必要なの?
表示するときに加工すればいいだけじゃね?
487 :
471:2010/01/29(金) 23:36:38 ID:???
>>483 レスありがとうございます。
なるほどー。
>>472の自分が分かったSQLに、一つ追加され勉強になりました。
ただ、
>>472の一連の動作がどうにも分からず!
>>485 レスありがとうございます。
そうですね。
RSSリーダの作者に言ってみます・・・。
すみませんが、ほとんど分かっていないので、とりあえず
>>472を実行したいので、
>>472で不可能な部分は除いて、可能な部分だけでもSQLでどう書けばいいのか、
ご存知の方いましたら、SQLのみ、SQL+JavaScript、SQL+VBS/WSH?などで
書いていただけると大変助かります。
よろしくお願いします。
丸投げ馬鹿は死ねと。
>>477とか
>>483とかしか分からないってだけで丸投げしてないと思うが。
まあ俺が分かれば書いてやりたいところだが、俺も見に来てるだけだから分からないしな。
それより
>>483以外は無駄にレス伸ばしているだけで意味ないから止めてくれ、ログ漁って調べるのに邪魔だ。
1. delete from entries where deleted = 2
2. delete entries_text_content where docid in (select id from entries where deleted = 2)
* 順序考えろよ
3. rownum とかなきゃ無理
4.
>>483 5. select count(*) を各テーブルに
6. update splite_sequence set seq = select count(*) from どれか
>490
3.は一時的にフィールド追加して
>>483するとか方法は色々あるじゃん。
SQLiteではカラムの追加はできても削除はできないっぽい
まあ一時的にっつーか恒久的に追加すべきだが
てか現状のテーブル設計でentries_textはどうやって参照されるんだ?
一時的にカラムを追加して、順序は誰が保証してくれんの?
>>483,490
SQLiteじゃそれ実行できないよ。
>>490は、sqliteをspliteと間違ってたり酷いな(w
entries_textにIDをもたせずに連番っていうところが設計ミスだよな。
order byによって連番なんて変わるものだし他テーブルとの関連を結びつける何かが(IDが望ましい)必要となる。
sqliteにカスケード機能があるかどうか知らないが、あるとしたら設定するだけで削除は自動でなされる。
sqlite_sequenceは必要か?
欲しい時はcountとればいいと思うんだが。パフォーマンスも影響しないだろ。
entries_textのtitleと、entries_text_contentのc0titleが同じだからそれ使って削除できるな。
というか、briefのsqliteの構造について質問者にどうこういっても作者じゃないんだから意味ねーよ。
MYSQLなんですけど
date data
2010/01/01 hoge1
2010/01/05 hoge2
みたいなテーブルから
2010/01/01 hoge1
2010/01/02 NULL
2010/01/03 NULL
2010/01/04 NULL
2010/01/05 hoge2
のような結果の出せるSQLを求めてます。
select * from table between date '2010/01/01' and '2010/01/05'
とするとデータのある2レコードしか出ないのですが、
データがない部分も日付を出しつつ、デフォルトのデータないよっていう文字列を出せると助かります。
お助けください!
単発SQLでやるような処理じゃないと思うけど
どうしてもやるなら日付テーブルを作っておいて外部結合するか
2010/01/02 NULL
2010/01/03 NULL
2010/01/04 NULL
をUNIONでくっつけるか
すいませんが、SQLを教えてください MySQL 5.1.35です
日付テーブル dailytbl
t_date 西暦日付
出来高aテーブル
code 1,2,3〜
a_date 西暦日付
dekidaka
出来高bテーブル
code a,b,c〜
a_date 西暦日付
dekidaka
日付テーブルのt_dateに沿って出来高を表示させたいです。
出来高aテーブルにしても、出来高bテーブルにしても出来高のない日が
ありますが、出来高のない日はdekidakaを0として表示させたい
イメージとしては
日付テーブル 出来高aテーブル 出来高bテーブル
t_date 1_dekidaka a_dekidaka
2010/01/28 1000 1500
2010/01/29 1026 0
2010/01/30 0 3400
2010/01/31 0 5500
2010/02/01 1020 0
2010/02/02 1030 0
2010/02/03 1040 4550
2010/02/04 1040 5500
2010/02/05 1000 0
みたいな感じなんですけど。
>>502 OUTER JOINとIFNULL()
t-sqlならこんな感じ。
select
t.t_date
ISNULL(a.dekidaka,0),
ISNULL(b.Dekidaka,0)
FROM
dailytbl t LEFT JOIN 出来高aテーブル a ON t.t_date = a.t_date
LEFT JOIN 出来高bテーブル b ON t.t_date = b.t_date
505 :
502:2010/02/09(火) 21:32:45 ID:???
なんか基本的な所を間違えているのか?
日付テーブルと出来高aテーブルとの2つだけでやっても、先程の例で言えば、
2010-01-30、2010-01-31の日付が出てこず orz
ちょっと考え直します。んでこの質問は閉じさせてください
506 :
NAME IS NULL:2010/02/13(土) 15:16:59 ID:g2r47VLn
MYSQLで以下のようなSELECTを行いたいのですが、効率面で相談させてください。
「会員である(=退会していない)人間の中で、ポイントランキングを抽出する」
という処理です。
■user_tbl
| user_id | name | point |
■member_tbl
| user_id | member_flg |
という2つのテーブルがあるとします。
この時「member_flgが1なユーザーの中で、pointが上位3位の人の名前取得したい」のです。
SELECT name FROM user_tbl
INNSER JOIN member_tbl ON user_tbl.user_id = member_tbl.user_id
WHERE member_tbl.member_flg = 1
ORDER BY user_tbl.point DESC
LIMIT 3
これをJOINを使わないようにしたいのですが、現実的な変更方法はありますでしょうか?
・サブクエリも使いたくない
・クエリーを2度発行するのはOK(JOINに比べ性能がガタ落ちするのでなければ)
・member_flgは結構な割合で1以外が入っている
・実際はもっと色々カラムを持っていたり、別のテーブルとの絡みがあり、user_tablとmember_tblはまとめられない
・user_tblとmember_tblには数万件のレコードが入っている
これで遅いなら、インデックスの方に問題があるんじゃね?
遅いということはないのですが、今後JOINを出来る限り減らしていこうという方針があり
今のところ「こればっかりは無理だよな」とは思っています。
ですが自分はSQLを学び始めたばかりなので、実は良い方法があったりするのかなと思い相談させていただきました
JOINを使わずに結合する方法はあるけど、あれって方言だろ?
テーブルを結合するんなら、JOINは使わざるを得ない。その方針とやらがおかしいんじゃね?
JOINをなるべく減らすという方針自体は正しい
JOINは大抵の場合遅いからな
JOIN使うくらいなら、クエリ2度発行した方が早い場合も少なくない(MYSQLなら特に)
user_tblからまずは全件持ってきて
member_tblからも全件持ってきて
呼び出しプログラム側でmember_flgみながら上位3つを取得
ってのじゃダメなの?
512 :
NAME IS NULL:2010/02/13(土) 15:50:48 ID:g2r47VLn
>>511 全てのランキングを取得。ならそれでもよかったと思います。
ただ、レコード数が多い+欲しいのはトップ3だけと考えると、あまり良い方策ではない気がします。
・上位30位くらいを持ってくる
・member_flgが1かどうかを問い合わせる
・30位分の中に、3人くらいはmember_flgが1のやつがいるだろう
という方法も考えたのですが、今回はそれで良いかもしれません。
ただ、より汎用性の高い方法が無いのだろうか?と考えています。(もしかしたら、無いのかもしれません)
MySQLってJOIN一回より、30回クエリー投げる方が速いの?
っ[ IN ]
主キー同士の1対1のJOINならなんら問題はないと思うけどな。
スピードを重視したいならポイントに索引を付けるのもひとつ。
あと同3位が2つ以上あったらどうするつもり?
516 :
NAME IS NULL:2010/02/13(土) 16:07:56 ID:g2r47VLn
>>515 ポイントに索引はつけないとダメですよね。
同3位については、とりあえず主題ではないので置いときます(ご指摘ありがとうございます)
やはりこの手の「会員の中でランキング」とかの場合、JOINが必須なんですかね。
>>516 情報が二つのテーブルにわかれている以上、
誰かがどこかでその二つを突き合わせ無いとダメだわな
>>511が言うように、全件取得してアプリで付き合わせれば
JOIN使わなくても欲しい結果は得られる
通常RDBMSは検索や突き合わせに対して最適な方法を選択して実行してくれて
その結果だけを返してくれる
そのRDBMSに結合させる命令であるJOINより、無駄なデータを取得してアプリで
突き合わせる方が高速になるとは通常考えにくいと思うが
MySQLのオプティマイザや結合処理ってそんなに貧弱なのか?
518 :
NAME IS NULL:2010/02/13(土) 18:33:59 ID:g2r47VLn
>>517 そこらへんどうなんでしょうね?
自分のPG脳の判断だと
・まずはポイント上位から順に調べられるように準備
・上位から選びあげるたびに、結合されたテーブルを見に行ってmember_flgを確認
・繰り返して3人まで選んだら終了
とはやってそうな気がしますが…
普通ならJOINかEXISTSやIN等を使ったサブクエリが思いつくが、速くしたいならストアドで処理するくらいじゃないか?
PGでするのは通常は遅いと思う。
MYSQLではビューにIndex貼れたりする?できるならそれも試して欲しい。
実際にどういう方法でデータを取得したかは、実行計画をとってみればわかる
つか実行計画も見ずにパフォーマンスやインデックスの必要性を判断するな
521 :
NAME IS NULL:2010/02/13(土) 23:55:00 ID:ElJKKmZW
販売集計処理について教えてください。
環境はMySQL+PHPです。
■商品テーブル
商品名 販売本数
─── ────
お菓子
お弁当
飲み物
■販売履歴テーブル
商品名
───
飲み物
お菓子
お弁当
お菓子
お菓子
飲み物
お弁当
お弁当
商品テーブルの各商品が、販売履歴テーブルに何件ずつあるかを集計し、
商品テーブルの販売本数に記録させたいと思っております(日次処理)。
これについて、1クエリでDBに丸投げするのが早そうな気がするのですが、
そのようなSQL文が書けるようでしたら是非教えてください。
素人丸出しの質問で恐縮ですが、宜しくお願いいたしますm(__)m
普通に GROUP BY で COUNT でいいだろ。
販売履歴テーブル自体に日付カラムを持たせれば日次処理もいらない。
どこの宿題だよ ひどい問題だなw
すいません、教えてください
SELECT id,カナ,名称 FROM tbl WHERE 名称 LIKE あ% ORDER BY カナ
上記のような場合
カナにインデックスを定義するメリットありますか?
(名称にはインデックス無しの前提で)
なんでやってみて実行計画見ないの?
そんなんオプティマイザ次第でしょ。
カナで並べ替えるんだから、インデックスあれば使われる可能性はあるけど
つかってるDBMSもバージョンも書かずにそんな具体例をだされてもな
全件舐めなきゃ「名称 LIKE あ%」は取れないんだから
カナにインデックスあっても無意味
全件舐めた後、並べ替えのためにインデックス使うなんてこともありえない
oracle10g です
集約の単位(group by)が違う2つのSQLがあり、
UNIONを使用して1つにまとめるべきか、2回SQLを投げるべきか
判断に迷っています。
where句が動的になっており、最大で約60〜70万件ほどhitします。
UNIONでレスポンスが悪くなるのってどのくらいの件数に
なった時なんですかね。
union all じゃあ駄目なケースなの?
UNIONだと重複除去のためにソートが入るから
結果セットがメモリに乗り切らなくなったときに大幅に遅くなる
532 :
531:2010/02/14(日) 20:28:05 ID:???
と、思ったけど10gぐらい新しくなるとオプティマイザの動作も変わってるかもしれず
もしかしたら単純にソートしないかもしんないからやっぱやってみないと分からん
パフォーマンス周りはもっと詳細な情報を書いてくれなきゃ答えようがない。
しかも、それも机上の空論で、実際に計測してみなきゃわからないというのが本当のところ。
union all ですむならそっちのほうが速いとしかいえないね。
詳細な情報書かれても答えられるケースのほうが圧倒的に少ないだろ
よって実際に計測してみろってのに同意
explain planの結果とか貼ってもらえれば批評はできるだろうけども
その件数ならunionしないほうがいいと思う
>>534 一般的な傾向の情報は言える。
どのレベルで解決するかで話は違うが最終的には計測しなきゃならんのは同意。
つか、パフォーマンスはこのスレの範囲外だと思うんだがな
そう?
SQLを書く以上避けては通れない話題だし
過疎り気味のスレでわざわざ話題の範囲を狭めることもないと思うけど
書くだけならだれでもできるSQLで、パフォーマンス絡み以外の質問ってあるか?
SQLは全然やさしくないから、このスレがあるんだけど。
別にパフォーマンスの質問だっていいと思うけど
大概は書くだけの方の質問だろ
SQLの書き方でパフォーマンスに大差がでることもあるから
一般論の範囲で、ある程度はこのスレの範疇でもいいかもしれないが
初心者向けのこのスレでやるにはふさわしくはないと思う
パフォーマンスのスレって見当たらないな
ややこしいねん。
SQL関係はここでいいやろ。
煽るやつは無視しろ。親切なやつが1人でもいれば感謝すればいい。
545 :
NAME IS NULL:2010/02/18(木) 21:46:08 ID:LsNyZ5p3
Accessで共通テーブル式?のようなものを実現することはできないでしょうか?
・DB : Microsoft Access 2007
・データ
■Relationテーブル
TaskNo RelationTaskNo
1 2
2 3
2 4
4 5
: :
: :
: :
上記はタスクと、そのタスクに関連するタスクのNoを保持しているテーブルです。
このようなテーブルから、指定したTaskNoに関連するタスクを取得して、
その関連タスクにまた関連するタスクを…といった形でデータを取得したいのです。
以前SQLServerで似たような結果を取得した際は
WITH RELATIONS (TaskNo,RelationTaskNo)
AS
(SELECT TaskNo,RelationTaskNo FROM Relation WHERE TaskNo = 取得したい番号
UNION ALL
SELECT R.TaskNo,R.RelationTaskNo
FROM Relation AS R INNER JOIN RELATIONS AS CTE ON R.RelationTaskNo = CTE.TaskNo)
SELECT * FROM RELATIONS
(当時の環境が手元にないため構文が微妙に間違っているかも…)
どなたかご存じだったら知恵を貸してください。
再帰SQLだから単なるビューでは代用にならんだろうな
VBAでループまわして検索するしかないんじゃないかな
548 :
546:2010/02/19(金) 00:07:46 ID:???
よくみてなかった。ビューじゃだめだね。スマソ
549 :
NAME IS NULL:2010/02/22(月) 16:55:32 ID:2TqP5rDt
■2chテーブル
no name
───
1 ニュース
2 芸能
3 料理
4 音楽
■googleテーブル
no name
───
1 スポーツ
2 事件
3 お笑い
4 宇宙
5 電化製品
MYSQLで質問です。
上記の2つのテーブルからUNION ALLを使用し、限られた件数を抽出したいのですが、
select no,name from 2ch union all select no,name from goole limit 1,3
とした場合、 3件ではなく4件抽出されます。
limit 0,3とした場合はちゃんと3件抽出され、limit 2,3とした場合は5件抽出されます。
2,4で6件、3,4で7件…。
(union allをはずすか抽出件数が総データ数より多い場合は期待通りの動きをしてくれます。)
limit 1,3や2,3とした場合、3件の結果が欲しいのですが、ちゃんと抽出する方法はありませんか?
>>549 select * from (select no,name from 2ch union all select no,name from goole) limit 1,3
ってやったらどうなる?
もともとRDBの観念では物理的な行の順番に意味はなくて
order byを指定しない行の順番は不定なんだが
つまり、order byの無いlimitってどの行が帰るか不定だと思うんだが
それでちゃんと抽出っていえるのかね
多くの実装では主キーの順番で帰ってくるだろうけど
それって保障された動作じゃないと思うんだが
MYSQLではちゃんと保障されてるってならしらね
>>551 私もそれを書き込もうと思ったけどやめた。三つを越えたら面倒だから
あとは捨ててしまえ、というつもりだろうから。
>>551 多くの実装はinsert順に取れると思っていた
インデックス使ったらそれ順で
保証されてなきゃ、怖くて使えません。
経路列挙モデル
ってなんですか?
556 :
NAME IS NULL:2010/02/24(水) 01:31:48 ID:Efiuo6Iv
Mysqlです。
質問なんですが、1回のSql文で2つのテーブルへInsertする事は可能なんでしょうか?
1つ目のInsert文のauto_incrementの主キーのデータを2つ目のテーブルへInsertしたいんですが。
無理。
LAST_INSERT_IDでできそうだけど、無理なのかな
ってああああ。1回のsqlじゃ無理w
わかりました。
考え直します><
外部キーにするとか
考え直すってほどでもなくて、2回投げればいいじゃん?
>>556 サーバーへのリクエスト回数を減らしたいなら、ストアドにでもすればいいんじゃないの?
教えてください
以下のような構造のテーブルがあります。
管理番号-ID - 商品コード1 - 原価 - 価格
トID - 商品コード2 - 原価 - 価格
露D - 商品コード1 - 原価 - 価格
管理番号…
管理番号内の商品コード1〜3で比較して
価格>原価のもののIDを出したいのです
たとえば商品コード1〜3の価格の合計-商品コード1〜3の原価
これをストアド的に書いても良いのですが、いまいちスマートな書き方がわからず
どなたかご教授お願いします
意味がわからん。Group by して Sum(価格) - Sum(原価)?
よくわからんけどたぶんこんな?
select distinct 管理番号, ID from t1 where exists(select count(*) from t1 where 管理番号=管理番号 and ID=ID and 原価 > 価格);
そんな感じなのですが
何でグループバイしていいのか
Sum(価格)-Sum(原価)して管理番号を出すところまでも大丈夫なので
>>567
おおお、ありがとうございます
これでちょっと試してみます。
やりたいのはこんな事か?
select 管理番号,ID from テーブル where 管理番号 in (
select 管理番号 from テーブル group by 管理番号 having sum(価格) > sum(原価)
)
社員
・ID
・名前
・現在の課ID
・その他社員情報いろいろ(各列は独立した要素)
異動歴
・社員ID
・異動番号(社員ID単位の通し番号)
・課ID
のような構造とした場合、このデータベースを使うアプリケーション上では、
社員というオブジェクトがあって、異動歴という可変長配列を、
IDや名前と同じように社員オブジェクトのメンバに持たせたいです。
社員検索のSQLから引っかかった社員オブジェクトを作る際、
WHEREやJOINで社員と異動歴をつなげた状態で検索すると
同じIDの社員レコードが異動歴ごとに連続して出てきてしまいます。
異動歴を固定数の列として社員テーブルに持たせてしまえば
なにも悩まずに1つのレコードから社員オブジェクトを作れるのですが、
このようなケースの場合、異動歴の可変長配列を作るためだけに
異動歴テーブル単体をあとから再検索することになるのでしょうか?
Linqとか使えば出来るけどアプリ側の問題であってここで聞くことじゃない
異動回数が極端に多くないならcase文で1行にしてもいいんじゃない?
LINQもいいけど、社員クラスにDictionary<int,Dictionary<int,int>>メンバがあればいける。
>>572 スレ的には別々に検索して、アプリでどうにかする、JOINして検索して、アプリでどうにかする
しかならんのじゃないかな。
縦を横にするのはDBMSに依存すると思ったし、列の型を配列にするのもDBMSに依存すると思った。
そもそも列に落とし込んだ時点で可変長配列じゃないしな。
もしそれをプログラム側で繰り返し処理するなら、列じゃなくて行でもいいわけだし。
577 :
NAME IS NULL:2010/02/28(日) 01:17:11 ID:+f3l1+Qh
質問します。
・オラクル、バージョンわかりません。すいません。
アップデータ文を使う練習問題を解いていて、どうしてもわからなくなりました。
一つのテーブルないで、ある行(A)を参照して、他の行(B)にその値をいれたいです。
簡単にかくと
A B
1 1
2 1
3 1
4 1
というところを
A B
1 1
2 2
3 3
4 4
としたいのです。
アップデート文を一つ使って、一行分変える事はできるのですが、
全部で100行変えたい場合は、今のままだとSQL文を100回使わないと
変えられません。
知り合いには、SQL分をエクセルにいれて、連続データを作成して・・・
などと言われたのですが、本当にそんなことしないとできないのでしょうか。
全ての行の項目Bの値を項目Aの値にしたいのか?
update テーブル set B=A
って、そういう事じゃないのか?
579 :
NAME IS NULL:2010/02/28(日) 01:36:31 ID:+f3l1+Qh
>>578 全ての行ではないです。説明不足すいません。
その1行に何かを足して、それをAが1〜100までに限定って感じにできますか?
その1行の、その ってなにを指してるんだ?
何かを足しての、何か って何だ?
単にAが1〜100までに限定したいなら
update テーブル set B=A where A>=1 and A<=100
ビトゥィーン使えとかいう意見も聞こえてきそうだが
スペルが怪しげなんで却下するw
581 :
NAME IS NULL:2010/02/28(日) 08:22:16 ID:XgRPAAez
>>580 試してみます。
ありがとうございました!
Between な
使用環境はMYSQLかSQLITEです。
MYSQLで
テーブルAにID,ユーザーID,パスワード
テーブルBにID,カテゴリID,作成者,内容1,内容2...
テーブルCにID,テーブルBのID,内容A,内容B...
このようにテーブルとフィールドを作ったのですが、
ユーザーIDごとにテーブルCの内容が必要になるところで行き詰まりました。
ユーザーが1000人になるとテーブルCが1000個必要になるのですが、
テーブルどんどん増やしていっていいものなんでしょうか・・・?
ユーザーごとにテーブルCだけSQLITEで作るとなんとかなんのかな・・・とか考えたりしてます。
すいません書き込んですぐ
テーブルCにユーザーID入れればいいだけって気づきました。お騒がせしました。
テーブルAがテーブルB、テーブルCとどういう関係があるのかわからん。
作成者とユーザーIDで紐づくってこと?
あと各テーブルが持ってるIDの意味もわからん。
ユーザーIDやらカテゴリIDやらはキーじゃないの?
あ、テーブルAはIDいらないですね・・・
カテゴリIDと作成者は重複することがあるのでプライマリキーできないです。
B-Cにリレーションシップはあるけど、Aが浮いてるじゃん。
>>586 私なら、
テーブルDにID,ユーザID,テーブルCのID
にするけど。何か勘違いしてるのかな。
PostgreSQL8.1なんですけど、Timestamp型(Null許容)の
カラムにインデックス張りました。
で、その項目、Orderby DESCに使ったんですけど、
実行計画みたら、インデックス当たってないみたいでした。
基本的にOrderByのときに指定する項目って
インデックス使われないんでしょうか…。
ORDER BY にインデックスは使わない。
と思っていたが、手元の8.3だと使うんだな。
初めて気づいたはw、どのバージョンからなんだろ。
>>589 組数が少ないのでは。私の8.1で確かめたところ、
700万組から100組選択で1秒以内。
インデックス使用しなくては不可能。
>>590 レスありがとうございます!
そうなんですか…
実は8.4.2にしようかと思ってたとこです
>>591 レスありがとうございます!
確かにレコード数が少ないです
5万組から数百組です
SQL自体は350msec程度なんですが、その発行件数が
多いので、なんとか減らそうと試みてます
Orderby抜いたものを副問合せ化して、抜いたデータに
対して最後にOrderby掛けると350msecが
1msec程度になるのですが、事情があって、
副問合せ化しないやりかたを模索中です
593 :
589:2010/03/03(水) 09:46:35 ID:???
>
>>591 >Orderby抜いたものを副問合せ化して、抜いたデータに
>対して最後にOrderby掛けると350msecが
>1msec程度になるのですが、事情があって、
1msecじゃなくて、10msecです
失礼しました
質問です。mysql 5.0.51a
>>4についてなんですが、
各idに対して最新の5件ずつ抽出するSQLの書き方を教えてください。
595 :
NAME IS NULL:2010/03/04(木) 12:21:36 ID:s26r5JPC
postgresSQL(Ver8.1.18使用)の勉強中です。テーブルeigyo_data カラムdate,syohin,uriage
select date,count(*),sum(uriage) from eigyo_data where date>='2010-01-01' and date<='2010-02-28' group by date order by date;
実行データ
date | count | uriage
2010-01-01 | 30 | 500000
2010-01-02 | 20 | 450000
(略)
として営業データを出しています。
しかし、実際したいのは
date | count | uriage
2010-01-01 | 22 | 320000
2010-01-01 | 8 | 180000
2010-01-02 | 18 | 410000
2010-01-02 | 2 | 40000
(略)
というような日付と商品(syohin)をキーにしてそれぞれ出したいのですが分かりません。
初歩的にsql文を2個書いてそれぞれ where syohin=1,syohin=2とすれば取ってくることは出来ますが、
1文でまとめる事はできますか?どうすればいいでしょうか。
宜しくお願いします。
syohinと日付でgroup by
597 :
595:2010/03/04(木) 12:42:16 ID:???
出来ました。本当に下らない質問で申し訳ございませんでした。
またお世話になるかも知れませんが、その時は宜しくお願いします。
>>594 自分よりも新しいレコードの件数が4件以下なら最新の5件と考える
599 :
NAME IS NULL:2010/03/05(金) 06:12:14 ID:LeZyPZhh
以下のSQLで最新日の2000円以上の売上があった商品を抽出しているんですが、
SELECT * FROM SALES WHERE uriage >= 2000 AND date = (select max(date) from SALES)
「その商品の前回売上があった日の売上額」と「前回の売上と比べてどれだけ増えたのかの増加率」
を出したい場合どうしたらいいでしょう?
商品ID(id) | 日付(date) | 売上額(uriage) | 前回売上額 | 売上増加率
3 | 1267714800 | 2500 | ????? | ?????
4 | 1267714800 | 2800 | ????? | ?????
[日付の形式はunixtimeで、データ登録の際は日付だけ変更させ、時間と秒は常に統一させています]
[mysql 5.1]
>599
mysqlよくしらないけどたぶんこんな感じ
SELECT *,
(SELECT uriage from SALES where date = (select max(date) from SALES where date < t.date and id=t.id)) as 前回売上額,
uriage/(SELECT uriage from SALES where date = (select max(date) from SALES where date < t.date and id=t.id)) * 100 as 売上増加率
FROM SALES as t WHERE uriage >= 2000 AND date = (select max(date) from SALES)
前回の売上がゼロだとゼロ割のエラーが出ることになる
topとかlimitとか使えるならもうちょいスマートに書けるかもしれんが
ああ、今見直したら、外側のサブクエリに and id=t.id つけるの忘れてるわ
ちなみにid,dateで行が一意になるって前提だから、そうじゃないとエラーになるからな
602 :
NAME IS NULL:2010/03/05(金) 19:28:30 ID:oFawM8/f
MYSQLを使っています。
SELECT * FROM aaa WHERE id = ●
この●の部分が複数ある場合、例えば 3,9,14,43,413,545のような場合に
一度に結果を取り出したいのですが、どういう風にかけばいいでしょうか?
他のテーブルにid_list = "3 9 14 43 413 545"
のように入っていているのですが
これをsplitしてid=3 id=9のようにして
SELECT * FROM aaa WHERE id = ●を繰り返すコードにしてるですが
数増えたらとんでもないことになるじゃん・・・って気づきました。
っ IN
つExists
605 :
NAME IS NULL:2010/03/05(金) 20:15:15 ID:oFawM8/f
>>603,603
ありがとうございます。ちゃんと便利巻数あるもんですね
関数ってなんだよ・・・IN句とEXISTS句だろが・・・
いや句じゃなくて述語だが・・・
>600
どうもありがとうございます。
昨日からずっとやっているんですが、ローカルで実行するとロード中のままになり、
どうもうまくいってくれないです。。
自分の書き方がまずい可能性大ですが・・
postgres8.4を使ってます。
レコード数が3件以上あるものを取得したいのですが
どう書けばよいでしょうか。
やりたいことのイメージはこんな感じです。
select recordCD from Table where recordCD in (select recordCD from Table where count(recordCD) > 3);
inでまとめる意味が無かった…
どちらにせよ出来ないから意味はないんですが。
select recordCD from Table where count(recordCD) > 3
こんなイメージです。
HAVING
うおー
迅速なご回答ありがとうございます!
MS Accessなんですが、質問させてください。
<ユーザ情報テーブル>
ID,氏名,住所
1,ユーザ1,青森県〜〜〜
2,ユーザ2,青森県〜〜〜
3,ユーザ3,北海道〜〜〜
4,ユーザ4,宮城県〜〜〜
5,ユーザ5,岩手県〜〜〜
・・・
<都道府県テーブル>
ID,都道府県
1,北海道
2,青森県
3,岩手県
4,宮城県
5,秋田県
・・・
この2つのテーブルから、クエリを使用して、都道府県別のユーザ数を取得(カウント)したいのですが、教えていただけませんでしょうか?
上記の5レコードで説明しますと、
都道府県,人数
北海道,1
青森県,2
岩手県,1
宮城県,1
秋田県,0
という結果を望んでいます。
よろしくお願い致します。m(_ _)m
>>613 SELECT T1.都道府県, COUNT(T2.ID) AS 人数
FROM 都道府県 T1 LEFT JOIN ユーザー情報 T2 ON T2.住所 LIKE T1.都道府県 + '*'
GROUP BY T1.都道府県
>>607 述語INとか言い辛いからIN区 EXISTS区って言ってるわ
616 :
613:2010/03/10(水) 23:09:22 ID:???
>>614 正常にに動作しました!
ありがとうございます!m(_ _)m
もっと勉強しなきゃ…(^^;)
句が正解だろ?
INは演算子だ馬鹿
INやEXISTSは述語
って、流れ見てなかったw
スマソ。
MySQL5.0.77を使っています。
ID | DATE | DATA
--+----------+-----
1 | 2010-03-11 | 10
2 | 2010-03-11 | 5
1 | 2010-03-10 | 50
3 | 2010-03-11 | 40
3 | 2010-03-09 | 70
4 | 2010-03-10 | 35
こんな感じのテーブルから下のように、
日付を降順ソート、その日付の中でDATAを降順ソートしたいのですが、
教えていただけませんでしょうか?
ID | DATE | DATA
--+----------+-----
3 | 2010-03-11 | 40
1 | 2010-03-11 | 10
2 | 2010-03-11 | 5
1 | 2010-03-10 | 50
4 | 2010-03-10 | 35
3 | 2010-03-09 | 70
説明が下手でごめんなさい。
よろしくお願いします。
ORDER BY DATE DESC, DATA DESC
623 :
621:2010/03/11(木) 17:46:12 ID:???
>>622 お答え頂きありがとうございます!
試してみたのですがやはり日付だけが優先されてDATAの方はバラバラのままでした。
DATEがDATETIMEでフォーマットがYYYY-MM-DD HH:MM:SSなのが悪いのかと思って、
DATE_FORMAT( DATE, '%Y-%m-%d' )とSELECTに記入してみましたが駄目でした。
それはDATE_FORMATをORDER BY のほうにも
>>624 なるほど!
考えが足りませんでした。
もっとしっかり考えられるよう気をつけます。
本当にありがとうございました!
いいってことよ
627 :
NAME IS NULL:2010/03/14(日) 20:28:58 ID:fTeg03mG
テーブルA
・社員ID
テーブルB
・社員ID
・資格ID
テーブルC
・資格ID
・資格名
テーブルBとCは内部結合して
テーブルAとテーブルBは外部結合したいのですが
A.社員ID = B.社員ID(+)
B.資格ID = C.資格ID
だとうまくいきません。
どうかご教授願います。
テーブルBとCの結合をサブクエリで先にやればいい
やりたいのは「全社員ごとの所有資格の一覧」の取得だよな?
「資格を持っていない社員」の場合を考えると、
まず A.社員ID とマッチする B のレコードはないが、外部結合で
「B.資格ID が null」とみなした結合結果が得られる。
このとき B と C の内部結合をすると、
「null と = な資格ID である C のレコード」を抽出することになってしまい、
該当なしになるからまずいわけで。
回避するには B と C も外部結合させれば。
まあ、B.資格ID は「必ず」C.資格ID と一致する物しかないことが前提だけど。
まず
>>1を読んでから質問し直せと
(+)による外部結合は結合の順序を制御できないので
思わぬ結果になることがある
(outer)join使えってことだ
631 :
NAME IS NULL:2010/03/15(月) 10:10:21 ID:Khkp/c0m
MYSQL使ってます。
id キーワード
1 りんご
2 みかん
3 りんご
のようにDBに登録されてるキーワードが多い順に例えばヒット件数トップ10のキーワードを
抜き出したいのですがどのように書いたらいいでしょうか?
件数少ないうちは今までにないキーワードがでたら全検索で数えるって繰り替えそうと思ったんですけど、
件数多くなったら効率的に書かないとまずいかなと思いまして。
SELECT COUNT(キーワード), キーワード FROM table GROUP BY キーワード ORDER BY count desc
とか?
LIMITで切りだすと同数が落ちるし、順位付けして取り出すといいかも、これは処理系依存かな
633 :
NAME IS NULL:2010/03/15(月) 11:25:12 ID:Khkp/c0m
>>632 ありがとうございます。まさにやりたい事です。
DBで一文で表せなくてキーワード一度全部抜き出してforループさせるとこでした。
上と同じく、MYSQLでCOUNTについてなんですが、
WHEREの条件に1つも該当しなかった場合は0と出力させたいです。
どのように記述したらよいでしょうか。
COALESCE使えたっけ?
COALESCE(COUNT(なんちゃら),0)とかは?
ってWHEREで絞ったらそもそも出てこないじゃないか
もうちょっと状況を具体的に説明できない?
結局わからんけど、こんな感じ?
SELECT DISTINCT t1.キーワード, COALESCE(t2.count,0) AS total FROM table t1 LEFT JOIN (SELECT キーワード, count(キーワード) FROM table WHERE なんちゃら GROUP BY キーワード) t2 ON t1.キーワード = t2.キーワード ORDER BY total;
>>637 コストかかりすぎ。
EXISTSと相関副問い合わせでいける。
>>634 キーワードがマスタ化されてなけれ、
select
T.キーワード,
coalesce((select count(T1.*) from テーブル T1 where T1.キーワード = T.キーワード), 0) as ヒット数
from テーブル T
group by
T.キーワード
マスタ化されてれば、
select
M.キーワード,
coalesce((select count(T1.*) from テーブル T1 where T1.キーワード = M.キーワード), 0) as ヒット数
from キーワードマスタ M
>>638がいうEXISTSの使いどころはよく分からん。
LEFT JOINの方が圧倒的にコスト少ないんじゃないの?
JOINは全件
十分小さくなった一時表とhash joinするだけだから全然コストかからんよ?
existsや副クエリのだと毎回本体に問い合わせして最悪のコスト
は?
まあこのへんは当人が実測してみないとわからん部分もある
そもそもcount(*)はゼロ件はゼロを返す仕様だと思ったが
MYSQLではそうじゃないのか?
whereで条件絞ったら項目自体ないことになっちゃうじゃない
MySQLのことはよく知らんがどっちでかいてもプランナが適切な方法を選ぶことがある。
結局のところ実行プランを確かめないとなんともいえない。
count(項目)じゃなくてcount(*)だからな
つかホントにMYSQLでselect count(*) where ゼロ件になる条件 は
ゼロ返さないのか?
>>648 どういう場合にゼロを返すことがありえるの?
650 :
NAME IS NULL:2010/03/16(火) 17:41:24 ID:yVYi7DHU
初歩的な質問なんですが
whereで指定する条件って
from句で指定した表を指定する必要があるんですか?
たとえば
selecct A.テストA from A
join B on B.テストB = C.テストC
where (ここで指定する条件にA表についての条件を必ず含める必要がある)
なんで簡単に試せることを
>>649 だからwhereの条件でヒットが0件のときだろう
たとえばselect count(*) from テーブル where 主キー is null とか
だれかMYSQLの実行環境あるやつ試してくれ
MySQL持ちはキチガイなのか?
数秒で確認できる事に何故答えが無い
Count(*)ならNULLが帰る事はありえないだろ
0が返るよ。MySQL5.0.67
COUNTが絶対にNULLを返さないのはSQLの標準仕様な。
SQL初めて1週間で叩き込まれるくらいの基礎。
>>659 そんなのわざわざ数えてたら同類に見えるよ。
スルーしておけばいい。
あほか、最初の方はCOUNT(*)じゃないだろ
WHEREで除外した項目も0を返すカウントってのを見せてくれよw
SELECT COUNT(1) FROM table WHERE 1 = 0 GROUP BY なんちゃら
とかな、最初の方はこういうことを言ってるだろ。
質問させてください
SQLserverを使用しているのですが
SELECT name FROM Table1 WHERE name LIKE '%' + @item + '%'
としたときに、@itemにNULLが入っている場合、全件を返すのですが
この動きは問題ないのでしょうか?
解説しているサイトなどありましたら、ご紹介いただけませんでしょうか
>>663 SQL Serverのことはよく知らないが、
普通 '文字列'+NULL は NULL で
field LIKE NULL は UNKNOWN になって、
全件返すのはおかしい。
@itemに代入するのはホスト言語側かな。
だとすると、そのホスト言語がNULLを代入しているのか、
空文('')を代入してしまっているのかに因る。
SELECT '%' + @item + '%';
を実行してみて、結果を見ればどのように処理されてしまっているのか
わかるんじゃね。(NULLが返るのか、'%%'が返るのか)
SQLServerであれば
SELECT name FROM Table1 WHERE name LIKE '%' + @item + '%'を
SELECT name FROM Table1 WHERE name LIKE '%' + NULL + '%'に書き換えるとゼロ件になる。
@itemにはNULLでは無く空文字が入っているはず。
666 :
663:2010/03/17(水) 14:57:02 ID:???
>>664 >>665 ありがとうございます。
SQLを直接叩いてみたところ、NULLが返ってきまして、原因を調べたところ
ASP.NETのCancelSelectOnNullParameterがFalseになっているのが原因でした
お騒がせしました
ASP.NETはパラメータ周りをグネグネ弄りまくるから、実際に発行されたSQLのトレースがデバッグの第一歩
ASP.NETじゃないな
ADO.NET
ORACLEを使っています。
テストのため2000万件ほどのレコードを1テーブルに投入する必要があるのですが、
どうにもこうにも時間が掛かりすぎてしまいます。
現在はファイルを5分割して、SQLローダを使って投入していますが、それでも丸1日掛かることもザラ…
コミット間隔を見てると300万件超えた辺りから急に遅くなるような気がします。
ダイレクトパスを使ってDBが落ちてしまった経験があるので、出来れば使わずにもっと高速化したいのですが、何か良い方法はないでしょうか?
一度だけ15分くらいで終わったのですが、いったい何が違うのでしょうか?
スレ違い。
邪魔なINDEXを事前に削除しておく
___
(_) /、____\
l| / |::::::::::::::::::::::| 〉
r‐'、_ノ 厶:-v、‐:v=イゝ xく)
T⌒\ |:l '⌒ ⌒Yレく:::∧
|::::::::/ 从" 「 フ ノ:| }::::::〉
\/ }:}>rz< }:! __j/
/V:\_i/ V|V j丁\
∨レヘ:八 /::∧ {:ノ/
/ /::∧:ヘ ∨
/ /::/ '::::. '.
>>670 このスレがデータベース板の総合質問スレだと思ったのですが、違いましたか…申し訳ありません。
よろしければ誘導をお願いしたいのですが、よろしいでしょうか?
672にも無反応だな
ダイレクトパスを使うことと
DBが落ちることの因果関係もわからん
本当か?それ
使い方を間違えていないのに落ちるんなら、バグってんじゃん!という
怒りをこめてサポートに連絡すべきだよな
PostgreSQLだと10万件ずつ200回に分けると速くなったりするんだが。
昔の記憶だからなんともいえないがオラクルもそうだったような。
それはDBの問題というよりOSの問題じゃないのか
Windows XPです。
バッチから、エクスポートするSQLファイルを起動させたんですが、
途中で終了させる方法を教えていただけませんでしょうか。
echo OFF書き忘れました。
完全にスレ違いだがとりあえず最小化して右クリックから「閉じる」でいいだろ
一括コミットならある程度分けたほうが速くなるのはどのDBでもじゃないのか。
まあ分けなくてもコミットだけときどき切ればいい話だけど
>>683 SQL PLUSで既に実行中のエクスポートなんですが
DOS窓閉じたら終わるんですか?
電源ケーブル抜けば、すぐに終わるよ。
Oracle10gを使用しています。
<生徒テーブル>
生徒ID, 生徒名称, クラスID, 好きな教科ID,
1, 鈴木, A, 1
2, 田中, B, 1
3, 佐藤, c, 2
<教科テーブル>
教科ID, 教科名称
1, 国語
2, 数学
3, 理化
<クラステーブル>
クラスID, クラス名称
a, Aクラス
b, Bクラス
c, Cクラス
上記2つのテーブルから、
各教科でクラス毎に何人が好きな教科としているか以下のような形で取得します。
教科, Aクラス内での人数, B〜, C〜, …
国語, 1, 1, 0, …
数学, 0, 0, 1, …
理化, 0, 0, 0, …
ここで、クラスの数が固定の場合のSQLは出来たのですが、
クラス数が可変の場合にデータを取得する方法がわかりません。
ちなみに、教科も可変です。
どなたかご教示願います。
>クラス数が可変の場合にデータを取得する方法がわかりません。
無いです
どうせクラス数なんて大して変わらないんだから、
固定にしちゃえばいいじゃん。
>>688 SQLServerだとpivotでできるけど、oracleに似たような機能があるか調べてみたら?
教科,国語,数学,理化
A 1, 0, 0
B 1, 0, 0
C 0, 1, 0
…
…
とやるわけにはいかんの?
教科も可変ってことだから
やるとしたら
クラス、教科、人数
A,国語,1
A,数学,3
…
…
とかだろうね
クラスなんて、26もないだろうから、A〜Z固定にしちゃえば?
>>695 乙。ならpivotで解決だな。
→→→クラス
↓sum(人数)
↓
↓
教科
>>696 馬鹿は人を見下そうと必死にならなきゃいけなくて大変だな
使えたところでクラスを列挙しなきゃならないことに変わりはないんじゃ
702 :
688:2010/03/22(月) 18:13:11 ID:???
皆さん回答ありがとうございました。
pivotというものが有力そうでしたが、
10gでは使えないみたいですね。
ちなみに私の例えが良くなかったのですが、
クラスも教科も例として挙げただけで、
実際は固定に出来ないデータです。
こういう質問をするときはもう少しわかり易い例にしないといけないですね。
もう少し調べて無理そうなら、データ取得以降に加工する形にします。
いいかもね。
っても横の可変サイズが高々いくらで、それに満たない場合はそこがNULL列となってもよい
なんてのだと標準でかけるからなぁ
それは可変の要件を無理やり固定に変えちゃってるだけだろ
列数が変わるたびにsql文を書き直さなきゃならんのを「列の数が可変な問合せ」とは言わない。
pivotでどう実現するつもりやら
>>706 でも結構そういうのですむ場面が多いように思う。
4半期ごとに横に並べたいとかそういう。
ほんとに横可変ってその見せ方をしてる時点でおかしいんじゃないかと一度は疑ってみるべき。
それでも横可変がいいなら、と続いて。
横持ちは将来に渡って固定であると約束できる時に限ったほうがいいよ。
4半期、月数なんかはOKだろうね。
EXCELにあるピボットテーブル、ACCESSにあるクロス集計だろ。必要な局面結構あるよ。
自分はそのどちらかにデータを持って行って加工してた。
業務だと二言目にはピボットだよな
T-SQL的なもので動的SQL組む手もある
オラクルは知らんが、あるでしょ?
すみません、havingを使えば上手くいくかと思ったのですが、
SQLエラーとなっしまい上手くいきません。
A B C
- - -
1 3 a
1 5 b
2 8 c
2 4 d
2 6 e
3 3 f
3 1 g
上記のようなSAMPLEテーブルがあるとき、
AごとにBが最大となるレコードのCを得たい、
つまり、抽出結果を下記のようにしたいと考えています。
A C
- -
1 b
2 c
3 f
select A,C
from SAMPLE
group by A
having C=MAX(C)
としてみましたが、ORA-00979でエラーとなってしまいます。
助けてください。。
>>713 >>4を参考。
別解で
SELECT a,c FROM Sample AS T1 WHERE b = (SELECT max(b) FROM Sample where a=T1.a );
相関副問い合わせはウンココストだからやめなさい
716 :
713:2010/03/24(水) 15:53:44 ID:???
ありがとうございます!
>>4を参考に実装できました。
ただ、、、
実際には、Cがaかcかgで且つBがxx以下の範囲で最大値を取るもの、
といった挙げた例よりもう少し複雑な条件なのですが、
100万顧客サイトの履歴抽出なので、パフォーマンスが不安です。。(><;
ありがとうございました。
インデックスはっときゃいい
>>716 PostgreSQLやMySQLならJOINも相関クエリもなしで済ませる方法があるし、
きっとOracleにもあるんじゃね。
>>715 やだ、止めないw
>>716の環境・条件次第だが、Sampleテーブルx2がオンメモリで処理できず、
溢れてスワップするようだと、相関クエリの方が速い....(可能性もある)
実行プランも見ずに相関クエリだから遅いとか
どんだけ貧弱なオプティマイザ想定してるんだ
カスクエリを書いてもある程度はオプティマイザにフォローして貰えるからって、何でそんな偉そうなの
SQLServer2005を使用していますが、
一定時間内に「削除」されたテーブル、プロシジャ、ファンクションの各名称を取得するにはどうしたらいいでしょうか?
よろしくお願いします。
>>721 何があったのか、お兄さんに教えてごらん
>721-722
何があったかというかこれから消すんですが、その確証を残すことができないかなぁと思いまして。。。
724 :
NAME IS NULL:2010/03/25(木) 21:15:16 ID:DArwV8ya
そうだ、フロはいらないと
∬ ∬
。。 ・・∧〜∧ ・。。。 ∬
o0o゚゚ (´∀` ) ゚゚oo
。oO ( (( ー----‐ )) ) O0o
(~~)ヽ (´^ヽO,
(⌒ヽ (⌒)(゙゙゙)~
/~゙゙ヾ⌒`ゝ-(~´`(⌒(⌒~ヽ~ ~~ 、、
ゝー ′ '" ``"
>>729 かけるっちゅうのw
少なくともSQLServer2008では実装したぞ。
ヒントをいうと、動的な部分はselect句がかける。
それでは頑張ってくれ。
できないそうです
SQLだけで何とかしようなんて、おこがましいとは思わんかね。
まあアプリ側でやるわな
734 :
729:2010/03/28(日) 18:51:44 ID:???
[[,]...n]のところもじゃないかな
それでも私はSQLで組むんだ!
自分の職のために!
あっちょんぶりけ
739 :
729:2010/03/29(月) 20:52:09 ID:???
JOINを使った結合方法で教えてください
A、B、Cというテーブルがあって
Aに、BとCを内部結合したしたものを外部結合する場合
ちなみに
BとCはkey1が結合条件
AとBはkey2が結合条件
オラクルの(+)を使う場合、
一旦BとCのインラインビュー作って、Aとそれ(BC)を外部結合って
やると思うんだけど。(他にもやり方あれば教えて)
つまり
SELECT *
FROM A、(SELECT * FROM B、C WHERE B.key1=C.key1) BC
WHERE A.key2=BC.key2(+)
JOINだと
SELECT *
FROM B INNER JOIN C ON B.key1=C.key1 RIGHT OUTER JOIN A ON A.key2=B.key2
で同じ結果が得られる?
あと、こんな書き方もあり?
SELECT *
FROM A LEFT OUTER JOIN (B INNER JOIN C ON B.key1=C.key1) ON A.key2=B.key2
>>740 こうゆうこと?
SELECT * FROM A
LEFT OUTER JOIN (
SELECT * FROM B
INNER JOIN C ON B.key1=C.key1
)V ON A.key2=V.key2
>>741 あ、最後のは思いつきで
インラインビュー使わないで↓と同じ結果とるには()使えばどうにかなるのかとおもって書いてた。
コンパイル通るかもわからん
SELECT *
FROM B INNER JOIN C ON B.key1=C.key1 RIGHT OUTER JOIN A ON A.key2=B.key2
があってるとして、Aはメイン結果を想定していたので↑だと最後に出てきてちょいと見た目がやだなぁ と思いまして。
>>740 Oracleは知らんけど、
最後のはPostgreSQLだと意図した通りにいける。
<社員テーブル>
社員番号,名前,ランク,部署コード
-------------------------------
1,鈴木一郎,A,01
2,鈴木二郎,A,01
3,鈴木三郎,B,01
4,鈴木四郎,A,02
5,鈴木五郎,A,03
↑の社員テーブルから、
同じ部署にBランクの社員がいないレコードを
取得したいです。
<結果>
社員番号,名前,ランク,部署コード
-------------------------------
4,鈴木四郎,A,02
5,鈴木五郎,A,03
SELECT *
FROM 社員テーブル
WHERE
部署コード IN(
SELECT 部署コード
FROM 社員テーブル
WHERE ランク= 'B'
GROUP BY 部署コード);
これで一応、データは取れるのですが、
サブクエリを使わずに取れる方法で良い案があれば教えてください。
多分サブクエリを使うのが一番速いと思うけど。
SELECT DISTINCT A.*
FROM 社員テーブル A
LEFT OUTER JOIN 社員テーブル B
ON A.部署コード = B.部署コード AND B.ランク = 'B'
WHERE B.社員コード IS NULL
なお、試してないし自信もない。
>>745 Bランクの社員がいない なのか?
そのSQLだとBランクがいる部署じゃないのか?
ちなみにサブクエリのGROUP BYはいらないが
で、サブクエリで何か問題でもあるのか?
パフォーマンスなら、相関サブクエリにすれば早くなるかもしれんが
オプティマイザが十分賢ければスピードは変わらない
748 :
NAME IS NULL:2010/04/07(水) 10:12:13 ID:zNyGh9FV
日付データを保持するテーブルを作成したいと思ってます。
はじめ、DATE型でやろうと思っていたのですが
年月日のデータと月日データを登録することが考えられます。
DATE型は0000-00-00 と年月日の登録が必要になります。
こういう場合には、 DATE型で作成し、年が無い場合にはありえない数字を登録
するやり方が多いのでしょうか?
それとも、年データと月日データを分けて数値として保持するやり方が多いのでしょうか?
助言お願いします。
DATE型を使って
・対象業務的にありえない年を想定できるなら、それを入れる
・年が有効かどうかを示すbooleanなカラムを付ける
DATE型をあきらめて
・年データと月日データを分けて数値として保持する
さて他には?
access2000、SQLServer
年月日と金額を持ったテーブルがあります。前年度合計と今年度合計を出したいのですが、
年月日 金額
20080402 200
20080404 200
20090401 100
20090401 500
20090404 300
とあった場合、↓にしたいです
年月日 今年度合計 前年度合計
20090401 600 0
20090402 0 200
20090404 300 200
両年度金額が無い場合は表示無しで、年月日は8桁の数値型です。
うるう年は考慮しなくて構いません。
よろしくお願いします。
すみませんズレました
年月日 金額
20080402 200
20080404 200
20090401 100
20090401 500
20090404 300
とあった場合、↓にしたいです
年月日 今年度合計 前年度合計
20090401 600 0
20090402 0 200
20090404 300 200
こんな感じかな。
select coalesce(a.年月日, b.年月日) as 年月日, isnull(sum(a.金額), 0) as 今年度合計, isnull(sum(b.金額), 0) as 前年度合計
from
(select 年月日, 金額 from t_9_750) a
full join
(select 年月日 + 10000 as 年月日, 金額 from t_9_750) b
on a.年月日 = b.年月日
where coalesce(a.年月日, b.年月日) between 20090101 and 20091231
group by coalesce(a.年月日, b.年月日)
order by coalesce(a.年月日, b.年月日)
同じ発想で
select coalesce(a.年月日, b.年月日) as 年月日, isnull(今年度金額, 0) as 今年度金額, isnull(前年度金額, 0) as 前年度金額
from
(select 年月日, sum(金額) as 今年度金額 from t_9_750 group by 年月日) a
full join
(select 年月日 + 10000 as 年月日, sum(金額) as 前年度金額 from t_9_750 group by 年月日) b
on a.年月日 = b.年月日
where coalesce(a.年月日, b.年月日) between 20090101 and 20091231
order by coalesce(a.年月日, b.年月日)
というのもあるけど、多分前者の方がいいかな。
なんかおかしいか?
SELECT
年月日, SUM(今年度) AS 今年度合計, SUM(前年度) AS 前年度合計
FROM
(
SELECT
年月日, 金額 AS 今年度, 0 AS 前年度
FROM テーブル
UNION
SELECT
年月日 + 10000 AS 年月日, 0 AS 今年度, 金額 AS 前年度
FROM テーブル
) T
GROUP BY 年月日
おおー。なるほどです。
757 :
750:2010/04/08(木) 10:40:11 ID:???
無事出来ました。
勉強になりました、どうもありがとうございました。
結局、どのやり方で?
>>752 もともとのテーブルの日付に重複があるから前者はダメだね
>755って、UNIONじゃなくてUNION ALL じゃないとまずくないか?
同一年月日で同一金額のレコードが無いとは保障できないだろう
あらほんと。十分ありえるね
SQL初心者ですが、
SELECT文でのデータ検索で、BLOB型のバイナリデータをキーにしてデータを検索したいのですが、
可能でしょうか? どのようにクエリを書けばいいのでしょうか?
無理じゃない?
実装によるんじゃない?
対象のDBMSをちゃんと書け
766 :
NAME IS NULL:2010/04/11(日) 18:56:13 ID:T8XQsdpG
・DBMS名とバージョン:MySQL 5.1.45
・テーブル名:USER_POINT
NAME POINT
Aさん 50
Bさん 60
Cさん 70
・欲しい結果
AさんのPOINTよりPOINTが高いレコードの数
・説明
SELECT COUNT(POINT) FROM
(SELECT POINT, BASIS FROM
USER_POINT,
(SELECT POINT AS BASIS FROM USER_POINT WHERE NAME = 'Aさん') AS BASE
) AS COMPARE
WHERE POINT > BASIS
上記のSQLでとれるのですが、AさんのPOINTを全レコードと結合してCOMPAREを作ってるのが凄く無駄に思えます。
もっと効率のいいSQLにできないでしょうか?
SELECT COUNT(*) FROM USER_POINT WHERE POINT > (SELECT POINT FROM USER_POINT WHERE NAME = 'Aさん');
MySQLは知らないけどいかんの?
素直にするなら
select COUNT(*)
from USER_POINT a, USER_POINT b
where a.point > b.point and b.name = 'a'
かなぁ。
769 :
766:2010/04/11(日) 19:47:40 ID:???
ありがとうございます!どっちもできました。
770 :
763 :2010/04/12(月) 01:36:47 ID:???
使用してるのはSqliteです。
ファイルのハッシュ値をバイナリ(BLOB型)で入力して、それをSELECTで検索するようなことをしたいのですが
できるのかどうか自体を悩んでいます。
ハッシュ値を格納するのにBLOB型じゃなくてcharにすればいいんでないの
できるかどうか聞いてるのにその答えはおかしいだろ。
つっても俺もSQLiteは分かんないから答えらんないけど。
SQLiteスレで聞いたほうがいいんじゃね?
SQLite 3.6.23.1で試してみたらできる。他のRDBMSは手元に環境がないので試してない。
ただ、俺だったら
>>771が言うようにTEXTにして格納するけどな。
以下、検証コード。Perl5.10.1
use feature qw/say/;
use DBI;
use Digest::SHA;
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', '', '') or die $!;
say $dbh->{sqlite_version};
$dbh->do(<<'__SQL__');
CREATE TABLE testdb (
pk BLOB PRIMARY KEY,
val INTEGER
);
__SQL__
my $sth = $dbh->prepare('INSERT INTO testdb (pk, val) VALUES (?, ?)') or die $!;
my $d = Digest::SHA->new;
for (0..99) {
my $s = $d->add($_)->digest;
$sth->execute($s, $_);
$d->reset;
}
$sth->finish;
$sth = $dbh->prepare('SELECT val FROM testdb WHERE pk = ?');
for (0..9) {
my $s = $d->add(int rand 100)->digest;
$sth->execute($s);
say $sth->fetchrow_array;
}
$sth->finish;
$dbh->disconnect;
BLOBにしてインデックス効くのかよと思ったが最近のSQLiteは
インデックス付きカラムにBLOBもありなのね。
でもやはり770は何故BLOBなのか理由を明らかにすべきだと思う。
文字列ないし固定長整数でいいじゃんと思う。
ハッシュ値、ってものをよくわかってないんだろ。
例えば、都道府県名が保存してあるフィールドに
温泉万歳北海道編 など、どこかに都道府県名が入っているかもしれない
文字列を与え、その文字列に都道府県名が入っているかどうかを
判断させることってできますか?
【質問テンプレ】
・SQLServer 2005(spなし)
・A, B, C, D・・・列があり、全て文字型(レコードは数値のみ)。
A, Bがプライマリーキー
・以下の条件を満たすA, Bの値
Cが'04', '08', '09'のいずれかの行の中で、
Dの値が最小のレコードのA, Bの組み合わせのうち、
Bの値が最小のレコードすべてのA, B
・説明
自分でsqlを作成すると、同じSELECT文が何度も出てきてしまいます。
もっと重複のない文にできないのでしょうか?
select A, MIN(B) as B from
(
(select A, B, D from foo where C in ('04', '08', '09')) T1
inner join
(
select A, MIN(D) as D from foo where C in ('04', '08', '09')
group by A
) T2
on T2.A = T1.A and T2.D = T1.D
) T0
group by A
>>776 都道府県名が保存してあるフィールドに温泉万歳と入ってるのがよくないようにおもうけど
まぁ検索はできるよ
>>777 > Dの値が最小のレコードのA, Bの組み合わせのうち、
なのにgroup by Aなのは大丈夫なの
具体例も挙げずにごめん。
select A, MIN(B) as B from
(
select A, B, MIN(D) as D from foo where C in ('04', '08', '09')
group by A, B
)
group by A
ではだめなのかな、というところです。
781 :
776:2010/04/13(火) 18:10:29 ID:???
すみません。
select * from data where pref like '温泉万歳北海道編'
pref に都道府県名が入っています。
'温泉万歳北海道編'などの文字列にprefのいずれかを含んでいるかを
判定したいのです。
このSQLではだめなのはわかりますが、どうすればよいのでしょうか。
説明べたで申し訳ないです。
>>781 select * from data where '温泉万歳北海道編' like '%' + perf + '%'
じゃないの?
>>777 Cが'04', '08', '09'のいずれかってのはどこまでかかるんだ?
素直に考えると
select A,MIN(B) from foo
where D=(select MIN(D) from foo where C in ('04', '08', '09') )
group by A
か
select A,MIN(B) from foo
where C in ('04', '08', '09') and D=(select MIN(D) from foo where C in ('04', '08', '09') )
group by A
じゃないかな。ためしてないけど
>>780 Dが最小じゃないA,Bの組み合わせ拾うんじゃね?
784 :
777:2010/04/13(火) 21:12:27 ID:???
>>779 うーん、そもそもそこから怪しいかも。
1. とりあえず「Cが('04', '08', '09')の奴のA, B, D」を取得する。
2. この中で、各Aに対して最小のDを取得する。
3. 1とは別の「Cが('04', '08', '09')の奴のA, B, D」を取得する。
4. 3のA=2のA、3のD=1のDを満たすA, Bを取得する。
5. 4で取得した各Aに対して、最小のBを取得する。
という考え方だと、group byはAでいい気がします。typoがなければ。
785 :
777:2010/04/13(火) 21:14:33 ID:???
A, B, C, D
1, 1, 01, 20
1, 2, 02, 11
1, 3, 04, 22
1, 4, 04, 21
1, 6, 08, 12
1. 7, 04, 33
1, 8, 09, 12
2, 1, 04, 80
3, 1, 03, 01
786 :
777:2010/04/13(火) 21:16:04 ID:???
の場合、(1, 6)と(2, 1)を取得したいです。
ただ、('04', '08', '09')も結構複雑なSQLで取得しているので、
できればC IN 〜という句をなんども書きたくないんです。
sqlって難しいです。
787 :
777:2010/04/13(火) 21:32:27 ID:???
>>783 日本語もむずかすぃ。ネイティブなのにな。
ごめんなさい、うまく伝えられてなかったようで。
どう説明すればいいのか悩んでる途中ですが
Viewとかが作れればもっと単純に説明できるかもしれません。
View1, fooのうち、Cが('04', '08', '09')
select * from foo where C in ('04', '08', '09')
View2, View1のうち、同じA内でDが最小のもの。
select * from View1 where D = (select min(D) from View1 m where m.A = View1.A)
View3, View2のうち、同じA内でBが最小のもの。
select * from View2 where B = (select min(B) from View2 m where m.A = View2.A)
最終的に欲しいもの, View3のAとB
select A, B from View3
こんな感じだと思います。
>>787 その条件ならたぶん
select A,min(B) from foo T
where C in ('04', '08', '09') and D=(select MIN(D) from foo where A=T.A and C in ('04', '08', '09'))
group by A
SQLServerの共通テーブル式 (WITHなんちゃら)って2008からだっけ?
2005でも使えるなら、ビューを作らなくてもビュー使うのと同じようにできる
それ以外でCの条件を1回で済ます方法はちょっと思いつかなかった
まあ、Cの条件がSQLで取得されてるなら、リテラルで比較なんてせずに、
そのSQLも結合させるのが本筋だと思うが
789 :
NAME IS NULL:2010/04/14(水) 01:53:04 ID:NwQAw5NG
[TABLE1]
key1 key2 data
------------------------------------
AAA 01 zzz
AAA 02 yyy
BBB 01 xxx
BBB 03 www
CCC 02 vvv
DDD 03 uuu
[TABLE2]
key1 data
------------------------------------
MMM 01&02
NNN 01only
OOO 02only
このようなテーブルから、以下のデータを抽出したいです。
key1 key2 data1 data2
---------------------------------------------
AAA 01 zzz 01&02
BBB 01 xxx 01only
CCC 02 vvv 02only
TABLE1のKEYはkey1とkey2です。
このTABLE1から、各key1に対して一意にデータを抽出します。
抽出はkey2の値が01と02のもののみを対象とし、
01があれば01のデータを、なければ02のデータを引っ張ってきます(data1)。
さらに、各key1におけるkey2の状況?をdata2として付加したいです。
ここではTABLE2に格納されていることにしましたが、
条件文で単に文字列を設定するのでも構いません。
SQLServer2000を使用します。
どのようなSQLを書けばよいでしょうか?
よろしくお願いします。
>>789 こんな感じ?
select key1,key2,data as data1,'01&02' as data2 from TABLE1 T
where key2=01 and exists (select * from TABLE1 where key1=T.key1 and ke2=02)
union
select key1,key2,data as data1,'01only' as data2 from TABLE1 T
where key2=01 and not exists (select * from TABLE1 where key1=T.key1 and ke2=02)
union
select key1,key2,data as data1,'02only' as data2 from TABLE1 T
where key2=02 and not exists (select * from TABLE1 where key1=T.key1 and ke2=01)
ああ、key2がなぜかke2になってる。試してないのがまるわかりだなw
union嫌いな人向けにこんなのも考えた
select key1,key2,(select data from TABLE1 where key1=T.KEY1 and key2=T.key2) as data1,
CASE key_count WHEN 2 THEN '01&02' ELSE
CASE key2 WHEN 01 THEN '01only' ELSE '02only' END
END as data2
from (
select key1,min(key2) as key2,count(*) as key_count from TABLE1 where key2 in(01,02) group by key1
) T
これも試してないから間違っててもしらない
俺はWHEN CASE嫌いだから素直にunionでやる
union all を知らない人間の多いこと
今回のだとソートのコストが増えるだけじゃないか?
UNION ALLでソートのコストが増えるとはこれいかに。
UNION ALLを使わないことにより、だよ
796 :
NAME IS NULL:2010/04/14(水) 20:57:00 ID:UFO6GLca
ID|value|日付 |etc
01|100 |2010/4/5|10
01|150 |2010/4/6|20
02|100 |2010/4/5|30
02|160 |2010/4/6|40
各ID、特定の日にち2010/4/6のvalueから1日前のvalueを引いた値をdiff列に出力
ついでに2010/4/6のetcを出力
ID|diff |日付 |etc
01|50 |2010/4/6|20
02|60 |2010/4/6|40
お願いします!
使う必要のある文法?のヒントだけでも、教えていただければ有り難いです。
select A.ID,
A.value - B.value as diff,
A.日付,
A.etc
from TableName A
inner join
TableName B
on A.ID = B.ID
and A.日付 - 1 = B.日付
where A.日付 = '2010/4/6'
inner joinはだめだろ。
レコードが毎日あるとは保証されていない。
799 :
796:2010/04/15(木) 01:13:32 ID:5HAGZ3rQ
>>797 ありがとうございます!
表の結合に同一テーブルを使ってASで名づけるという発想がなかったのでとても勉強になりました!
気持ちよすぎるぐらい理解できました。
>>798 試しにいくつか試してみましたが、期待していた通りの動作でした。
1日前の日付が存在しない場合は出力しなくてもよかったです。
問題提示が雑すぎたみたいです。すいませんでした!
800 :
777:2010/04/15(木) 11:10:46 ID:???
>>788 やっぱりCの条件は2回でてきますか・・・
Cの条件部分もsqlで書いてるんですが、
そのsqlが50行くらいなのでそれを2回かくのがやだなあと思ってたんですよ。
ありがとうございました。
801 :
NAME IS NULL:2010/04/15(木) 12:36:59 ID:4isnBhcn
SQLserver2005です。
割と大量のテーブルを内部結合なり外部結合なりするとして、
FROM句とWHERE句が遠く離れるのが嫌だという理由で、
SELECT
...
FROM (
SELECT
...
FROM [A]
WHERE
...
) AS [A]
INNER JOIN [B]
...
てな具合にAを先に絞っちゃう書き方ってデメリットはありますか?
onで絞ったら?
803 :
801:2010/04/15(木) 13:21:57 ID:???
>>802 FROM [A]
INNER JOIN [B]
ON
<AとBの結合条件>
<AのWHERE句的なもの>
って事ですか? あー、なるほど…
でもなんかちょっとモヤモヤ感があります。
JOINのときはテーブル名指定部分と条件の記述箇所が近いのに、
FROM句で指定するテーブルの抽出条件であるWHERE句が遠いのが
なんか解せないのです。
結合しただけのビューを作るとか
WHERE句に指定された条件文は例の中のテーブルAだけにではなく
A, B, ...等々を結合した結果の大きなテーブルTに対して適用される
ものだからズラズラINNER JOINが連なった後にWHERE句が来るのは
不思議じゃないし場合によってはAの位置から遠くなるのも仕方が
無いのでは。
もともとの質問に対する答えとしては、同じ結果が出るのであれば
あとはオプティマイザの問題。実際のDB相手にクエリを走らせて
実行計画を見てみないことには何もわからない。
806 :
801:2010/04/15(木) 14:37:49 ID:???
>>805 JOINするときはそのテーブルへの条件はON句に書いて結合時に絞れ、
てな事を常々言われていたので、大きなテーブルTの条件≒Aへの条件
になっていました。オプティマイザに食わせてみます。
お手数をおかけしましたm(_ _)m
常々言われてたようには見えなかったけどな
>>800 だから共通テーブル式使えって言ってるだろうが
つかその50行のSQLに>777の条件も混ぜればいいだけなんだが
PostgreSQL 8.3.7
foo=# SELECT createtime,updatetime from unkou ;
createtime | updatetime
------------------------+-------------------------
2010-04-01 10:00:00,000 | 2010-04-01 10:01:00,000
これを抽出されないようにするにはどう書けばよいですか?
新規作成してから数秒ラグが発生する仕様のようで
"createtime!=updatetime" ではできませんでした。
なんかよくわからんが抽出されなくなるクエリをいくつか
select createtime, updatetime from unkou where createtime = updatetime;
select createtime, updatetime from unkou where createtime <> '2010/4/1 10:00:00';
select createtime, updatetime from unkou where 1=0;
-- select createtime, updatetime from unkou;
811 :
NAME IS NULL:2010/04/17(土) 00:50:46 ID:qlaUhD0O
それぞれ二つのテーブルから抽出してソートしたデータを等価結合したいのですが一つの文で作成可能ですか??
普通にjoinしてソートじゃだめなの?
<基本テーブル>
ID | 材料1ID | 材料2ID | 材料3ID
----------------------------
1 | 123 | 234 | 123
2 | 234 | 123 | 589
<材料テーブル>
材料ID | 材料名(文字列)
---------------------
123 | 123の名前
234 | 234の名前
589 | 589の名前
<結果>
ID | 材料1IDの材料名 | 材料2IDの材料名 | 材料2IDの材料名
----------------------------------------------------
1 | 123の名前 | 234の名前 | 123の名前
2 | 234の名前 | 123の名前 | 589の名前
JOINを使ってみたんですけど、SELECTの所はどう書けばいいのでしょうか?
三つ分書けばOK
JOINで悩まなかったのなら、SELECTでも悩まなくてもいいと思うんだが…
SELECT
tb.id,
tm1.name AS 材料1IDの材料名,
tm2.name AS 材料2IDの材料名,
tm3.name AS 材料3IDの材料名
FROM
基本テーブル AS tb,
材料テーブル AS tm1,
材料テーブル AS tm2,
材料テーブル AS tm3
WHERE
tb.材料1ID = tm1.id
AND tb.材料2ID = tm2.id
AND tb.材料3ID = tm3.id
>>814-815 ありがとうございます。無事に動きました。
今朝MySQL始めたばかりで、AS句すら理解しておりませんでした。
[テーブルA]
ID, AGE, GROUP
--- ---- -----
1 , 10, A
2 , 20, A
3 , 30, A
4 , 20, B
5 , 40, B
6 , 60, B
7 , 10, C
8 , 30, C
9 , 50, C
10 , 70, C
↑のテーブルから、
各GROUP毎にAGEで順位付けした結果を取得したいです。
<結果>
ID, AGE, GROUP, RANK
--- ---- ----- -----
1 , 10, A 1
2 , 20, A 2
3 , 30, A 3
4 , 20, B 1
5 , 40, B 2
6 , 60, B 3
7 , 10, C 1
8 , 30, C 2
9 , 50, C 3
10 , 70, C 4
GROUP BYを使ってみたのですが、
どうも見当違いのSQLになってしまって。。
すいませんが、どのようなSQLを書けばよいかご教示ください。
>>817 select ID,AGE,グループ,(select count(*)+1 from テーブルA where グループ=t.グループ and AGE < t.AGE) as ランク
from テーブルA t
GROUPは予約語だから項目名に使わない方が無難
RANKは予約語じゃないかもしれないけど使わない方が無難
>>817 SELECT ID, AGE, GROUP, ROW_NUMBER() OVER ( PARTITION BY GROUP ORDER BY AGE ) AS RANK
>>777 もう見てないと思うけど
WITH ABD( A, B, D ) AS ( SELECT A, B, D FROM foo WHERE C IN ( '04', '08', '09' ) )
SELECT A, MIN(B) FROM foo ABD AS AB WHERE D = ( SELECT MIN(D) FROM ABD WHERE A = AB.A ) GROUP BY A
なんで今すでに過ぎた話題であるWITH句を用いたSQLを出したの?
mysql 5.1.38
table inputdaka
code int
inputdate date
data1 int
data2 int
指定された日(その日が存在しなければ直前の日付)及びその前日の
データを得る
select inputdate,data1,data2
from inputdaka
where code= 9999
and
inputdate <= '2010-04-10'
order by inputdate desc limit 2;
mysqlで、こう書きましたが、limitが使えないRDBでは
どうなるんですか?
こんなんとか、まあいろいろやりようはあるわな
select inputdate,
data1,
data2
from (select row_number() over (order by inputdate desc) as RN,
inputdate,
data1,
data2
from inputdaka
where code= 9999
and inputdate <= '2010-04-10')
where RN <= 2
order by RN
;
825 :
823:2010/04/21(水) 19:49:59 ID:???
ありがとうございます。
ところで、row_numberってOracle固有ですよね。標準SQLでは無理でしょうか?
指定日未満でフィルタして自己結合して自レコードよりも日付の新しいレコードが1件以下のものを取得する
ごめん補足
順位を出すSQLと同じような感じだよ
こんな感じ?
select A.inputdate,
A.data1,
A.data2
from inputdaka A
inner join
inputdaka B
on A.code = B.code
where A.code = 9999
and A.inputdate <= '2010-04-10'
and A.inputdate < B.inputdate
group by A.inputdate,
A.data1,
A.data2
having count(*) < 2
;
829 :
828:2010/04/21(水) 20:03:35 ID:???
間違えたかも
途中の < が2箇所とも <= かな
830 :
823:2010/04/21(水) 20:44:42 ID:???
ありがとうございます。
>>828のでエラーが出てたりしてますんで、時間がかかったりするかもしれませんけど(アセ
>>826を手がかりに自分で勉強してみます。
すばらしい姿勢だな
がんばれ。
>>829 根本的に間違えてるか勘違いしてる気がする
そもそも
>>823の質問が「指定された日(その日が存在しなければ直前の日付)及びその前日の」
の「その前日」に「指定された日」が含まれて良いのか
単純に「その前日」なのか「その前日が存在しなければさらのその直前の日付」なのか
単にlimitで切ってるSQLと文章で説明している条件が違いすぎる
ちなみに、row_number()はSQL:2003に入ってる。
4/8 4/9 はあるが、 4/10のレコードがない状態で4/10を指定すると
・4/10がないので前日=4/9
・4/10の前日=4/9
を取得したいという要求ではないか?ということだよね。
どんだけ意図を汲み取れないんだよ
>>833 4/10が2件以上あったら4/9は引っ張る必要はあるのか?
3件以上の場合でも、2件だけで良いのか?
「inputdate」に時間は含まれてないのか?
('2010-04-10' は '2010-04-10 00:00:00' だから
'2010-04-10 08:00:00' であれば、指定の日付だけど除外して良いの?)
とか、
>>823の日本語の文章はあいまい過ぎる。
単にlimit使ってるSQLが正しいなら、それでも別に構わんが
それでも、
>>828は「limit 2」と同意ではないしな。
835 :
823:2010/04/22(木) 07:23:45 ID:???
ご指摘、確かに。
一応補足(というか、後出しになってしまってすいません)ですが、codeとinputdateをあわせてPKにしています。
よって、同じcodeでinputdateは必ず1件です。
jこういう重要なデータを言わなかった事、あやまります。
>>835 limitを使わないってことは、mysql使わないってことなの?
その場合、環境によっては、date型に時間を含むことがあるから気をつけた方が良いよ。
>>835 ほんとまじめだね。
こういうぐだぐだ論はほっといたらいいと思うよ
Table1
|ID|Score|Div1|Div2|
|1 |0.2 |1 |1 |
|2 |0.3 |1 |2 |
|3 |0.4 |1 |3 |
|4 |0.2 |1 |4 |
|5 |0.1 |2 |1 |
|6 |0.3 |2 |3 |
Table2
|ID|Div1|
|1 |1 |
|1 |2 |
|1 |3 |
|2 |2 |
こういう構成で、約2000万行のTable1と約100万行のTable2について
1.Select Div1, Score From Table Where Div2 = 2 or Div2 = 4;
2.Select Div1, Score From Table Where Div2 = 5 or ... or Div2 = 9;
:
:
N.・・・・;
このようなN個のクエリを発行し、
得られる全ての結果から共通するDiv1の値を含む行のみを抽出、
得られた共通するDiv1の値を使ってTable2にjoinし、
Table2のIDごとのScoreの合計値を取得したいのです。
(Score値が複数回加算されるのは仕様です)
手続き的に処理をしているのですが、どうにも遅く
一回のSQLで素早く取得する方法はありませんでしょうか。
どうかお願い致します。
ちょっと情報が足りないのだけど、
Q1. N個のクエリって、Nってどれぐらいのオーダー?
Q2. 1個のクエリでヒットするTable1の行数って平均どれぐらい?
Q3. そもそもN個のクエリそれぞれのWHERE文ってどうやって
作っているの?
あえてエスパーしてみると、Nが数十とかいうオーダーならN個の
クエリをUNION ALLで繋げてTable2とJOINして、Table2.IDで
GROUP BYしてTable2.DIV1のSUMをとるのを試してみると良い
と思うけど。
あとQ3に関連して各クエリのWHERE文を同じDB中の別のテーブル
を読んで作っているようなら、ここも含めてクエリに取り込めない
か考えると良いと思う。
distinct したものを (ここは不要かも)
union allして
having でフィルタリングして
という手順が必要じゃないかな。
842 :
839:2010/04/28(水) 17:45:59 ID:???
>840,841 返信ありがとうございます。
>Q1. N個のクエリって、Nってどれぐらいのオーダー?
理論上無限に行えますが、実際使われるのは2〜4個程度です。
5回という制限を設けても運用上あまり問題ない感じです。
>Q2. 1個のクエリでヒットするTable1の行数って平均どれぐらい?
平均だと2・3千件ですが、1件の場合も数万件の場合もあります。
(1万件以上ヒットするDiv2の値は最初のWhere句に入れないことも検討しています。
精度が下がるのでできればやりたくはないといったところです)
>Q3. そもそもN個のクエリそれぞれのWHERE文ってどうやって
> 作っているの?
for(i = 0; i < N; i++){ SQL発行 }
と結果をプログラム側でループして作って、
結果を受け取って、ユニーク処理して...です
ひとまず、union all と distinct と havingを調査してみます。
抽出条件がdiv2しかないならN個のクエリがそもそも一つにまとまると思うんだけど、
そうじゃないからN個なんだよねきっと。
こういうこと?問題の解釈が違う?
select B.ID, sum(A.sum_sum_Score)
from (select Div1, sum(sum_Score) as sum_sum_Score
from (Select Div1, sum(Score) as sum_Score From Table1 Where Div2 = 2 or Div2 = 4 group by Div1
union all
Select Div1, sum(Score) as sum_Score From Table1 Where Div2 = 5 or ... or Div2 = 9 group by Div1
union all
・・・
)
group by Div1
having count(*) = N
) as A
inner join
Table2 as B
on A.Div1 = B.Div1
group by B.ID
;
>>839 1回のSQLで済ますには、N個の条件を1回で指定できないとダメだぜ
ホストアプリでループまわして手続き的に処理して遅いって話だが、
なんの処理が時間かかってるかちゃんと分析してみたか?
N個のクエリ結果をホストアプリで取得して操作する時間が大半じゃないのか?
この条件で俺がやるならDB側にワークテーブル作る
(ID,Div1,Score,N)ってワークテーブル作っといて
insert into ワークテーブル select Table2.ID,Table1.Div1,Score,1 from Table1 join Table2 on Table1.Div1=Table2.Div1 where 条件1;
insert into ワークテーブル select Table2.ID,Table1.Div1,Score,2 from Table1 join Table2 on Table1.Div1=Table2.Div1 where 条件2;
ってなSQLを条件Nまで実行
select ID,sum(Score) from ワークテーブル where ID in
(select ID from (select distinct ID,N from ワークテーブル) t group by ID having count(*)=N)
group by ID ;
ってなSQLで合計取る
試してないのでミスがあっても責任はとらん
847 :
NAME IS NULL:2010/04/29(木) 13:48:11 ID:KPdM3Kie
SQLってコマンドを大文字、引数を小文字って分けるじゃないですか
あれってみんなどう打ってるんですか?
(1) CAPS LOCKで大文字/小文字を打ち分ける
(2) SHIFTを押しながら大文字を打つ
(3) そもそも大文字/小文字を分けずにそのまま小文字で打つ
一般的にはCAPS LOCKかなと思うんですけど結構面倒臭くて…
でもSHIFTを押し続けるには長過ぎる気もするし…
そろそろ自分のスタイルを確立したいなと思います
マジレスキボンです
スレ違い
849 :
NAME IS NULL:2010/04/29(木) 13:54:38 ID:KPdM3Kie
>>848 ではどのスレで?
誘導までして一人前です
>一般的にはCAPS LOCKかなと思うんですけど
(´-`)
851 :
NAME IS NULL:2010/04/29(木) 14:17:32 ID:KPdM3Kie
>>850 なに?違うの?
オノレがどう打ってるか言いやがってください
俺は予約語は小文字
項目名とかは大文字だけどだいたいテーブル定義なんかからコピペ
むしろ大文字で書かず、清書するときにいろいろ書き直す
>>847 全部小文字で書いてフォーマッタにぶっこむに決まってんだろダボスケが
850じゃないけど、CapsLockは俺もありえんわ。
別にダメって言う訳じゃないけど、それが「一般的」だったらショックうける。
でも考え直してみたら、SQLに関して言えば、ありっちゃありか?
今は、SHIFTキーかあとから清書(toupperショートカット)
大小の使い分けはジョー・セルコに倣っている。
と言うかお前らって手でチマチマ整形してんの?
給料泥棒にも程があるんだが
857 :
847:2010/04/30(金) 07:29:56 ID:???
>>852 ん?「予約語」の方を小文字で!?
自分は「予約語は大文字で、項目名は小文字で」と習いましたけど…
>>854 フォーマッタ凄し!
http://db2.jugem.cc/?eid=1254 スペシャルサンクストゥユーです
これは全部大文字に変換されてますね…
>>855 でも、
CREATE TABLE foodslist
(id INTEGER NOT NULL AUTO_INCREMENT,
foodname VARCHAR(30),
PRIMARY KEY(id));
みたいなんだと大文字の方が多いんでCAPS LOCKの方が便利じゃないですか?
(当然、上のはCAPS LOCK ONで打ちました)
ジョー・セルコって名前すら知らなかったですけど、業界のルールがあるなら従いたいです
858 :
855:2010/04/30(金) 09:04:41 ID:???
>>857 たしかに大文字多いな。だからありか!? と本当に思った。
普段大文字を打つ機会が少ない、せいぜい頭文字ぐらいだろ、
だから指が慣れていないだけなんだろうけど、
CapsLockが一般的と言われるとカルチャーショックを覚えるよ。
ジョー・セルコの書き方が業界標準かどうか知らないけど、
予約語、標準関数は大文字、
フィールドは小文字、
テーブルは頭文字のみ大文字。
SELECT column1,MAX(column2) FROM Table1 GROUP BY column1;
あ、この書き方をジョー・セルコが始めたのか、ジョー・セルコが誰からかに倣ったのか忘れた。
あくまで俺が勝手にこの書き方を倣っているだけだからな。ツッコミは無用。
859 :
839:2010/04/30(金) 09:24:19 ID:???
>843, 844, 845, 846
みなさんありがとうございます。
一昨日の方法含め、
順番に試してみます。
最終的にどうしたかはまた報告させていただきます。
>845 ユニーク処理
Table2から値を取得する時に
Where Div1 = 1 or Div1 = 1
と無駄なSQLにならないようにDiv1の値を一意にする処理です。
・DB2、バージョン9?
・受注データ
・検索範囲の期間指定をして、日別の受注件数を表示したい。
ただし、受注がなかった場合にはその日は受注0件と表示しなければいけない。
・以下のような結果を得たい。
例
元となるテーブルが、
4/1 商品A
4/1 商品B
4/3 商品A
4/4 商品A
4/5 商品C
(4/2の受注レコードはなし)
で、
検索期間4/1-4/5という場合、
4/1 2件
4/2 0件
4/3 1件
4/4 1件
4/5 1件
という結果を得たい。
よろしくお願いします。
SQLってのは基本的にテーブルに存在するデータを出力するためのものなんだよ
どのテーブルにも4/2って日付がないならSQLだけで出力しようというのが間違ってる
ストアド作って出力させるか、ホストアプリで工夫するか、日付もってるテーブルと突き合わせるか
どうしてもSQLだけでやりたいなら再帰SQL使うとかしないかと(DB2でできるかどうかはしらん)
862 :
847:2010/04/30(金) 13:29:53 ID:???
>>858 いや、実は自分も毎回CAPS LOCKを押すのもどうかと思ってたんで
SHIFTが一般的っぽいならSHIFTにします(^^ゞ
>SELECT column1,MAX(column2) FROM Table1 GROUP BY column1;
(・∀・)イイ!! ミヤスイ!!
今後、書き方はそれに従います
今までの自分との違いは「テーブルの頭文字のみ大文字」だけですけどね
勉強になりました、ありがとうございました
なにも考えずに全部小文字で後から整形もしない派なんだけど
ここでは少数派かな?
スレチだけどCapsLock使うと楽だよ。というか、多分拒否してる人は食わず嫌い。
すまん。カナタイパーの俺にとってそのキーはCapsLockではなく
英数(かなロックOFF)キーなんだわ。SHIFT+CapsLockも面倒だし。
IMEをOnにしたまま書くほうが面倒じゃね?
>>860 日付テーブルを別途用意してOUTER JOIN
俺もカナタイパーだがUSキーボードなんでCAPS LOCKはそのまま押すだけでOK
SHIFT押しながらでも打てるが長い文だとやっぱCAPS LOCK ONだな
快適さが違う
全部小文字
大文字とか見づらいよ
俺も全部小文字だ
インデント合わせてれば全部小文字でも見やすいよ
全部、全角の小文字だ。
>>870 インデントというか、改行を入れる場所とかどうしてる?
ただ単に長くなったら適当な語句でって訳でもないんでしょ?
テンプレ
>>4-6にあるようなぐらい、細かく入れてるものなのかな。
そろそろ別スレでやってくれ
じゃ、どのスレか指定しる
このスレ以外ならどこでもいいよ。
877 :
NAME IS NULL:2010/05/05(水) 03:24:17 ID:os4bXybE
T_USER T_HOGE T_MAG T_USER
ID|NUM ID MAG ID|NUM
--|---- -- -- --|----
A | 100 A 100 .→→→ A | 300
B | 100 A update結果 B | 200
C | 100 B C | 100
「T_HOGE.IDがT_USER.IDと一致する数×T_MAG.MAG」の結果を
T_USER.NUMに対し加算するUPDATE文を書きたいのですが
どうすればよいでしょうか。IDはVARCHAR型です。
DBMSはMySQL 5.1です。
update T_USER AS a
set NUM = NUM + ((select count(*) from T_HOGE where ID = a.ID) * (select MAG FROM T_MAG))
>>877 MySQLで行けるかどうかわからんが
update T_USER
set NUM = NUM + (select count(*) from T_HOGE where ID = T_USER.ID) * (select MAG from T_MAG)
where ID in (select ID from T_HOGE)
T_MAGが1行じゃないとエラーになるのでその場合はmaxなりsumなりしてくれ
880 :
879:2010/05/05(水) 04:13:28 ID:???
う、こんな夜中にかぶってるとはw
まあwhereはあってもなくても結果は同じだが
無意味に+0されるのが気になるようなら
updateのテーブル名に別名付けれない処理系は結構存在すると思うが
MySQLではOKなのかな?
少なくともお前らが想定してるDBで試してからSQLかけよw
そのためにDB起動したりテーブル作ったりするのがめんどい。
なので、ソラでSQL書くだけ。
このスレの回答者のレベルは低すぎ
質問者よりSQLが分かってない馬鹿が答えてる事も多い
2chに何期待してんだよw
同じ2chでも他のスレはここまで酷くないぞ
少なくとも質問すら理解出来てない初心者がソラで書いて答えるような事はまず無い
で、レベルの高い人の回答は?
別に、これで誰も困ってないだろ?
文句あるなら、初心者がびびってレスできなくなるような、
すばらしい回答でもしてくれよ。
>>885 質問を理解できてないってことは、
>>878,879の回答は間違ってるって言うんだよな?
レベルが高いか低いかはどうでもいいが、間違ってるならどう間違ってるかぐらい指摘しろよ
889 :
NAME IS NULL:2010/05/08(土) 15:48:55 ID:n+w//U39
質問です。DBMSはOracle10gです。
ユニークな数値IDをキーとしたテーブルがあります。
IDは連続した値にはなりません。
ID | hoge
----+---------
001 | aaa
003 | bbb
047 | ccc
084 | ddd
120 | eee
401 | fff
645 | ggg
IDは1000万まで取り得ます。
ここから、1〜1000万までの間で、テーブルに存在しないIDのみを取り出すには
どのように書いたら良いでしょうか?
1〜1000万までのIDを埋めた別テーブルと外部結合して、
元テーブルのIDがnullのものを取るしかないかな・・・?
それでいいよ。not in とかでもいいけど。
1000万件のnot inとか書いてたらぶん殴られるな
892 :
889:2010/05/08(土) 23:58:40 ID:n+w//U39
レスどうもです。
やっぱ別テーブル用意するしかないですかね・・・。
1000万行のinsert文書かれたSQLはイヤなので、
スクリプトで作ったほうがよいかな。
not inは使いませんw
存在しないIDを全て取り出したいなら母集団のテーブルが要るだろうね
ひとつ取り出せばいいだけならもうちょっとやりようもあるけど
処理速度はシラネ
SQL> select id from hoge;
ID
----------
1
2
3
5
8
10
6行が選択されました。
SQL> select level "null ID" from dual connect by level<=(select max(id) from hoge)
2 minus
3 select id from hoge;
null ID
----------
4
6
7
9
指定した範囲の数値テーブルを返す関数とかあるんじゃね。
PostgreSQLなら generate_series(1,10000000) で1から1000万までの数値が入った集合(テーブル)を返す。
896 :
NAME IS NULL:2010/05/10(月) 19:15:03 ID:BnQAk+sp
過去にクラスメイトとJSP+MySQLでショッピングカートを作りました
そのMySQLのデータは前のパソコンのハードディスクに中にあったのですが
卒業後にハードディスクが壊れてしまいました
ソースコードはバックアップがあったので、ある程度までは起動します
ただ、MySQLのデータが無いのでそれ以上進めません
簡単なUMLダイアグラムだけは残っています
MySQLのフィールド名と他のクラスとのつながりが書かれていますが
文字数や属性なんかは書かれていません
こんな状態で正常に動くまで
MySQLのデータを復旧させることが出来ると思いますか?
ちなみにそのクラスメイトとは疎遠です
スレ違い。
板違いだな
オカルト板で聞くべき
そのクラスメイトは可愛いのならフラグだろうな
>>896 ハードディスクの復旧サービスに依頼するしかないんでないの
901 :
896:2010/05/12(水) 02:06:32 ID:???
>>900 KNOPPIXで復旧しようと思ったんですけど
TOSHIBAのハードディスクだったんで完全にロックが掛かっちゃったんですよ
ネットで検索したら、もうその状態では復旧も無理らしいんで諦めました
しかも引越しのときに捨てました
いやいや、そんな話をしているんじゃなくて
やっぱりUMLダイアグラムとソースを見ながら
MySQLのデータを元に戻すのは
かなり難しそうですか?
無理っぽいなら逆にソースの方をいじって
アクセスするMySQLのフィールドの数を減らそうと思ってます
それも簡単にいくとは思えないんですけど
>>901 いや、そういう復旧じゃなくて。。まぁいいか。
MySQLのデータってレコードの話してる?それともスキーマ?
スキーマなら最低限必要なものまでは復旧できると思うよ。
というか、フィールド減らすってダメでしょ
というかソースあるならアクセスしてる全テーブル・全カラムはわかるじゃねーか
型に関しては、日付型はソース見りゃわかるし文字列型と数値型をヤマカンで割り振れるだろう。
もちろん文字数・桁数は大きめにとる。100桁とか。
これで動くだろ。
904 :
896:2010/05/12(水) 09:50:28 ID:???
ありがとうございます
>>902 データと書くと語弊がありましたね
復旧したいのはスキーマです
(データの方は製品の写真もありますし、後でなんとでもなりますよね)
おっしゃるとおり、最低限必要なものまで復旧できそうであるならば挑戦してみます
>>903 全テーブル・全カラムは
(超簡易ですけど)UMLダイアグラムに書いてありますので今でも分かります
ただ、文字数・桁数が気になっていました
では余裕をもって大きくとっておきます
後はFOREIGN KEYのON UPDATEとかUNIQUEとか細かい設定が気になりますが
とりあえず可能性があるなら試行錯誤で設定してみます
つまづいたらまた質問するかもしれません
ちょっと前までSQLはSQL Serverの略だと勘違いしている人がいるってネタ
があったけど昨今はSQLとMySQLがごっちゃにされるご時世なのかね。
前者は今でもよくいると思うけど後者は珍しいね
908 :
NAME IS NULL:2010/05/13(木) 23:48:18 ID:e7aL0cCY
MySQL 5.1
TEAMが1 で、
NAMEにaを含むデータを上位にして、
SCOREの多い順に取り出したいのですが、
どう書けばいいですか?お願いします。
これを
ID|TEAM|NAME|SCORE
--+----+----+-----
1 | 1 | aaa | 200
2 | 2 | abb | 50
3 | 1 | ccc | 300
4 | 1 | daa | 400
5 | 1 | eae | 100
6 | 1 | fff | 800
↓こうしたい
ID|TEAM|NAME|SCORE
--+----+----+-----
4 | 1 | daa | 400
1 | 1 | aaa | 200
5 | 1 | eae | 100
6 | 1 | fff | 800
3 | 1 | ccc | 300
>>908 select * from TABLE_NAME
where TEAM = 1
order by case when NAME like '%a%' then 0 else 1 end, SCORE desc
911 :
NAME IS NULL:2010/05/15(土) 19:41:46 ID:PxDAo/Fz
>>909 select * from TABLE_NAME
where TEAM = 1
order by NAME like '%a%', SCORE desc
912 :
NAME IS NULL:2010/05/16(日) 17:06:06 ID:1vPYIMlX
文章の中に特定キーワードがあるかどうかを探し、一致した場合そのキーワードを取得する
SQLを作成したいのですが、(たとえるなら逆LIKEのようなイメージ)上手くいきません。
テーブル名:T_DIC(InnoDB,UTF-8)
KEYWORD
-------
'ぬるぽ'
'めるぽ'
'nullpo'
これに対し、'お前の母ちゃんぬるぽ!nullpo!'のキーワードに対する応答を下記のようにしたいのです。
KEYWORD
-------
'ぬるぽ'
'nullpo'
MySQL 5.0.67です。宜しくお願いします。
意味がわからん。
そんなもんシンプルに
>>912 ガッ
すればよろしい
質問させてもらいます。
ユーザ users がいて、そいつが登録した本データ books 、音楽データ musics ってのがあった時に、
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255)
);
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
bookname VARCHAR(255),
created DATETIME
);
CREATE TABLE musics (
music_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
musicname VARCHAR(255),
created DATETIME
);
あるユーザに関連する本と音楽をとってきて、それを created で並べ替えたいんだけど、うまい方法ありますか?
適当に JOIN とかしてみると、なんか他のフィールドにも要らないモノがはいってしまってダメです。
例えば、以下のようなデータが欲しいです。
ここでは面倒なので user_id AS u, book_id AS b, music_id AS m として、
| u | b | m | bookname | musicname | created |
+---+---+---+-----------+---------------+---------------------+
| 1 | 3 | 0 | DEATHNOTE | NULL | 2010-05-14 00:00:00 |
| 1 | 0 | 2 | NULL | GO!GO!MANIAC! | 2010-05-14 02:00:00 |
| 1 | 8 | 0 | みなみけ | NULL | 2010-05-14 04:00:00 |
みたいな感じです。
usersとbooksをjoinしてselect、
usersとmusicsをjoinしてselect、
unionしてcreatedでソート。
超ありがとうございました。
username が必要ないならusersとjoinする必要はないな
>>912 select keyword from t_dic where 'お前のかーちゃんぬるぽnullpo' like '%' + keyword + '%';
試してないからうまくいかなかったらごめんね
922 :
NAME IS NULL:2010/05/19(水) 13:42:58 ID:K9z2vbtK
売上データベースがあって、主テーブルの項目は、売上番号、得意先名、日付。
明細テーブルの項目は、明細番号、売上番号、商品、個数。売上番号で1対多でリンクしています。
商品(複数)と日付を指定して、 商品名 個数計 得意先計の一覧を出力したいです。
ただし得意先名は同名を1とカウントしたいのです。
SELECT 明細.商品, Sum(明細.個数) AS 個数計, Count(主.得意先) AS 得意先計
FROM 主 INNER JOIN 明細 ON 主.売上番号 = 明細.売上番号
WHERE 主.日付 Between '5/1/2010' And '5/4/2010'
GROUP BY 明細.商品
HAVING 明細.商品='えんぴつ' or 明細.商品='筆';
とすると、得意先計が複数カウント(同名は1とならない)してしまいます。
どのように書いたらよいのでしょうか。
923 :
922:2010/05/19(水) 13:44:34 ID:K9z2vbtK
すみません。DB書き忘れました。
Firebird 1.03です。よろしくお願いします。
Count(主.得意先)
を
Count(distinct 主.得意先)
にする。
firebirdで使えなかったらごめん
925 :
922:2010/05/19(水) 16:11:56 ID:K9z2vbtK
>>924 今そのものを試せないのですが、別DBで試してみていけそうです。
ありがとうございます。
ただ、純粋(?)なSQLでは無理なのでしょうか?
ACCESSだと構文エラーになってしまいます。
SQLの汎用性は持たせたいのです。
(ACCESSが純粋なSQLとは思えないですけれど)
標準SQLに準拠していないDBだと無理なだけじゃないかな
count distinctの併用はできないがサブクエリが使えるDBならサブクエリで解決できるよ
かといってサブクエリはすべてのDBで使えるわけじゃないし
927 :
922:2010/05/19(水) 19:03:40 ID:???
>>926 ・count distinctの併用
・サブクエリの利用
は、標準SQLで定義されているのでしょうか。
定義されていないから、ローカルルール・自由拡張しているような気が・・・。
標準SQL準拠の範囲でできないでしょうか?
サブクエリを用いたSQLだとどうなりますでしょうか?思いつかなくて
すみません。
ということで、標準SQLに準拠した範囲のSQLだよ、ということを書き忘れていた。
930 :
922:2010/05/19(水) 22:45:57 ID:???
>>928 929
ありがとうございます。なるほど両方ともSQL92に準拠してるんですね。
ACCESSなんかずばりの例でありがとうございます。
図々しいお願いですが、「分割して取得して」というのは
どうやるのでしょうか。
922のSQLでいうと、商品と期間で一旦テーブルを作成して(VIEW?)
それを店舗数でカウント・・・だと結局同名1社に統合できない。
どうしたらよいでしょうか?何度もすみません。丸投げで恐縮
ですが、助けていただきたくよろしくお願いいたします。
count(distinct hoge)という標準SQLすら使わずにやるという条件なら、
普通のselectだけ使ってあとは呼び出し元のプログラム側でカウントしろよと言う意味かと。
さすがに内部結合が使えないRDBMSはないか。
933 :
NAME IS NULL:2010/05/20(木) 22:22:35 ID:GTmF0xlD
DEFENDANT GUILT JOROR
-----------+-------+--------
HOGE | 1 | FOO
HOGE | 0 | BAR
HOGE | 1 | BAZ
FUGA | 1 | FOO
FUGA | 1 | BAR
FUGA | 1 | BAZ (T_JUDGEテーブル)
上記のテーブルにDEFENDANTで絞込をかけた結果、GUILTに一つでも0がある場合
1行も取得せず、全部1の場合はそのまま一覧を返す場合どのようなSQLを書けば
よいでしょうか?この場合、HOGEで絞り込むと0行、FUGAなら3行と言う具合です。
DBMSはMySQL5.0.67です。
select *
from T_JUDGE A
where not exists (select *
from T_JUDGE B
where A.DEFENDANT = B.DEFENDANT
and B.GUILT = 0)
;
テーブル tb_mastr tb_office tb_area
master_id office_code area_code
name office_name area_name
office_id
area_id
のテーブルがあり、office_idでtb_officeのoffice_nameを取得し、area_idでtb_areaのarea_nameを
取得したいのですが、2つ以上のテーブルを結合?させる場合どのように記述すれば
いいのでしょうか?
1対1なら以下の記述でできるのですが、同時にarea_idにarea_codeを割り当てる記述が
見つからなくて困っています。
SELECT tb_mastr.master_id, tb_mastr.name, tb_mastr.office_name FROM
tb_mastr LEFT JOIN tb_office ON tb_mastr.office_id=tb_office.office_code
その後ろにもう1個JOIN書けばいいだけなんだが
937 :
922:2010/05/22(土) 09:48:02 ID:???
>>931,932
お礼が遅くなりました。MSのサイトとかと参考に自力でがんばってみます。
ありがとうございました。
Mac(10.6)にphpMyAdminを入れたのですが、パスワードがわからず
ブラウザからログインができません。どのように設定すれば良いのでしょうか?
はい、次。
Mac(10.6)にphpMyAdminを入れたのですが、パスワードがわからず
ブラウザからログインができません。どのように設定すれば良いのでしょうか??
num0からnum50までの連番でフィールドがあるのですが、
この中で、レコードの値が0より大きいものをSELECTするにはどうしたらいいでしょうか?
ワイルドカード的な書き方はないのでしょうか?
「レコードの値が」ってどういう意味やら。
num0〜50のどれかが0より大きかったらって意味か?
num0が0より大きい または num1が0より大きい または・・・ num50 が0より大きい
ってこと?
まあorで並べるしかないわな
まずテーブルを最適化しろって言いたくなるような質問だなあ。
>>946でFAだと思う。
全部足して > 0
全部足した結果がminより大きかったら。
全部足してっていうのが動作するのは状況が限られるだろ
マイナスの値がないこと、nullがないこと、他には?
全部足しても桁あふれしないこと、か
質問です。
SELECT COUNT(*)
FROM TABLE
より、
SELECT COUNT(COL_NAME)
FROM TABLE
の方がDBの処理は軽くなると先輩社員に聞きました。
処理時間に違いはないと思うのですが、どうでしょうか。
>>953 あなたの使っているDBMSによります。
昔のDBMSは、そうした方が速くなるものがありました。
最近のDBMSであればもう解決済みで、ほぼ問題ないはずです。
一番の答えは実際に計測することですね。
…俺がついつい後者で書いてしまうのは悪い癖orz
>>953 MySQL/InnoDBだとそうなると聞いたことはある。
ほんとにそうなのかどうかは確かめていない。
956 :
953:2010/05/24(月) 12:07:20 ID:???
>>954-955 確かにレコード数に左右されそうですね。
その時に使っていたDBはOracle9iでした。
最新のDBであれば、実感として変わらないという思いは変わりません。
機械があれば計測してみます。
>>953 COL_NAMEがNULLを含んでいる場合に結果が変わってくるのはわかるよね?
NULLを許可している場合にNULLチェックが必要になる関係で後者が遅くなる処理系がある。
>>956 誰もレコード数に左右されるとは書いてないが
959 :
953:2010/05/24(月) 18:54:02 ID:???
うう、誤解を招く発言をしてしまい申し訳ありません。
精進します。
960 :
NAME IS NULL:2010/05/25(火) 00:31:12 ID:sUe75wMv
ID|A|B|C|
---------
1 |1|2|3|
2 |3|4|5|
3 |7|8|9| (テーブルFOO)
select FOO.ID,FOO.C from FOO,(select 1 AS EXPR) AS A
where FOO.A = A.EXPR or FOO.B = A.EXPR or FOO.C = A.EXPR;
こんなかんじで得たCの内容を1のところにセットして
該当するIDとCを全てリストアップするSQLはどう書けばよいでしょうか?
MySQL 5.0.67です。ストアドの作成権限はないです。
>>960 意味がワカラン。
a,b,cいずれかに1がある行のidとaを抽出ってことでもないんだろ?
エスパーしようと思ったが意味わからないなw
まず
何がやりたいのか を整理して
日本語 で説明してくれ。
SELECT * FROM HOGE WHERE A > 100 OR IN (1,2,3,4,5)
と
SELECT * FROM HOGE WHERE A > 100
UNION
SELECT * FROM HOGE WHERE IN (1,2,3,4,5)
ではどちらが軽いでしょうか?
構文ミスを除けば一般に前者
後者はソートが発生する
>>960 俺がエスパーするに
A,B,Cのカラムのいずれかが1であった場合、その1だったカラムの内容をCの値で更新する
実際に更新するSQLと更新対象をリストアップするSQLは?
だとみた
リストアップは
select ID,C from FOO where A=1 or B=1 or C=1
でいいんじゃね。AもBも対象だったら2行だせとかだとまた話は別だが
更新は
update FOO set A=C where A=1
update FOO set B=C where B=1
の2行かな。CをCで更新しても意味ないからな
で、この三つを同一トランザクションで流せばいいんじゃないかと
立ててみる。しばし待たれ。
972 :
NAME IS NULL:2010/05/27(木) 23:09:36 ID:JLDVw0sK
こっちを先に消費すること。
そもそもこれまでのスレ速度考えたら967で次スレ検討とか見積もり甘すぎでしょ。
質問です。
テーブル設計も質問してもいいんでしょうか?
社内に聞ける人間がいなくて、悩んだ時期が自分にありました。
それ用のスレは過疎っているように思うからここでもいいんじゃない?
おお、スレッドあるんですね。
ありがとうございました。
スレ違い
979 :
NAME IS NULL:2010/06/01(火) 23:54:28 ID:4+IIsIOo
テーブルの外部キー制約において、現在ON DELETE RESTRICTとなっているところをCASCADEに変更したいのですが、
運用中なのでDROPできないのでALTERで変更したいのですが、どのように書けばよいでしょうか。
MySQL5.0です。
hosyu
そこはUPDATE