使用MySQL,我有三个表：
项目：
ID name
1 "birthday party"
2 "soccer match"
3 "wine tasting evening"
4 "dig out garden"
5 "mountainbiking"
6 "making music"
批次：
ID projectID templateID when
1 1 1 7 days before
2 1 1 1 day before
3 4 2 21 days before
4 4 1 7 days before
5 5 1 7 days before
6 3 5 7 days before
7 3 3 14 days before
8 5 1 14 days before
模板：
ID name message
1 inf1 "Hi,I'd like to invite ..."
2 for1 "Dear Sir,Madam,..."
3 can1 "Can you please ..."
4 inf2 "Would you like to ..."
5 all1 "To all dear friends ..."
6 inf3 "Does any of you guys ..."
我想显示一个模板表及其使用的项目数.
因此,结果应为(已更新！)：
templateName projectCount
inf1 3
for1 1
can1 1
inf2 0
all1 1
inf3 0
最佳答案
SELECT t.name templateName,COUNT(DISTICT b.projectID) projectCount
FROM templates t
LEFT OUTER JOIN batches b ON t.ID = b.templateID
GROUP BY t.ID,t.name
ORDER BY t.ID