子查询中的 mySQL 错误 1064

Question

我知道这可能很愚蠢，但我已经花了 2 个小时来寻找错误，而且我的截止日期已经不多了。你能帮忙吗？

这是代码

create view Children as
select avg(chAvg) as Avg_Children
from
(select CHILDREN.ID,
CHILDREN.PAT_ID,
CHILDREN.GENDER,
DRUGS.DRUG_TYPE,
count(CHILDREN.ID)/count(distinct(CHILDREN.PAT_ID)) as 'chAvg'
from CHILDREN
join PATIENTS
on CHILDREN.PAT_ID = PATIENTS.PATIENT_ID
join CASES
on PATIENTS.PATIENT_ID=CASES.PAT_ID
join DRUGS_TO_CASES
on CASES.CASE_ID = DRUGS_TO_CASES.CASE_ID
join DRUGS
on DRUGS_TO_CASES.DRUG_ID=DRUGS.ID
where DRUGS.DRUG_TYPE = 'tranquillisers'
group by CHILDREN.GENDER)

这是 Workbench 所说的:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 19

Answer 1

在子查询上使用别名并尽量避免与表同名，即使 mysql 允许也是如此

create view VChildren as
select avg(chAvg) as Avg_Children
from
(select CHILDREN.ID,
CHILDREN.PAT_ID,
CHILDREN.GENDER,
DRUGS.DRUG_TYPE,
count(CHILDREN.ID)/count(distinct(CHILDREN.PAT_ID)) as 'chAvg'
from CHILDREN
join PATIENTS
on CHILDREN.PAT_ID = PATIENTS.PATIENT_ID
join CASES
on PATIENTS.PATIENT_ID=CASES.PAT_ID
join DRUGS_TO_CASES
on CASES.CASE_ID = DRUGS_TO_CASES.CASE_ID
join DRUGS
on DRUGS_TO_CASES.DRUG_ID=DRUGS.ID
where DRUGS.DRUG_TYPE = 'tranquillisers'
group by CHILDREN.GENDER)x