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

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!

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.

You can try LEFT JOIN, this will show all users and replace page data by NULL if the user doesn't owns any, then sort by page. The distinct aims to avoid doubloons.

SELECT DISTINCT u.name, u.lastname FROM users u LEFT JOIN pages p ON u.id = p.user_id ORDER BY p.id;

Join both tables together then sort on pages.user_id

select *
from users u
left join pages p on u.id = p.user_id
order by p.user_id desc

You can easily add a column in your query with COUNT, so you know how many pages the user have.

SELECT u.id, u.name, u.lastname, count(p.id)
FROM USERS u
RIGHT JOIN PAGES p ON u.id = p.user_id
GROUP BY u.id