父级按子表数量排序

Question

I have a tbl_user , which contains information about user, and I have a tbl_article, which contains articles + the ID of the tbl_user

We have a parent-child relation, because every user may have many articles, that's why I included user_id in the articles table.

I'd like to list the 10 users that have most articles... I've searched everywhere though I couldn't find it...I've thought about it , but in vain, I'm not good in SQL Queries.

Thank you in advance

Answer 1

SELECT TOP(10)
  tbl_user.id,
  COUNT(tbl_article.user_id)
FROM
  tbl_user
LEFT JOIN
  tbl_article
    ON tbl_user.id = tbl_article.user_id
GROUP BY
  tbl_user.id
ORDER BY
  COUNT(tbl_article.user_id) DESC
LIMIT
  10

Depending on which RDBMS you use, you may need TOP(10) or LIMIT 10, etc. I included both so you can see, but only use the one that is used by your RDBMS ;)