SQL顺序，在哪里放？(SQL order by, where to put it?)

SQL顺序，在哪里放？(SQL order by, where to put it?)

我有一个可用的sql语句，但是当我想添加Order By子句时，查询就会停止工作。

以下查询工作正常：

SELECT DISTINCT property.id , property.unid , property.imported , property.userid , CONCAT(user.firstname) as username , property.url , IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic , property.bedrooms , property.beds , type.meta_val as type , property.accommodates , property.price , IFNULL ( (SELECT thumbimg FROM tblpropertyimages WHERE pid = property.id LIMIT 1 ) , 'temp/misc/noimage.png' ) image , property.name as propertyname , ( SELECT SUM(rating) FROM tblreviews WHERE pid = property.id ) as totalrating , ( SELECT COUNT(id) FROM tblreviews WHERE pid = property.id) as countratings , location.name as cityname FROM tblproperty as property JOIN tbluser as user ON property.userid = user.id JOIN tblcommon as type ON property.type = type.id LEFT JOIN tblpropertyamenities as p_amenities ON property.id = p_amenities.pid JOIN tbllocation as location ON location.id = property.city WHERE property.status = 'Active' AND user.status = 'Active' AND property.price >= 0 AND property.price <= 10000 LIMIT 9 OFFSET 0

但是，如果我将此行添加到语句的末尾：

ORDER BY property.price ASC

然后查询停止工作，任何想法为什么这个ORDER BY子句导致错误？

I have an sql statement which works, but when I want to add Order By clause, then the query stops working.

The query below works fine:

SELECT DISTINCT property.id , property.unid , property.imported , property.userid , CONCAT(user.firstname) as username , property.url , IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic , property.bedrooms , property.beds , type.meta_val as type , property.accommodates , property.price , IFNULL ( (SELECT thumbimg FROM tblpropertyimages WHERE pid = property.id LIMIT 1 ) , 'temp/misc/noimage.png' ) image , property.name as propertyname , ( SELECT SUM(rating) FROM tblreviews WHERE pid = property.id ) as totalrating , ( SELECT COUNT(id) FROM tblreviews WHERE pid = property.id) as countratings , location.name as cityname FROM tblproperty as property JOIN tbluser as user ON property.userid = user.id JOIN tblcommon as type ON property.type = type.id LEFT JOIN tblpropertyamenities as p_amenities ON property.id = p_amenities.pid JOIN tbllocation as location ON location.id = property.city WHERE property.status = 'Active' AND user.status = 'Active' AND property.price >= 0 AND property.price <= 10000 LIMIT 9 OFFSET 0

However, If i add this line to the end of the statement:

ORDER BY property.price ASC

Then the query stops working, any idea why this ORDER BY clause is causing the error?

最满意答案

你需要在LIMIT之前放置ORDER BY ：

MYSQL - ORDER BY＆LIMIT

SELECT DISTINCT property.id,property.unid,property.imported,property.userid, CONCAT(user.firstname) as username,property.url, IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic, property.bedrooms,property.beds,type.meta_val as type,property.accommodates,property.price, IFNULL((select thumbimg from tblpropertyimages where pid=property.id limit 1),'temp/misc/noimage.png') as image, property.name as propertyname,(select sum(rating) from tblreviews where pid=property.id) as totalrating, (select count(id) from tblreviews where pid=property.id) as countratings, location.name as cityname from tblproperty as property join tbluser as user on property.userid=user.id join tblcommon as type on property.type=type.id left join tblpropertyamenities as p_amenities on property.id=p_amenities.pid join tbllocation as location on location.id=property.city WHERE property.status='Active' and user.status='Active' and property.price >= 0 and property.price <= 10000 ORDER BY property.price ASC limit 9 offset 0

查询应该是这样的，因为您先订购然后过滤限制。

我希望有所帮助！

You need to put ORDER BY before the LIMIT:

MYSQL - ORDER BY & LIMIT

SELECT DISTINCT property.id,property.unid,property.imported,property.userid, CONCAT(user.firstname) as username,property.url, IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic, property.bedrooms,property.beds,type.meta_val as type,property.accommodates,property.price, IFNULL((select thumbimg from tblpropertyimages where pid=property.id limit 1),'temp/misc/noimage.png') as image, property.name as propertyname,(select sum(rating) from tblreviews where pid=property.id) as totalrating, (select count(id) from tblreviews where pid=property.id) as countratings, location.name as cityname from tblproperty as property join tbluser as user on property.userid=user.id join tblcommon as type on property.type=type.id left join tblpropertyamenities as p_amenities on property.id=p_amenities.pid join tbllocation as location on location.id=property.city WHERE property.status='Active' and user.status='Active' and property.price >= 0 and property.price <= 10000 ORDER BY property.price ASC limit 9 offset 0

The query should be something like that, because you first order and then you filter with limit.

I hope that helps!