MySQL: How To Count Rows But Still Show All The Rows?

Posted by – October 21, 2013

之前寫過 MySQL: Displaying Rows With COUNT 0 With Group By,用於強制顯示 Count() 為 0 的資料。最近又看到另一種寫法,因為我自己很少這麼寫,所以留個筆記。

SELECT col1,
       (SELECT COUNT(*) 
          FROM table1 t1 
         WHERE t1.id = t2.t1_id) as Total
  FROM table2 t2
GROUP BY t2.id;

把子查詢放在 SELECT 敘述。試了一下,這種寫法的查詢速度會比單純的 left join 慢上許多,實際應用要視資料筆數來決定。

References:

Leave a Reply

Your email address will not be published. Required fields are marked *