mysql - 如何在子查询中使用选择列别名？

Question

考虑这个查询:

SELECT DATE_FORMAT(t.date, "%Y") training_year, 
COUNT(i.id) nb_trainees, 
nb_canceled_trainings, 
nb_done_trainings, 
(nb_canceled_trainings / (nb_canceled_trainings + nb_done_trainings)) cancellation_rate
FROM (
    SELECT COUNT(`trainings_trainings`.`id`) nb_canceled_trainings
    FROM  `trainings_trainings`
    WHERE `trainings_trainings`.`id_status` = 5
    AND DATE_FORMAT(`trainings_trainings`.`date`, "%Y") = training_year
) nct, (
    SELECT COUNT(`trainings_trainings`.`id`) nb_done_trainings
    FROM  `trainings_trainings`
    WHERE `trainings_trainings`.`id_status` IN (6, 7)
    AND DATE_FORMAT(`trainings_trainings`.`date`, "%Y") = training_year
) ndt,
`trainings_inscriptions` i
INNER JOIN `trainings_trainings` t ON i.`id_training` = t.id
GROUP BY training_year;

问题是我无法在子查询中访问training_year。我怎样才能做到这一点？

我的第一次尝试是移动 SELECT 部分中的子查询，但这样做后我无法使用这些子查询的别名来计算取消率。

编辑

我得到了 @Jafar 查询和一些修改的预期结果:

SELECT current_year, nb_trainees, nb_canceled_trainings, nb_done_trainings, 
        (nb_canceled_trainings / (nb_canceled_trainings + nb_done_trainings)) cancellation_rate,
        (nb_trainees / nb_done_trainings) filling
FROM (
    SELECT COUNT(CASE WHEN `t`.`id_status` = 5 THEN 1 END) nb_canceled_trainings,
    COUNT(CASE WHEN `t`.`id_status` IN (6, 7) THEN 1 END) nb_done_trainings, (
        SELECT COUNT(`ti`.`id`) nb_trainees
        FROM `trainings_inscriptions` `ti`
        INNER JOIN `trainings_trainings` `tt` ON `ti`.`id_training` = `tt`.`id`
        WHERE DATE_FORMAT (`tt`.`date`, "%Y") = DATE_FORMAT (`t`.`date`, "%Y")
    ) nb_trainees, DATE_FORMAT (`t`.`date`, "%Y") current_year
    FROM `trainings_trainings` `t`
    GROUP BY DATE_FORMAT (`t`.`date`, "%Y")
) dummy;

这就是我得到的:

+------+----------+----------+------+--------+---------+
| year | trainees | canceled | done | ratio  | filling |
+------+----------+----------+------+--------+---------+
| 2012 |        0 |        0 |    0 | NULL   | NULL    |
| 2013 |       58 |        0 |    2 | 0.0000 | 29.0000 |
| 2014 |        2 |        1 |    0 | 1.0000 | NULL    |
+------+----------+----------+------+--------+---------+

Answer 1

您可以将别名放在表名前面并在子查询本身中使用它，如果您引用外部表，那么最好选择该字段然后与外部表连接。

<小时/>

EDIT v2.0更改了查询以获得所需的结果

    SELECT YEAR,
       TRAINEES,
       NCT,
       NDT,
       NCT / (NCT + NDT) RATIO
  FROM (  SELECT COUNT (CASE WHEN T.ID_STATUS = 5 THEN 1 END) NCT,
                 COUNT (CASE WHEN T.ID_STATUS IN (6, 7) THEN 1 END) NDT,
                 (SELECT COUNT (TI.ID) TRAINEES
                    FROM trainings_inscriptions TI
                   WHERE TI.id_training = T.ID)
                    TRAINEES,
                 DATE_FORMAT (T.date, "%Y") year
            FROM trainings_trainings t
        GROUP BY DATE_FORMAT (T.date, "%Y"))