こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

EXISTSについて

以下のようにしているのですが取得できるデータは1回目のselectのみで2回目のデータが
何度やっても取得できません。

$sql1 = 'SELECT
//ここのデータは取得できてます。
products_id, name, products_flag, IF("' . $products->beforedays . '" < DATE(create_date), "1", "0") as new
FROM
products
WHERE EXISTS (
SELECT
MIN(price1) as min_price1, MAX(price1) as max_price1,
MIN(price2) as min_price2, MAX(price2) as max_price2,
stock
FROM
products_class_detail
WHERE
//1回目のselectで取得したproducts_idを基準に問い合わせ
products_class_detail.products_id = products.products_id
)
//ここはproductsに存在するフィールドです。
AND status1 = ? AND status2 = ? AND del_flg = ?
ORDER BY
RAND()';

マニュアルも見てみたのですが取得できない原因がわからない状態におります。

どうすれば取得可能になるでしょうか?宜しくお願い致します。

投稿日時 - 2009-06-02 16:12:18

QNo.5011020

困ってます

質問者が選んだベストアンサー

どういう結果を得たいのか、説明できないというのは困りましたね。
existsのことは、一旦、忘れましょう。前回の回答者の勘違いだと思います。

>例えばproducts_id = 1であれば
>・1の商品ID
>~中略~
>・1の最大料金(通常価格)
>・1の最小料金(通常価格)
>・1の最大料金(セール価格*NULLでなければ)
>・1の最小料金(セール価格*NULLでなければ)
>・1の在庫数
>です。

detail表に、product_id=1の行が5行あったら、どういう結果を得たいのでしょうか?

こんな記述でしたよね?

SELECT
MIN(price1) as min_price1, MAX(price1) as max_price1,
MIN(price2) as min_price2, MAX(price2) as max_price2,
IFNULL(price2, price1) as price0, stock
FROM products_class_detail

「1の在庫数」は、stock列をsum関数で合計した結果を得たいのではないでしょうか?
それとも、5行それぞれに「1の最小料金(通常価格)」という同じ値をくっ付けて得たいのでしょうか?
前者であれば、sum(stock)とする必要があるし、後者であればmax等を得るクエリと各行を得るクエリを作って結合する必要があります。

>・1の最大料金(セール価格*NULLでなければ)
>・1の最小料金(セール価格*NULLでなければ)

これも同様で、各行でifnullした結果の中で、最大、最小を得たいのではないでしょうか?

こちらで想像力を働かせて、適当にSQLを作ってみました。これを活用して、試行錯誤するなり、情報を整理した上で再質問してみてください。

-- 定義済なら、削除
drop table if exists products,products_class_detail;
-- 定義
create table products
(products_id int primary key
,name varchar(10)
,status1 tinyint(1)
,products_flag tinyint(1)
,del_flg tinyint(1)
,comment1 varchar(30)
,create_date date
)
;
create table products_class_detail
(products_class_id int primary key auto_increment
,products_id int
,stock int
,price1 int
,price2 int
,create_date date
,index(products_id)
);

-- 一旦、空に
truncate table products;
truncate table products_class_detail;

-- テストデータ格納
insert into products values
(1,'a',0,0,0,null,'2009-06-01')
,(2,'b',0,0,0,null,'2009-06-01')
,(3,'c',0,0,0,null,'2009-06-01')
,(4,'d',0,0,0,null,'2009-06-01')
,(5,'e',0,0,0,null,'2009-06-01')
,(6,'f',0,0,0,null,'2009-06-01')
;

insert into products_class_detail values
(null,1,50,100,99,'2008-06-01')
,(null,1,10,100,98,'2008-06-01')
,(null,1,20,102,98,'2008-06-01')
,(null,1,20,103,null,'2008-06-01')
,(null,2,50,120,null,'2008-06-01')
,(null,3,10,130,null,'2008-06-01')
,(null,4,20,90,88,'2008-06-01')
,(null,4,30,91,null,'2008-06-01')
,(null,5,30,90,null,'2008-06-01')
,(null,5,40,91,78,'2008-06-01')
;

