mysql - 加入条件以提取mysql中的信息

我有2张桌子Vizite（外键），Medici（主键），我需要提取2017年咨询少于5位患者的医生名单。我这样做，但是我不知道如何设置条件才能获得预期的结果。

select concat(Nume,' ',Prenume) as 'Nume Medic',
    count(Pacienti_Idp) As 'Nr vizite',
    year(DataOra) as An
    from medici
    inner join vizite on medici.idm = vizite.medici_idm
    group by (Pacienti_Idp);

最佳答案

您应该按medici分组并使用

 select concat(Nume,' ',Prenume) as `Nume Medic`,
    count(Pacienti_Idp) As `Nr vizite`,
    year(DataOra) as An
from medici
inner join vizite on medici.idm = vizite.medici_idm
group by concat(Nume,' ',Prenume),  year(DataOra)
having  count(Pacienti_Idp) < 5

或在聚合函数中使用别名

 select concat(Nume,' ',Prenume) as `Nume Medic`,
    count(Pacienti_Idp) As `Nr vizite`,
    year(DataOra) as An
from medici
inner join vizite on medici.idm = vizite.medici_idm
group by c`Nume Medic`, An
having  `Nr vizite`< 5

PS不在列名..（这些区域用作文字）周围使用单个队列，在需要时使用反引号

    backtics: `