SQL 多個各自COUNT之後還要JOIN成一張表 (在多個表格裡COUNT資料) [關聯子查詢 和 先查詢後變成一張表在跟原表整合(虛擬表+ LEFT JOIN)]

主表搜尋:

SELECT at.name AS name,ct.type_name AS type,at.level AS level,at.available_date_start AS start_time,at.available_date_end AS end_time FROM authorization_template AS at,card_type AS ct WHERE (at.type=ct.id);

主表+關聯子查詢:

SELECT at.name AS name,ct.type_name AS type,at.level AS level,at.available_date_start AS start_time,at.available_date_end AS end_time,(SELECT COUNT(*) FROM authorization_template_detailed AS atd WHERE atd.authorization_template_detailed_id=at.id) AS doornum FROM authorization_template AS at,card_type AS ct WHERE (at.type=ct.id);

主表+先查詢後變成一張表 [聽說這個比較快]:

SELECT at.name AS name,ct.type_name AS type,at.level AS level,at.available_date_start AS start_time,at.available_date_end AS end_time,atd.doornum AS doornum FROM authorization_template AS at,card_type AS ct,(SELECT authorization_template_detailed_id AS id ,COUNT(authorization_template_detailed_id) AS doornum FROM authorization_template_detailed GROUP BY authorization_template_detailed_id) AS atd WHERE (at.type=ct.id) AND (at.id=atd.id);

PS實際應用:  (主表 LEFT JOIN 子表 ON 條件1) WHERE 條件2  (虛擬表+ LEFT JOIN)

SELECT Data.id AS id,Data.name AS name,Data.type AS type,Data.level AS level,Data.start_time AS start_time,Data.end_time AS end_time,Data.doornum AS doornum FROM ( SELECT main.id AS id,main.name AS name,main.type AS type,main.level AS level,main.start_time AS start_time,main.end_time AS end_time,sub.doornum AS doornum FROM ( SELECT at.id AS id,at.name AS name,ct.type_name AS type,at.level AS level,at.available_date_start AS start_time,at.available_date_end AS end_time FROM authorization_template AS at,card_type AS ct WHERE (at.type=ct.id) ) AS main LEFT JOIN (SELECT authorization_template_detailed_id AS id ,COUNT(authorization_template_detailed_id) AS doornum FROM authorization_template_detailed GROUP BY authorization_template_detailed_id) AS sub ON (sub.id=main.id) ) AS Data  WHERE ((Data.name LIKE'%fuck%') OR (Data.type LIKE'%fuck%') OR (Data.level LIKE'%fuck%') OR (Data.start_time LIKE'%fuck%') OR (Data.end_time LIKE'%fuck%'));