谢谢大家。最终似乎我自己解决了问题。但我不确定是否正确。目前它显示了正确的答案。
原始的MySQL查询非常完美:
SELECT avg(final) as Final, SUM(Final>89.5) as a, SUM(Final<89.5 AND Final>79.5) as b, SUM(Final<79.5 AND Final>69.5) as c, SUM(Final<69.5 AND Final>59.5) as d , SUM(Final<59.5) as f, Ca.letter, Ca.number, subject_id FROM grades as G join courses as C on G.course_id = C.id join schoolclasses as Ca on Ca.id=C.schoolclass_id where C.subject_id = 1 and G.quarter_id in (1, 2) group by G.course_id
我尝试将其转换为Dql。不幸的是,由于你们使用了括号,无法在Doctrine的聚合函数中使用。这是我的解决方法。现在它可以从grades表中计算所有A、B、C、D、F的成绩。学校班级(字母和数字)信息是从其父表中检索到的。A、B、C、D、F成绩并不完全是我想要的字段。就像下面的图片一样。
![enter image description here](https://istack.dev59.com/JLtVt.webp)
这可能有点棘手。
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Ficus\EschoolBundle\Entity\Grade', 'g');
$rsm->addFieldResult('g', 'Final', 'final');
$rsm->addFieldResult('g', 'A', 'a');
$rsm->addFieldResult('g', 'B', 'b');
$rsm->addFieldResult('g', 'C', 'c');
$rsm->addFieldResult('g', 'D', 'd');
$rsm->addFieldResult('g', 'F', 'abcd');
$rsm->addFieldResult('g', 'Class', 'a1');
//$query = $this->getEntityManager()->createNativeQuery('SELECT avg(final) as Final, SUM(Final>89.5) as a, SUM(Final<89.5 AND Final>79.5) as b, SUM(Final<79.5 AND Final>69.5) as c, SUM(Final<69.5 AND Final>59.5) as d , SUM(Final<59.5) as f, Ca.letter, Ca.number, subject_id FROM grades as G join courses as C on G.course_id = C.id join schoolclasses as Ca on Ca.id=C.schoolclass_id where C.subject_id = 1 and G.quarter_id=1 group by G.course_id', $rsm);
$query = $this->getEntityManager()->createNativeQuery(''
. 'SELECT avg(g.final) as Final, '
. 'SUM(Final>89.5) as A, '
. 'SUM(Final<89.5 AND Final>79.5) as B, '
. 'SUM(Final<79.5 AND Final>69.5) as C, '
. 'SUM(Final<69.5 AND Final>59.5) as D, '
. 'SUM(Final<59.5) as F, '
. 'CONCAT(ca.number, ca.letter) as Class '
. 'FROM grades as g '
. 'JOIN courses as c on g.course_id = c.id '
. 'JOIN schoolclasses as ca on ca.id = c.schoolclass_id '
. 'WHERE c.subject_id = ? AND g.quarter_id in (?) group by g.course_id '
. 'ORDER BY ca.number, ca.letter ', $rsm);
$query->setParameter(1, $rid);
$query->setParameter(2, $filterQuarter);
$grades = $query->getArrayResult();
SUM(g.final > 89.5)
不等同于SUM(g.final) > 89.5
。 - M Khalid Junaid