mysql count的子查询,mySQL order by count子查询麻烦

I have a table called items_status which has 3 fields, item_id, user_id, and status, which can be either 'have' or 'want'.

Field Type Null Key

user_id varchar(10) NO PRI

item_id varchar(10) NO PRI

status set('have','want') YES NULL

I have a page where I want to get a list of all the user ids in the table ordered by the number of records their user id is associated with in the table where status is 'have'. So far, this is the best I can come up with:

SELECT user_id

FROM items_status AS is

ORDER BY

//Subquery to get number of items had by user

(SELECT COUNT(i.item_id)

FROM items_status AS i

WHERE i.user_id = is.user_id AND i.status = 'have') DESC

GROUP BY user_id

However, this pulls up an error on the subquery. How can I get all of the user ids in the table ordered by the number of items they have?

解决方案

you can do it like this:

SELECT user_id

FROM items_status

WHERE `status` = 'have'

GROUP BY userID

ORDER BY COUNT(user_id) DESC

with slight difference of column name but the query is the same