-- products表だけの検索
select
products_id
,name
,products_flag
,create_date
from products
order by products_id
;
-- products_class_detail表だけの検索
select
products_id
,max(price1) as max_p1
,min(price1) as min_p1
,max(ifnull(price2,price1)) as max_p0
,min(ifnull(price2,price1)) as min_p0
,sum(stock) as sum_stock
from products_class_detail
group by products_id
order by products_id
;


-- 両方の表を、products_id毎に集計して検索
select
p.products_id as pid
,p.name
,p.products_flag as pf
,p.create_date as pcdate
,v_pcd.max_p1
,v_pcd.min_p1
,v_pcd.max_p0
,v_pcd.min_p0
,v_pcd.sum_stk
from products as p
left join
(
select
products_id
,max(price1) as max_p1
,min(price1) as min_p1
,max(ifnull(price2,price1)) as max_p0
,min(ifnull(price2,price1)) as min_p0
,sum(stock) as sum_stk
from products_class_detail
group by products_id
) as v_pcd
on p.products_id=v_pcd.products_id
order by p.products_id
;

投稿日時 - 2009-06-05 16:50:53

補足

何度もお返事頂き本当にありがとうございます。1つのテーブル操作であれば大体OKなのですが
不慣れなもので複数のテーブルからの連結となると理解が深めれず迷惑な話ですが自分でも
パニクっているexistsでなんとかなるものかと信じきっておりましたが
本当に感謝です!

言われてみて気づきましたが在庫数はSUMするべきですよね^^;

自分なりに試した結果ですが以下のSQLでなんとか思うような値を取る事に成功しました。

自信こそいまいちありませんが・・・

$sql = 'SELECT
A.products_id,
A.name,
A.products_flag,
A.comment1,
IF("' . $products->beforedays . '" < DATE(A.create_date), "1", "0") AS new,
MIN(B.price1) as min_price1,
MAX(B.price1) as max_price1,
MIN(B.price2) as min_price2,
MAX(B.price2) as max_price2,
IFNULL(B.price2, B.price1) as price0,
CEIL((MIN(B.price1) * "' . MASTER_TAX . '")) AS min_tax1,
CEIL((MAX(B.price1) * "' . MASTER_TAX . '")) AS max_tax1,
CEIL((MIN(B.price2) * "' . MASTER_TAX . '")) AS min_tax2,
CEIL((MAX(B.price2) * "' . MASTER_TAX . '")) AS max_tax2,
CEIL((MIN(B.price1) * "' . MASTER_TAX . '" + MIN(B.price1))) AS min_tax_price1,
CEIL((MAX(B.price1) * "' . MASTER_TAX . '" + MAX(B.price1))) AS max_tax_price1,
CEIL((MIN(B.price2) * "' . MASTER_TAX . '" + MIN(B.price2))) AS min_tax_price2,
CEIL((MAX(B.price2) * "' . MASTER_TAX . '" + MAX(B.price2))) AS max_tax_price2,
SUM(B.stock) AS stock
FROM
products A INNER JOIN products_class_detail B
ON
A.products_id = B.products_id AND A.status2 = ? AND A.del_flg = ?
GROUP BY
A.products_id, B.products_id'

単純な計算をSQLで行うように追加したのですがパフォーマンス的に問題等ってあるのでしょうか?

これまでは2回のSQLで計算はPHPで行っていましたが気持ち的には若干早くなった気がします。

突っ込みどころがありましたら再度アドバイス頂けますと幸いです。宜しくお願い致します。

投稿日時 - 2009-06-06 12:28:00

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

>自分なりに試した結果ですが以下のSQLでなんとか思うような値を取る事に成功

何度も同じことを書くのは不本意ですが、

IFNULL(B.price2, B.price1) as price0

の結果は、保証されませんよ?
product_idが同じdetailの行が5行あたとして、5行ともprice1の値、price2の値はそれぞれ同じではないのですよね?
もし同じなら、maxやminの意味がないし、値が異なるなら、MySQLは結果を保証していません。

http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html?ff=nopfpls

この独自仕様は、MySQL 5.0以降なら、使用しないようにもできます。
→MySQLの独自仕様で、「結果を保証できないのに、何も教えてくれずに実行されていた」ものが、標準SQL準拠で文法エラーになるようになる

http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html

>パフォーマンス的に問題等ってあるのでしょうか?

