i have little bit long select, where I join many tables. What i need to do: I'm selecting lowest price with
我有点长的选择,我加入很多桌子。我需要做什么:我选择最低价格
SELECT MIN(accomodation_price.price) AS price_from
Everything works great till i want to order results by price_from. Ok i know i can't use ORDER BY price_from so i tried to use ORDER BY MIN(accomodation_price.price) but i want to use it in CASE. My select works good but it applies first results, which doesn't have any value for MIN(accomodation_price.price)... First i want results with some value, then others...
一切都很好,直到我想通过price_from订购结果。好吧我知道我不能使用ORDER BY price_from所以我试图使用ORDER BY MIN(accomodation_price.price)但我想在CASE中使用它。我的选择很好但是它应用了第一个结果,它没有MIN的任何值(accomodation_price.price)...首先我想要一些有价值的结果,然后其他...
Here is my code, row with MIN.... is not working and i don't know why. I tried also to use WHERE IS NOT NULL but the same....
这是我的代码,行与MIN ....不工作,我不知道为什么。我也尝试使用WHERE IS NOT NULL但是相同....
ORDER BY
CASE
WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() THEN 0
WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' THEN 1
WHEN MIN(accomodation_price.price) > '0' THEN 2
ELSE 3 END";
1 个解决方案
#1
1
what you should do is include that field (along with CASE) in result dataset, wrap it with yet another select and then order by that field. Something like:
你应该做的是在结果数据集中包含该字段(以及CASE),用另一个选择包装它,然后按该字段排序。就像是:
select * from
(select field1,field2,...,
(CASE
WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() THEN 0
WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' THEN 1
WHEN MIN(accomodation_price.price) > '0' THEN 2
ELSE 3 END) as field3 from table1,table2...,tableN) as R
order by R.field3
#1
1
what you should do is include that field (along with CASE) in result dataset, wrap it with yet another select and then order by that field. Something like:
你应该做的是在结果数据集中包含该字段(以及CASE),用另一个选择包装它,然后按该字段排序。就像是:
select * from
(select field1,field2,...,
(CASE
WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() THEN 0
WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' THEN 1
WHEN MIN(accomodation_price.price) > '0' THEN 2
ELSE 3 END) as field3 from table1,table2...,tableN) as R
order by R.field3