我想执行以下查询
SELECT t1.patient_report, COUNT(*) AS cnt, t1.doctor_report, (SELECT t2.doctor_report FROM infoTable t2 WHERE t2.patient_report = t1.patient_report AND cnt > 1 LIMIT 3) AS Doctors FROM infoTable t1 WHERE t1.patient_report != 'N/A' GROUP BY t1.patient_report ORDER BY cnt DESC
但是我得到了这个错误!
Result: no such column: cnt
请问我该如何解决问题?
子查询无法访问外部查询的别名列cnt
。
但即使它确实有访问权限,也会抛出另一个错误,因为子查询可能返回超过 1 行。
我认为您可以使用窗口函数和GROUP_CONCAT()
做您想做的事:
SELECT t.patient_report, t.cnt,
GROUP_CONCAT(doctor_report) AS Doctors
FROM (
SELECT patient_report,
doctor_report,
ROW_NUMBER() OVER (PARTITION BY patient_report) rn,
COUNT(*) OVER (PARTITION BY patient_report) cnt
FROM infoTable
WHERE patient_report <> 'N/A'
) t
WHERE t.rn <= 3 AND t.cnt > 1
GROUP BY t.patient_report, t.cnt
ORDER BY t.cnt DESC