联接中ON子句中的集合函数
联接中ON子句中的集合函数
提问于 2019-02-15 14:31:33
为什么Postgres不允许我在联接中使用聚合函数？
SELECT p.id, p.actual_price,
h1.min_price, h1.max_price
FROM (
SELECT min(price) as min_price, max(price) as max_price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id
) AS h1,
products p
JOIN (
SELECT max(timestamp), price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id, price
) AS h2
ON h2.product_id = p.id
AND h2.price = h1.min_price
WHERE
p.id = h1.product_id
GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
ORDER BY p.id;错误:列" min_price“不存在第19行:和min_price^提示:表"h1”中有一个名为"min_price“的列，但是不能从查询的这一部分引用它。
回答 3
Stack Overflow用户
回答已采纳
发布于 2019-02-15 14:37:13
请检查我的代码下面，我已经用适当的解释标记了问题。
SELECT p.id,
p.actual_price,
min_price,
max_price
FROM (SELECT Min(price) AS min_price,
Max(price) AS max_price,
product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id) AS h1,
products p ,
join (SELECT Max(timestamp),
price,
product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id,
price) AS h2
ON p.id = h2.product_id
AND price = min_price -- <= This join is same level with subquery h1.
-- you cannot use min_price here
WHERE p.id = h1.product_id
GROUP BY p.id,
min_price,
max_price,
p.actual_price
ORDER BY p.id; 为了解决这个问题，我还用适当的内部连接替换了遗留的逗号分隔连接。
with h1 as
(
SELECT Min(price) AS min_price,
Max(price) AS max_price,
product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id
), h2 as
(
SELECT Max(timestamp),
price,
product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id,
price
)
SELECT p.id,
p.actual_price,
min_price,
max_price
FROM h1,
inner join products p
on p.id = h1.product_id
join h2
ON p.id = h2.product_id
AND price = min_price
GROUP BY p.id,
min_price,
max_price,
p.actual_price
ORDER BY p.id; Stack Overflow用户
发布于 2019-02-15 19:54:19
当然，除了现有的答案之外，您还可以使用派生表使用原始查询，只需将隐式更改为显式联接：
SELECT p.id, p.actual_price,
h1.min_price, h1.max_price
FROM (
SELECT min(price) as min_price, max(price) as max_price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id
) AS h1
JOIN
products p
ON p.id = h1.product_id
JOIN (
SELECT max(timestamp), price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id, price
) AS h2
ON h2.product_id = p.id
AND h2.price = h1.min_price
GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
ORDER BY p.id; Stack Overflow用户
发布于 2019-02-15 14:37:40
您使用的是“表表达式”。表表达式(如h2)不能引用以前的表表达式(如h1)或其列，但JOIN子句中除外。
如果希望h2使用h1，则使用公共表表达式(简称CTE)。您的查询可以改为：
with
h1 as (
SELECT min(price) as min_price, max(price) as max_price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id
),
h2 as (
SELECT max(timestamp), price, product_id
FROM prices_history
WHERE timestamp > '2019-01-01'
GROUP BY product_id, price
)
SELECT p.id,
p.actual_price,
h1.min_price, h1.max_price
from products p
join h2 ON p.id = h2.product_id
join h1 on h2.price = h1.min_price
and p.id = h1.product_id
GROUP BY p.id, h1.min_price, h1.max_price, p.actual_price
ORDER BY p.id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54711464
复制相关文章
相似问题
腾讯云开发者
