你基本上需要两个层次
GROUP BY
. 所以,我们需要在这里使用派生表。第一个子查询将在以下级别聚合
fee
;然后第二级将这些费用的详细信息汇总到
student
.
此外,在MySQL的较新(和符合ANSI SQL的)版本中,需要确保
SELECT
子句应位于
小组通过
条款也一样。
查询
SELECT
CONCAT(stud_ttl,'-',GROUP_CONCAT(CONCAT(fee_det, COALESCE(CONCAT('->',fee_sch), '')))) AS fee
FROM
(
SELECT student.ttl AS stud_ttl,
CONCAT(fee.id,'.',fee.ttl) AS fee_det,
Group_concat(DISTINCT sch.ttl) AS fee_sch
FROM inv_id
JOIN student
ON student.id = inv_id.std
JOIN inv_lst
ON inv_lst.ftm = inv_id.ftm
JOIN fee
ON fee.id = inv_lst.fee
JOIN sec_fee
ON sec_fee.fee = fee.id
AND sec_fee.cls = student.cls
AND sec_fee.sec = student.sec
LEFT JOIN std_sch
ON std_sch.std = student.id
LEFT JOIN sec_sch
ON sec_sch.sch = std_sch.sch
AND sec_sch.fee = fee.id
LEFT JOIN sch
ON sch.id = sec_sch.sch
GROUP BY student.ttl, fee_det, fee.ttl
) dt
GROUP BY stud_ttl;
结果
| fee |
| -------------------------------------------------------------------- |
| Karan-1.Annual->Economic Scholarship,Incapable Scholarship,2.Monthly |
View on DB Fiddle