Mysql按其他表的结果排序（COUNT ROWS OF TABLE 2）[关闭]

Question

TABLE 1

SELECT * FROM page WHERE public = 1 order by*****results of table 2****** ASC

table 2

SELECT * FROM abonnement where page_id = $page_id AND (date > NOW() OR gratis = 1)

COUNT ROWS TABLE 2 AND PUT IT IN TABLE 1 ORDER BY....

Answer 1

It sounds like you need a group by and a left outer join:

SELECT p.*
FROM page p 
LEFT OUTER JOIN
     (SELECT page_id, COUNT(*) AS cnt
      FROM abonnement
      WHERE (date > NOW() OR gratis = 1)
      GROUP BY page_id
     ) a
     ON p.page_id = a.page_id
WHERE p.public = 1
ORDER BY a.cnt ASC;

The left outer join ensures that you do not lose any rows in page in case there are no matching rows in the second query.

Also note that I removed the condition page_id = $page_id. That is replaced by the on clause.

I would also suggest include a.cnt in the select clause so you actually see the count.