2009年5月 7日

[mysql 14830] Re: SQL文、お知恵を拝借させてください

KK@xxxxx です。

これも蛇足っぽいのですが...
5でなくてもサブクエリは使えます。

基本的には、名前とフルーツ名を掛け合わせた重複しないすべての組み合わせ
から、最初に設定されたデータを取り除いたものが答ですから...

表示中の列 0 - 2 (3 合計, クエリの実行時間 0.0021 秒)
実行した SQL:
SELECT yname, fname
FROM `names` , f2
WHERE concat( yname, fname )
IN (

SELECT DISTINCT (
concat( yname, fname )
)
FROM `names` , f2, Q2
WHERE concat( yname, fname ) NOT
IN (

SELECT concat( nm, fruitname )
FROM Q2
)
)
LIMIT 0 , 30


yname fname
Inoue Apple
Aoki Orange
Inoue Grape

---------------------------

SELECT version( )

version()
4.1.22-standard

-------------------------------

しかし、残念ながら、すでに提示されたSQL文のほうが効率が良いようです。

実行した SQL:
EXPLAIN SELECT DISTINCT t1.nm, t2.fruitname
FROM (
Q2 t1, Q2 t2
)
LEFT JOIN Q2 t3 ON t1.nm = t3.nm
AND t2.fruitname = t3.fruitname
WHERE t3.nm IS NULL

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 9
1 SIMPLE t3 ALL NULL NULL NULL NULL 9 Using where; Not exists; Distinct

--------------------------------------------------------------------------------

実行した SQL:
EXPLAIN SELECT yname, fname
FROM `names` , f2
WHERE concat( yname, fname )
IN (

SELECT DISTINCT (
concat( yname, fname )
)
FROM `names` , f2, Q2
WHERE concat( yname, fname ) NOT
IN (

SELECT concat( nm, fruitname )
FROM Q2
)
)

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY names index NULL PRIMARY 10 NULL 3 Using index
1 PRIMARY f2 index NULL PRIMARY 10 NULL 4 Using where; Using index
2 DEPENDENT SUBQUERY names index NULL PRIMARY 10 NULL 3 Using index; Using
temporary
2 DEPENDENT SUBQUERY f2 index NULL PRIMARY 10 NULL 4 Using where; Using
index
2 DEPENDENT SUBQUERY Q2 index NULL PRIMARY 8 NULL 9 Using where; Using
index; Distinct
3 DEPENDENT SUBQUERY Q2 ALL NULL NULL NULL NULL 9 Using where

>> 4.0だとサブクエリが使えませんよね?
>>
>> SELECT
>> DISTINCT food
>> FROM
>> table1
>> WHERE
>> food not in ( select food from table1 where name = '井上' )
>>
>> サブクエリが利用できればこんな感じでしょうか
>
> はい。これに近いものは何度も試していたのですが、エラーが出ていました。
> Ver.4でサブクエリが使えないのが原因だったのですね。無知でした。
>
>> SELECT
>> food, sum(IF(name='井上',1,0)) as ct
>> FROM
>> table1
>> GROUP BY
>> food
>>
> これの動作、確認させていただきました。
> いい感じです。
>
>> 試行錯誤の末、次のようにしたらそれっぽいリストが抜け出せました。
>>
>> mysql> select distinct t1.name,t2.food from (table1 t1, table1 t2) left
>> join table1 t3 on t1.name=t3.name and t2.food=t3.food where t3.name is
>> null;
>> +------+--------+
>> | name | food |
>> +------+--------+
>> | 井上 | りんご |
>> | 青木 | みかん |
>> | 井上 | ぶどう |
>> +------+--------+
>>
>> MySQL 4.0 が入手できなかったので、5.1.31 での結果ですけど。
>>
> こちらのとみた様の提示いただいたものも、確認できました。Ver.4でも同結果得られました。
> ご苦労頂きありがとうございました。とりあえず、こちらのものを使わせていただきたいと思います。
>
> とは言うものの、サブクエリが使えると何かと便利そうですので、機会を見てVer.5へ変更してみ
> ようと思います。

投稿者 xml-rpc : 2009年5月 7日 11:07
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/84869
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。