我有一个包含3列的表格:list_num,sales_price和days_on_market
我想运行一个查询,该查询将为“ days_on_market”列的平均值输出一个数字,其中按“ sales_price”排序时删除的行的顶部5%和底部5%.
我找到了一个我认为可以帮助解决问题的示例,但是答案太复杂了,以至于我无法理解(我上周才开始学习mySQL).参见示例here.
这是我的代码:
SELECT round(avg(days_on_market),0) as "90% Sell In"
FROM sold_q3_2016
WHERE list_num NOT IN (
SELECT list_num FROM sold_q3_2016 ORDER BY sales_price LIMIT (count(list_num)*0.05)
) OR list_num NOT IN (
SELECT list_num FROM sold_q3_2016 ORDER BY sales_price desc LIMIT (count(list_num)*0.05)
)
;
现在,我什至不能运行它,因为我在LIMIT子句中得到了count函数周围的语法错误,但是我想把它留在这里以显示我的思考过程.有任何想法吗?
解决方法:
试试看:
SELECT AVG(days_on_market)
FROM (
SELECT
l.*
, @rownumber := @rownumber + 1 AS rownumber
FROM list_num l
, (SELECT @rownumber := 0) var_init_subquery
ORDER BY sales_price DESC
) subquery_alias
WHERE rownumber >= @rownumber * 0.05
AND rownumber <= @rownumber * 0.95
标签:mysql
来源: https://codeday.me/bug/20191111/2018952.html