我想提取在两个单独的月份（3月和4月）中发生的“表演”中“教练”的出勤人数（即COUNT（））。我设法创建了一个查询，该查询仅在一个月内收集了该数字。另外，通过稍微修改查询，可以轻松找到第二个月的数字。但是，如何将它们合并到一个包含两列的表中？
因此，给定下面的两个查询和结果表，如何将查询2的结果“追加”到查询1的结果？换句话说，将如何组合它们各自的SELECT语句？
我提供了指向SQL小提琴的链接，以备需要时使用。
先感谢您。
查询1：
SELECT C.*, COUNT(CIS.idCoach) AS MarchNumOfShows
FROM Coach AS C
LEFT JOIN
(
CoachInShow AS CIS
LEFT JOIN
TVShow AS S
ON S.idShow = CIS.idShow
)
ON C.idCoach = CIS.idCoach AND S.airDate LIKE '_____04___'
GROUP BY C.idCoach
结果：
| idCoach | name | surname | MarchNumOfShows |
|---------|-----------|---------|-----------------|
| 1 | Stephen | Hawking | 5 |
| 2 | Nicholas | Cage | 7 |
| 3 | Sigourney | Weaver | 6 |
查询2（最小差异，查询4月而不是3月）：
SELECT COUNT(CIS.idCoach) AS AprilNumOfShows
FROM Coach AS C
LEFT JOIN
(
CoachInShow AS CIS
LEFT JOIN
TVShow AS S
ON S.idShow = CIS.idShow
)
ON C.idCoach = CIS.idCoach AND S.airDate LIKE '_____05___'
GROUP BY C.idCoach
结果：
| AprilNumOfShows |
|-----------------|
| 8 |
| 7 |
| 10 |
想要：
| idCoach | name | surname | MarchNumOfShows | AprilNumOfShows |
|---------|-----------|---------|-----------------|-----------------|
| 1 | Stephen | Hawking | 5 | 8 |
| 2 | Nicholas | Cage | 7 | 7 |
| 3 | Sigourney | Weaver | 6 | 10 |
您非常亲密，错过的最后一步是simply combine MarchNumOfShows and AprilNumOfShows with left join。
像下面的代码（或查看Sql Fiddle）：
SELECT C.idCoach, C.name, C.surname, COUNT(distinct CIS4.idShow) AS MarchNumOfShows
, COUNT(distinct CIS5.idShow) AS AprilNumOfShows
FROM Coach AS C
LEFT JOIN
(
CoachInShow AS CIS4
LEFT JOIN
TVShow AS S4
ON S4.idShow = CIS4.idShow
)
ON C.idCoach = CIS4.idCoach AND S4.airDate LIKE '_____04___'
LEFT JOIN
(
CoachInShow AS CIS5
LEFT JOIN
TVShow AS S5
ON S5.idShow = CIS5.idShow
)
ON C.idCoach = CIS5.idCoach AND S5.airDate LIKE '_____05___'
GROUP BY C.idCoach;
下面是获取相同输出（或查看SQL Fiddle）的另一种方法：
SELECT C.idCoach, C.name, C.surname,
sum(case when DATE_FORMAT(airDate,'%M')='April' then 1 else null end ) AS AprilNumOfShows,
sum(case when DATE_FORMAT(airDate,'%M')='May' then 1 else null end ) AS MayNumOfShows
FROM Coach AS C
LEFT JOIN
(
CoachInShow AS CIS
LEFT JOIN
TVShow AS S
ON S.idShow = CIS.idShow
)
ON C.idCoach = CIS.idCoach
GROUP BY C.idCoach;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句