[SQL ] 分組查詢比較的問題…
我製作了一個表格：
mysql> select * from production;
+--------+------+----------+
| name | pid | orderqty |
+--------+------+----------+
| john | 1 | 20 |
| kevin | 2 | 10 |
| alisha | 3 | 5 |
| kevin | 2 | 30 |
| john | 3 | 15 |
+--------+------+----------+
接著，我想要以pid(產品代碼)為分組，找出組內的最大數。
mysql> select p1.pid,max(p1.orderqty) as max from production p1 group by
p1.pid;
+------+------+
| pid | max |
+------+------+
| 1 | 20 |
| 2 | 30 |
| 3 | 15 |
+------+------+
OK,到目前為止都正確…
接著，我要秀出所有的orderqty 乘以 0.9後的值，如下
mysql> select 0.9*p2.orderqty from production p2;
+-----------------+
| 0.9*p2.orderqty |
+-----------------+
| 18.0 |
| 9.0 |
| 4.5 |
| 27.0 |
| 13.5 |
+-----------------+
接著，我下了如下的指令：即組內的最大值，必需大於所有的orderqty*0.9後的值，
mysql> select p1.pid,max(p1.orderqty) as max from production p1 group by p1.pid
having max(p1.orderqty) > ALL(select 0.9*p2.orderqty from production p2 where
p1.pid=p2.pid);
原本我預期的結果為：
+------+------+
| pid | max |
+------+------+
| 2 | 30 |
+------+------+
因為，只有這一筆資料是大於所有的 0.9*p2.orderqty
但實際上的執行結果卻是：
+------+------+
| pid | max |
+------+------+
| 1 | 20 |
| 2 | 30 |
| 3 | 15 |
+------+------+
我覺得很奇怪，怎麼想都想不出哪些出錯了，
請問有誰看得出來，問題出在哪嗎？
謝謝!!!
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 125.233.65.235
推
07/22 13:12, , 1F
07/22 13:12, 1F
推
07/22 13:22, , 2F
07/22 13:22, 2F
→
07/22 13:45, , 3F
07/22 13:45, 3F
→
07/22 14:24, , 4F
07/22 14:24, 4F
推
07/22 14:33, , 5F
07/22 14:33, 5F
推
07/22 18:48, , 6F
07/22 18:48, 6F
→
07/23 06:53, , 7F
07/23 06:53, 7F