(SQL 子查詢 次數統計)-使用『關聯子查詢』實現查詢A表ID在B表出現的次數[EX:一個人領了幾張門禁卡]

 

 

資料來源:http://jashliao.pixnet.net/blog/post/224937269-SQL%20%E5%AD%90%E6%9F%A5%E8%A9%A2%20%E6%AC%A1%E6%95%B8%E7%B5%B1%E8%A8%88-%5BSQL~%E9%97%9C%E8%81%AF%E5%AD%90%E6%9F%A5%E8%A9%A2%5D%20%E5%A4%9A%E5%80%8B%E5%90%84%E8%87%AAcount%E4%B9%8B%E5%BE%8C%E9%82%84%E8%A6%81join%E6%88%90%E4%B8%80%E5%BC%B5%E8%A1%A8%20(%E5%9C%A8%E5%A4%9A%E5%80%8B%E8%A1%A8%E6%A0%BC%E8%A3%A1count%E8%B3%87%E6%96%99)

 

SELECT u.id AS id,u.emp_no AS jobnum,u.security_id AS s_id,u.name AS name, d.name AS dname,u.attribute AS attribute,u.birthday AS birthday,(SELECT COUNT(*) FROM card_for_user_car WHERE card_for_user_car.user_id=u.id) AS card_count 
FROM user AS u 
LEFT JOIN department_detail AS d_d ON ((u.id=d_d.user_id) AND ((d_d.car_id IS NULL) OR (d_d.car_id <1))) 
LEFT JOIN department AS d ON (d_d.dep_id=d.id) 
ORDER BY u.id;