我在下面有 2 張桌子。我正在努力吸引有風險的購物者。有風險的購物者是平均風險評分為 100.00（四舍五入到小數點后 2 位）或更高的購物者
這是我到目前為止所擁有的
select m.merchantName,t.hashedShopperReference, AVG(t.riskScore) as averageRiskScore, COUNT(t.transactionId) as totalNumberofTransactions
from transaction as t
join merchant m on t.merchantId = m.merchantid
where t.hashedShopperReference IN(select distinct(hashedShopperReference)
from transaction where t.merchantId = m.merchantid)
group by t.transactionId
這給了我答案
Candle Store Inc fe9ac6f772088b00c006ca6736f946a2 10.0000 1
Candle Store Inc e80999e5dbf13cfb9b832fd4618154ca 100.0000 1
Candle Store Inc e80999e5dbf13cfb9b832fd4618154ca 103.0000 1
Local Coffee Roastery LLC e80999e5dbf13cfb9b832fd4618154ca 105.0000 1
Local Coffee Roastery LLC ebc3f4508048e3e673b89d1f963f2e7a 101.0000 1
我的查詢顯示所有結果，即使是低于 100 的結果。我怎樣才能讓我的查詢只顯示高于 100 的結果
uj5u.com熱心網友回復：
試試下面的查詢
select m.merchantName,t.hashedShopperReference, ROUND(AVG(t.riskScore),2) as averageRiskScore, COUNT(t.transactionId) as totalNumberofTransactions
from transaction as t
join merchant m on t.merchantId = m.merchantid
where t.hashedShopperReference IN(select distinct(hashedShopperReference)
from transaction where t.merchantId = m.merchantid)
group by t.transactionId
HAVING AVG(t.riskScore) >= 100.0
ORDER BY m.merchantName ASC, averageRiskScore DESC
uj5u.com熱心網友回復：
附加HAVING AVG(t.riskScore) >= 100.0到您向我們展示的查詢的最后，在 GROUP BY 之后但在任何 LIMIT 或 ORDER BY 子句之前。
uj5u.com熱心網友回復：
id name grocery price
1 milk Green Shop 2.34
2 bread Clark’s Grocery 3.56
3 bread Super Market 4.15
4 milk Super Market 1.80
5 bread Grocery Amanda 2.26
6 milk Violet Grocery 3.45
7 milk Clark’s Grocery 2.10
8 bread Violet Grocery 2.55
9 milk Grocery Amanda 1.95
讓我們找出雜貨中每種產品的平均價格高于 3.00 的產品名稱。
SELECT name, AVG(price)
FROM product
GROUP BY name
HAVING AVG(price)>3.00;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/418284.html
標籤：
上一篇：MySql存盤程序行問題