クエリの番号が画像のSELECT出力と同じはず。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
/* P131 SQLパズル すべての製品を購入した人 いくつかの製品を購入した人を探す */ CREATE TABLE Customers( customer_id int NOT NULL, acct_balance DECIMAL(12,2) NOT NULL ) CREATE TABLE Orders( customer_id int NOT NULL, order_id int NOT NULL ) CREATE TABLE OrderDetails( order_id int NOT NULL, item_id int NOT NULL, item_qty int NOT NULL,--注文数量 primary KEY (order_id,item_id) ) CREATE TABLE Products( item_id int NOT NULL Primary key, item_qty_on_hand int NOT NULL--在庫数量 ) INSERT INTO Customers VALUES(1,100), (2,200) INSERT INTO Orders VALUES(1,1), (2,2) INSERT INTO OrderDetails VALUES(1,1,10), (2,1,5) INSERT INTO Products VALUES(1,100), (2,200) INSERT INTO Orders(customer_id,order_id) VALUES(1,3) INSERT INTO OrderDetails(order_id,item_id,item_qty) VALUES(3,2,20) INSERT INTO Orders(customer_id,order_id) VALUES(1,4) INSERT INTO OrderDetails(order_id,item_id,item_qty) VALUES(4,1,12) SELECT 'Customers' as tableName,* FROM Customers SELECT 'Orders' as tableName, * FROM Orders SELECT 'OrderDetails' as tableName,* FROM OrderDetails SELECT 'Products' as tableName,* FROM Products -- --すべての製品を購入したユーザは？ --製品の総数は 2つ --ユーザが購入した製品の種類は2種類 --あるユーザが何種類の製品を購入したかを調べる --OrderDetails.order_idで Orders.order_id,customer_idがわかる --製品数が2種類というのはわかる。 --では全種類買ったというのはどこでわかる --(A)結合 (1) SELECT * FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id (2) --item_idでグループにして製品をそれぞれいくつ買ったかを調べる。製品数が行数になる SELECT OrderDetails.item_id,COUNT(OrderDetails.item_id) as buy_count_item FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id GROUP BY OrderDetails.item_id --この行数とProductsの数が同じであれば全製品が購入されたことがわかる --一人で全製品が購入されたかを調べる (3) --(B)ユーザ単位で、製品をまとめる SELECT Orders.customer_id, OrderDetails.item_id, COUNT(OrderDetails.item_id) as count_item_id FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id GROUP BY OrderDetails.item_id,Orders.customer_id (4) --BをもとにCustomer_idをGROUPにしてカウントして製品数が同じであれば全製品を購入した SELECT customer_id, COUNT(Customer_id) as count_item_Num FROM ( SELECT --(B) Orders.customer_id, OrderDetails.item_id, COUNT(OrderDetails.item_id) as count_item_id FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id GROUP BY OrderDetails.item_id,Orders.customer_id ) as ALLTable GROUP BY customer_id --これでcustomer_id:1の人は2製品を購入したことがわかる /* 正解 */ --（これから） SELECT AVG(acct_balance) FROM Customers as c1 Where ( SELECT COUNT(DISTINCT item_id) FROM Products )<> ( SELECT COUNT(DISTINCT item_id) FROM ORders,OrderDetails WHERE orders.customer_id = c1.customer_id AND Orders.order_id = OrderDetails.order_id ) --(検討１) SELECT * FROM Customers as c1 Where ( SELECT COUNT(DISTINCT item_id) FROM Products )<> ( SELECT COUNT(DISTINCT item_id) FROM ORders,OrderDetails WHERE orders.customer_id = c1.customer_id AND Orders.order_id = OrderDetails.order_id ) --= SELECT * FROM Customers as c1 Where ( SELECT COUNT(DISTINCT item_id) FROM Products )= ( SELECT COUNT(DISTINCT item_id) FROM ORders,OrderDetails WHERE orders.customer_id = c1.customer_id AND Orders.order_id = OrderDetails.order_id ) SELECT * FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id LEFT OUTER JOIN Customers ON Customers.customer_id = Orders.customer_id --購入した製品数 SELECT COUNT(DISTINCT item_id) FROM Orders LEFT OUTER JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id LEFT OUTER JOIN Customers ON Customers.customer_id = Orders.customer_id |
内容
–(検討１)
–（KB1)のところが理解できない。なぜ、COUNTした数が一致するとCustomerがわかるのか？
–数値でCustomerが取得できる理屈がわからない。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/*以下は検討*/ --(検討１) --（KB1)のところが理解できない。なぜ、COUNTした数が一致するとCustomerがわかるのか？ --数値でCustomerが取得できる理屈がわからない。 SELECT * FROM Customers as c1 Where ( SELECT COUNT(DISTINCT item_id) FROM Products )<> (--（KB1） SELECT COUNT(DISTINCT item_id) FROM ORders,OrderDetails WHERE orders.customer_id = c1.customer_id AND Orders.order_id = OrderDetails.order_id ) |
等号の場合は全製品2つを購入したcustomer_id1が出力される。以下は不等号なので全製品を購入していない2が出力された
（KB1）のところを調べる。そもそも等号不等号で行のレコードが表示される仕組みが理解できていない。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--（KB1）のところを調べる。そもそも等号不等号で行のレコードが表示される仕組みが理解できていない。 --SELECT * FROM Customers as c1 --Where --(--（KB1）WHERE以下を組み直すと以下のようになる。結果は同じ SELECT *--COUNT(DISTINCT item_id) FROM ORders INNER JOIN OrderDetails ON --orders.customer_id = c1.customer_id --AND Orders.order_id = OrderDetails.order_id INNER JOIN Customers C1 ON orders.customer_id = c1.customer_id --) |
INNER JOINの結合表
次にitem_idでCOUNT（DISTINCT item_id)を求める
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
--次にitem_idでCOUNT（DISTINCT item_id)を求める --そうすると重複を除く購入した製品数がユーザごとにでてくる。 --全製品は２つでこのときの購入者のデータも存在する。 /* customer_id１が製品数２で全部買った customer_id COUNT_item_id(製品数) 1 2 2 1 だから、製品数が同じときとそうでないときで誰が購入したかがわかる。 つまり、正解は数で調べているが、実際は見えないデータ（と自分が思い込んでる）が存在している。 */ SELECT orders.customer_id,COUNT(DISTINCT item_id) as COUNT_item_id FROM ORders INNER JOIN OrderDetails ON --orders.customer_id = c1.customer_id --AND Orders.order_id = OrderDetails.order_id INNER JOIN Customers C1 ON orders.customer_id = c1.customer_id GROUP BY orders.customer_id--COUNT（DISTINCT item_id)だけをだすなら不要 --KB1の検討ここまで |
結論
COUNTという数字だけで見ていると製品数しか情報を持っていないと思っていたようだ。
実際は、製品数に付随するCustomer_id情報もあるので、出力されたと思う
あくまでもWhereでの＜＞条件を求めているに過ぎず、SELECTでは必要な情報が出力されている。
このような考え方に慣れるしかない。
こちらの記事もどうぞ
SQLSERVER パズル63 MINの扱いが難しく理解できない、相関サブクエリ パズル63 問題文
dataのまとまりで連続しているnumを表示したい。
MINというと結 […]
SQLSERVER クエリ、ALL演算子、puzzel11_作業依頼 /*
puzzel11_作業依頼
C:完了
W:待機
step_nbrが0でstep_st […]
SQLSERVER勉強中、SQL実践入門P255成績のところ 行を列に /*
SQL実践入門P255成績のところを勉強中
*/
生徒に対して、複数の教科があり、生徒 […]
SQLSERVER SELECT TOP にはできるだけORDER BYをつける 先日、サーバによってSELECT TOP 1 AA FROM […]
SQLSERVER 行に出力されるanest_nameを横に1列に出力したいとき […]
SQLSERVER SUMの使い方GROUP SQLSERVER SUMの使い方GROUP
/*
[…]