对月份和年份进行分组时出现外部参考错误

Question

获取以下错误：

Each GROUP BY expression must contain at least one column that is not an outer reference

我想要一个按月份、月份和年份分组的calculatedDrugDistributionHistory列表。我只想盘点一下哪些设施在哪些月份被导入了我们的系统。

我有这个问题：

select 
    f.name,
    MONTH(cddh.dateGiven) as 'date_month',
    YEAR(cddh.dateGiven) as 'date_year' 
from 
    calculatedDrugDistributionHistory cddh
        inner join facilityIndividuals fi on fi.facilityIndividualId = cddh.facilityIndividualId
        inner join facilities f on fi.facilityId = f.facilityId
group by 
    f.name,
    'date_month',
    'date_year'
order by
    f.name,
    'date_month',
    'date_year'

Answer 1

SELECT
name,
date_month,
date_year 
FROM
(select F.NAME,MONTH(cddh.dateGiven) as date_month,
    YEAR(cddh.dateGiven) as date_year
from 
    calculatedDrugDistributionHistory cddh
        inner join facilityIndividuals fi on fi.facilityIndividualId = cddh.facilityIndividualId
        inner join facilities f on fi.facilityId = f.facilityId)TEMP
group by 
    name,
    date_month,
    date_year
order by
    name,
    date_month,
    date_year

请使用此更新

问候

Ashutosh Arya

Answer 2

您不能在group-by子句中使用别名。试试这个方法：

select 
    f.name,
    MONTH(cddh.dateGiven) as date_month,
    YEAR(cddh.dateGiven) as date_year 
from calculatedDrugDistributionHistory cddh
inner join facilityIndividuals fi on fi.facilityIndividualId = cddh.facilityIndividualId
inner join facilities f on fi.facilityId = f.facilityId
group by 
    f.name,
    MONTH(cddh.dateGiven),
    YEAR(cddh.dateGiven) 
order by
    f.name,
    date_month,
    date_year

Answer 3

两个问题。首先，对列别名使用单引号。使用双引号或方括号。第二，您需要在select子句中包含所有列，而不需要聚合函数。应该在group by:中

select f.name, MONTH(cddh.dateGiven) as date_month, YEAR(cddh.dateGiven) as date_year'
from calculatedDrugDistributionHistory cddh
        inner join facilityIndividuals fi on fi.facilityIndividualId = cddh.facilityIndividualId
        inner join facilities f on fi.facilityId = f.facilityId
group by  f.name, MONTH(cddh.dateGiven), YEAR(cddh.dateGiven)
order by f.name, date_month, date_year;

您可以在order by中使用列别名，但不能在group by中使用。

Answer 4

你不能在GROUP BY中使用别名，如果可以的话，你不应该用单引号写它们：

select 
    f.name,
    MONTH(cddh.dateGiven) as date_month,
    YEAR(cddh.dateGiven) as date_year 
from 
    calculatedDrugDistributionHistory cddh
        inner join facilityIndividuals fi on fi.facilityIndividualId = cddh.facilityIndividualId
        inner join facilities f on fi.facilityId = f.facilityId
group by 
    f.name,
    MONTH(cddh.dateGiven),
    YEAR(cddh.dateGiven)
order by
    f.name,
    date_month,
    date_year