时间戳

首页 > 解决方案 > 平均售价中的Mysql排序语句

mysql - 平均售价中的Mysql排序语句

问题描述

编写一 (1) 个 MySQL 查询语句，该语句返回该机构运营的每个州在过去一个月中“签订合同”的物业的平均销售价格。结果应按平均售价从高到低排序。您的查询应返回我尝试过的以下格式的表格

select * from(
select   avg(sale_price)  from  purchase where property_id = 64
union all  (
select   avg(sale_price)  from  purchase where property_id = 60)
union (
select avg(sale_price) from purchase where property_id = 58))  as i
order by  sale_price  ASC;

我收到此错误，因为我的表中有 sale_price 列

22:08:19 select * from( select avg(sale_price) from purchase where property_id = 64 union all ( select avg(sale_price) from purchase where property_id = 60) union ( select avg(sale_price) from purchase where property_id = 58))当我按 sale_price ASC LIMIT 0 订购时，1000 错误代码：1054。“订单条款”中的未知列“sale_price”0.000 秒

标签： mysqlsql

解决方案

您应该在每个 UNION 的 SELECT 子句中使用别名。

SELECT 
  * 
FROM
  (SELECT 
    AVG(sale_price) AS sale_price 
  FROM
    purchase 
  WHERE property_id = 64 
  UNION
  ALL 
  (SELECT 
    AVG(sale_price)  AS sale_price 
  FROM
    purchase 
  WHERE property_id = 60) 
  UNION
  (SELECT 
    AVG(sale_price)  AS sale_price
  FROM
    purchase 
  WHERE property_id = 58)) AS i 
ORDER BY sale_price ASC ;

或者您可以使用以下查询吗？我认为这在性能方面更直接。

SELECT 
  AVG(sale_price) AS avg_sale_price 
FROM
  purchase 
WHERE property_id IN (64, 60, 58) 
GROUP BY property_id 
ORDER BY avg_sale_price ;

推荐阅读