同じ結果を得る関数を、不必要に何度も使用しているのですから、当然、オーバーヘッドになります。
それ以前に、現状で、性能を出せるようにインデクスなどを設計しているのでしょうか???

質問のタイトルと質問内容が合わなくなってきているし、質問日時も古くなってきているので、一旦、この質問を閉じて再質問した方が、他の方からもアドバイスを得やすいのではないでしょうか?

その場合、私が補足要求したようなことを、予め書いてくれれば、無駄なやり取りをしなくてすみます。

投稿日時 - 2009-06-10 01:09:27

お礼

お返事ありがとうございます。
単純なSQLであれば問題ないのですが不慣れなもので複雑化するとどうも苦手意識が・・・
確かに時間もだいぶ経過しておりますので一端クローズし再度質問させて頂きたいと思います。
ご丁寧に教えて頂きまして本当にありがとうございました!

投稿日時 - 2009-06-11 11:57:14

ANo.2

前回の質問(​http://oshiete1.goo.ne.jp/qa4965989.html?ans_count_asc=20)を見ました。
誤解あるいは誤使用なのか、提示不十分なのか、前回の質問自体に誤りがあります。

products_class_detail表の検索ですが、group byを指定していないのでしょうか?指定がなければ、

SELECT
MIN(price1) as min_price1, MAX(price1) as max_price1,
MIN(price2) as min_price2, MAX(price2) as max_price2,
IFNULL(price2, price1) as price0, stock
FROM products_class_detail

のような、MAXなどの集合(集計)関数と、stockなどの列名を混在して指定すると構文エラーになるはずです。

さらに、もし上記に

GROUP BY products_id

を追加しているのであれば、”MySQLの独自仕様”により構文エラーにはならないですが、stock列などの値は保証されません。
MAXやMINは、(条件に合う)表全体またはgroup byで指定したグループ毎に1件の結果を得る指定です。

一方、現状でselect句の並びで同時に指定されている

IFNULL(price2, price1) as price0, stock

は、各行の値を各々返す指定です。

そのため、何を返すべきなのか、RDBMSでは判別できません。
標準SQLや主要なRDBMSでは、

select max(c2) from t1

select c1,max(c2) from t1 group by c1
といった指定は可能です。

しかし、今回の質問にもあるような

select c1,max(c2),c3 from t1 group by c1
→group byで指定されず、maxなどの集計関数の結果でもない「c3」列のような指定

は、許されません。

しかし、MySQLには、この部分に拡張仕様があり、「『group by c1』により『c3』も一意になるなら指定してよい。一意にならないなら、結果を保証しない」としています。

前回の質問にある

>2回のsqlの場合は問題なく動作していた

は、提示されたSQLが正しいとすると、信じられないというか、保証されていない結果なのに、正しく動いていると誤解しているように感じられます。

select文だけ、しかもそれが正しく動作しているか不詳なものだけを提示されても、他人には適切なアドバイスはできません。

(1)RDBMS名とバージョン
→MySQLの場合、MySQL 4.0まで、4.1、5.0などで大きな機能差や一部の仕様変更があります
(2)表の構成列、データ型、どの列でユニークになるか
(3)母体データ例
→得たいものだけでなく、得たくないものも
(4)得たい結果例

といったものを提示してください。

投稿日時 - 2009-06-03 16:07:18

補足

お返事ありがとうございます。もろもろ情報不足だったようですみません^^;
mysql5でショッピングカートを目的としています。非常に長くなりますが以下詳細です。

CREATE TABLE products (
`products_id` int(11) NOT NULL auto_increment,
`name` text NOT NULL,
`sale_limit` decimal(10,0),
`sale_unlimited` smallint(6) NOT NULL default '0',
`category_id` int(11) NOT NULL ,
`rank` int(11) NOT NULL default '0',
`status1` smallint(6) NOT NULL default '1',
`status2` smallint(6) NOT NULL default '1',
`products_flag` varchar(255),
`point_rate` decimal(10,0) NOT NULL default '0',
`deliv_date_id` int(11),
`filename0` varchar(255) NOT NULL,
`filename1` varchar(255),
`filename2` varchar(255),
`filename3` varchar(255),
`comment1` text,
`comment2` text NOT NULL,
`del_flg` smallint(6) NOT NULL default '0',
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
`update_date` datetime,
PRIMARY KEY (`products_id`)
);

具体的には・・・
ID,商品ID,商品名,販売数制限あり,販売制限なし,カテゴリーID,表示順,
スターテス1,スターテス2,フラグ,ポイント,配達ID,画像ファイル名1~4,コメント1~2,フラグ,登録日時,変更日時

(1, '商品名', NULL, 1, 9, NULL, 1, 1, '111', 10, 1,
'画像1',
'画像2',
'画像3',
'画像4',
'コメント1',
'コメント2',
'0',
'登録日時', '変更日時'),

CREATE TABLE products_class_detail (
product_class_id int(11) NOT NULL auto_increment,
products_id int(11) NOT NULL,
classcategory_id1 int(11) NOT NULL default '0',
classcategory_id2 int(11) NOT NULL default '0',
products_code varchar(50) NOT NULL,
stock decimal(10,0),
stock_unlimited smallint(6) NOT NULL default '0',
sale decimal(10,0) NOT NULL default '0',
sale_limit decimal(10,0),
price1 decimal(10,0) NOT NULL,
price2 decimal(10,0),
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
`update_date` datetime,
PRIMARY KEY (product_class_id)
);

具体的には
ID,商品ID,カテゴリーID1,カテゴリーID2,商品コード,在庫数,在庫制限,販売数,販売制限,通常価格,セール価格,登録日時,変更日時
// 同じ商品でもサイズがS,M,Lと違う場合以下のように同じIDで複数の値が混雑しています。
当然サイズがないものであれば1つのデータなので重複はありませんがさらに色違い等も
あれば1つの商品でも登録データは非常に多くなります。

(1, 1, 20, 0, '商品コード', NULL, 1, 1, NULL, 1500, 1300, '登録日時', '変更日時'),
(2, 1, 21, 0, '商品コード', NULL, 1, 1, NULL, 1400, 1200, '登録日時', '変更日時'),
(3, 1, 22, 0, '商品コード', NULL, 1, 1, NULL, 1300, 1100, '登録日時', '変更日時'),
(4, 2, 0, 0, '商品コード', NULL, 1, 1, NULL, 1500, NULL, '登録日時', '変更日時'),
(5, 3, 5, 33, '商品コード', NULL, 1, 1, NULL, 1980, NULL, '登録日時', '変更日時'),
(6, 3, 6, 34, '商品コード', NULL, 1, 1, NULL, 1980, NULL, '登録日時', '変更日時');

まずはproductsから
・商品ID
・商品名
・フラグ(products_flag)
・登録日時
を取得し取得した商品IDを基にproducts_class_detailから
通常価格の最大と最小値と在庫数を取得したいと思っております。

例えばproducts_id = 1であれば
・1の商品ID
・1の商品名
・1のフラグ
・1の登録日時
・1の最大料金(通常価格)
・1の最小料金(通常価格)
・1の最大料金(セール価格*NULLでなければ)
・1の最小料金(セール価格*NULLでなければ)
・1の在庫数
です。

うまく伝わればいいのですがどうすれば適切な結果が得られるものか自分でも混乱している状態で
意味わからないものにつきましては再度補足させて頂きますので宜しくお願いいたします。

投稿日時 - 2009-06-03 21:11:49

ANo.1

質問内容が、よく分かりませんが・・・。

>//ここのデータは取得できてます。
>//1回目のselectで取得したproducts_idを基準に問い合わせ

何を言いたいのでしょうか?

existsの使い方を、誤解しているのではないでしょうか?
products表のproducts_id列は、重複値があるのでしょうか?
また、existsのサブクエリ内のselect句で、max関数やmin関数を並べていますが、何ら意味がないですが、何をやりたいのでしょうか?

やりたいことは、existsではなく、joinなのでしょうかね?

投稿日時 - 2009-06-02 18:49:12

補足

お返事ありがとうございます。以前に
http://oshiete1.goo.ne.jp/qa4965989.html?ans_count_asc=20
でやりたいことを質問した際にexistsではないかとありましてなんとか試そうかと思っておりましたが全くの検討違いでしょうか?~~;

投稿日時 - 2009-06-02 20:56:11

あなたにオススメの質問