查询不包含指定的表达式"ID"作为聚合函数的一部分

Question

在MSAccess中，我有以下查询。当我尝试运行查询时，会出现错误Your query does not include the specified expression "ID" as part of aggregate function，但我找不到原因。我的查询有什么问题？

SELECT
Count(t2.subjectid) AS CountOfsubjectid,
t2.pname,
(
select
max(outcometime) 
from
table1 t1 
where
t1.id = t2.id 
)
AS showntime 
FROM
table2 AS t2 
WHERE
t2.outcome = "accepted" 
GROUP BY
t2.pname,
t2.showntime;

更新(样本数据(：

表1:

ID  outcometime pname  outcome  subjectid
1   20181111    USB    shown    Ux1ku
1   20181113    USB    shown    Ux1ku
2   20181115    USB    shown    Tsn2f
3   20181116    USB    shown    O93nf
2   20181114    USB    shown    Tsn2f
2   20181112    USB    shown    Tsn2f

表2:

ID  outcometime pname       outcome     subjectid
1   20181118    USB         accepted    Ux1ku
2   20181119    USB         accepted    Tsn2f
3   20181117    USB         accepted    O93nf

期望结果：

pname     showntime     countofsubjectid
USB       20181113         1
USB       20181115         1
USB       20181116         1

还更新了示例数据。这是错误的。谢谢

Answer 1

当前，您正试图在封装在聚合查询中的SELECT子句中运行相关子查询，然后在GROUP BY子句中通过别名引用此子查询。

考虑使用派生表首先使用子查询运行单元级，然后在外部主查询中运行聚合。

SELECT 
dt.pname,
COUNT(subjectid) AS CountOfsubjectid,
dt.showntime
FROM 
(SELECT
t2.subjectid
t2.pname,
(
select
max(outcometime) 
from
table1 t1 
where
t1.id = t2.id 
)
AS showntime 
FROM
table2 AS t2 
WHERE
t2.outcome = 'accepted'
) AS dt
GROUP BY
dt.pname,
dt.showntime;

但是，请考虑避免为表中的每一行运行低效的关联子查询，而不是在计算一次的MAX的聚合查询上联接，然后在主级别上再次为COUNT运行聚合。

SELECT
t2.pname,
COUNT(t.subjectid) AS CountOfsubjectid,
agg.showntime 
FROM
table2 AS t2 
INNER JOIN 
(
select 
t1.id,
max(outcometime) as showntime
from
table1 t1 
group by 
t1.id                     
) AS agg
ON t2.id = agg.id
WHERE
t2.outcome = 'accepted'    
GROUP BY
t2.pname,
agg.showntime;

Answer 2

如果我理解正确，您需要将其写成：

SELECT Count(t2.subjectid) AS CountOfsubjectid,
t2.name,
(select max(outcometime) 
from table1 as t1 inner join
table2 as tt2
on t1.id = tt2.id
where tt2.name = t2.name
) as showntime 
FROM table2 AS t2 
WHERE t2.outcome = "accepted" 
GROUP BY t2.name;