MS ACCESS：如何使用访问查询来计算不同的价值？

一尘不染

MS ACCESS：如何使用访问查询来计算不同的价值？

sql

这是下面给出的当前复杂查询。

SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question
FROM ((Evaluation INNER JOIN Training ON Evaluation.ETCode=Training.TCode) INNER JOIN Answer ON Evaluation.ECode=Answer.ECode) INNER JOIN Questions ON Answer.QCode=Questions.QCode
GROUP BY Evaluation.ETCode, Answer.QCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.Tvenue, Answer.Answer, Questions.Question, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Questions.SL
ORDER BY Answer.QCode, Answer.Answer;

还有另一列Training.TCode。我需要计算不同的Training.TCode，有人可以帮助我吗？如果您需要更多信息，请告诉我

阅读 128

2021-05-05

共1个答案

一尘不染

尝试

select ..., count(distinct Training.Tcode) as ..., ...

编辑-现在请看一下…

采取以下SQL代码。第一个选择是SQL Server如何执行此操作，第二个查询应符合访问权限…

declare @t table (eCode int, tcode int)
insert into @t values(1,1)
insert into @t values(1,1)
insert into @t values(1,2)
insert into @t values(1,3)
insert into @t values(2,2)
insert into @t values(2,3)
insert into @t values(3,1)

select 
    ecode, count(distinct tCode) countof
from
    @t
group by
    ecode

select ecode, count(*)
from
    (select distinct tcode, ecode
    from  @t group by tcode, ecode) t
group by ecode

它返回以下内容：

ecode tcode
1       3 (there are 3 distinct tcode for ecode of 1)
2       2 (there are 2 distinct tcode for ecode of 2)
3       1 (there is 1 distinct tcode for ecode of 3)

2021-05-05