如果我有这个表:Item(ItemID, descr, onhand, price)
我正在寻找低于所有其他价格平均水平的价格。因此,假设我有item 1
,那么我将它与2-5
项目的平均值进行比较,而不是1-5
项目的平均值。我知道这将是一个相关的子查询,但我无法弄清楚平均值。那么,如何从平均值中排除非特定项目呢?
编辑:正如@1010的转换所证明的那样,我们可以将项目价格与总平均值进行比较。
智能解决方案:
(提供与简单相同的结果,但在大数据上执行速度更快)
SELECT
t.ItemId,
t.descr,
t.onhand,
t.price
from Item t
JOIN (
SELECT AVG(price) AS price
FROM Item
) item_avg ON t.price < item_avg.price
简单的解决方案:
但是,这确实可以解决问题,目前我看不到优化它的方法(这是一个"执行计划杀手")
SELECT
t.ItemId,
t.descr,
t.onhand,
t.price
from Item t
WHERE t.price < (
SELECT AVG(T2.price)
FROM Item t2
WHERE t2.ItemId <> t.ItemID
)
对于具有此类值的表:
ItemId descr onhand price CalculatedAverage
1 desc1 1 23 40
2 desc2 1 5 44.5
3 desc3 1 100 20.75
4 desc4 1 35 37
5 desc5 1 20 40.75
它产生以下输出
ItemId descr onhand price
1 desc1 1 23
2 desc2 1 5
4 desc4 1 35
5 desc5 1 20
假设项目I有价格p_i,那么你想检查
p_i < (p_1 + ... + p_i-1 + p_i+1 + ... + p_n)/(n-1)
但它相当于
p_i * (n-1) < (p_1 + ... + p_n) - p_i
p_i * (n-1) + p_i < (p_1 + ... + p_n)
p_i * (n-1 + 1) < (p_1 + ... + p_n)
(p_i * n) / n < (p_1 + ... + p_n) / n
p_i < AVG(p)
因此,只需将每个价格与所有价格的平均值进行比较。这是相同的。
编辑
按照基里尔的建议,这是另一个查询,以使价格低于平均水平
SELECT ItemID, descr, onhand, price
FROM Item
WHERE price < (SELECT AVG(price) FROM Item)