MySQL在一个表中列出，在另一个表中的顺序

Question

I have 2 tables: "users" and "pages".

I need to list table "users" but IF user have own pages to first display users who have "pages" and after that to list others who don't have "pages".

USERS table:
id   |   name   |   lastname

PAGES table:
id   |  user_id   |    content

I don't know how to write query for this.

Thanks!

Answer 1

Something like this should get you close:

select users.id as user_id,
       users.name as user_name,
       users.lastname as user_lastname,
       count(*) as tot
  from users
  left join pages
    on pages.user_id = users.id
 group by user_id, user_name, user_lastname
 order by tot DESC, user_lastname, user_name

It should list the users with the most pages first, followed by the users with fewer pages, followed by the users with no pages, and within each page count, order the users alphabetically by last name and then user name.