sql—平均多个字段，同时忽略access 2016中的空格

n53p2ov0 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(316)

我试图在access 2016中平均每个记录中的多个字段。我希望能够将查询导入excel进行进一步分析。
有些记录包含我想忽略的空白字段。我第一次尝试使用nz，它在access中工作，但无法导入excel。
然后我尝试了以下方法：

SELECT Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA, IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA, (iif(isnull(B365H))+ iif(isnull(BWH))+ iif(isnull(IWH))+ iif(isnull(LBH))+ iif(isnull(PSH))+ iif(isnull(WHH))+ iif(isnull(VCH)))/7 AS AvgHome
FROM [Bund]
GROUP BY Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA, IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA;

但是得到一个“错误数量的参数用于查询表达式中的函数”错误。
有人知道问题是什么吗？
谢谢

sql ms-access ms-access-2016

来源：https://stackoverflow.com/questions/62891139/average-multiple-fields-whilst-ignoring-blanks-in-access-2016

3条答案

按热度按时间

x4shl7ld1#

您可以使用不为空：

Abs((B365H Is Not Null) + (BWH Is Not Null) + (IWH Is Not Null) + (LBH Is Not Null) + (PSH Is Not Null) + (WHH Is Not Null) + (VCH Is Not Null)) / 7

赞(0）回复(0）举报 2021-07-26

u5i3ibmn2#

试试下面

SELECT Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA, IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA, (Nz(B365H,0)+ Nz(BWH,0)+ Nz(IWH,0)+ Nz(LBH,0)+ Nz(PSH,0)+ Nz(WHH,0)+ Nz(VCH,0))/7 AS AvgHome
FROM [Bund]
GROUP BY Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA, IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA;

赞(0）回复(0）举报 2021-07-26

at0kjp5o3#

您没有使用聚合函数，因此 select distinct 更简单。
那么，如果你想治疗 NULL 值为 0 ，您可以使用 nz() :

SELECT DISTINCT Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA,
       IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA,
       (nz(B365H, 0) + nz(BWH, 0) + nz(IWH, 0) + nz(LBH, 0) + nz(PSH, 0) + nz(WHH, 0) + nz(VCH, 0))/7 AS AvgHome
FROM [Bund];

如果你想忽略 NULL 值，则还需要处理分母：

SELECT DISTINCT Date, HomeTeam, AwayTeam, FTR, B365H, B365D, B365A, BWH, BWD, BWA,
       IWH, IWD, IWA, LBH, LBD, LBA, PSH, PSD, PSA, WHH, WHD, WHA, VCH, VCD, VCA,
       (nz(B365H, 0) + nz(BWH, 0) + nz(IWH, 0) + nz(LBH, 0) + nz(PSH, 0) + nz(WHH, 0) + nz(VCH, 0)) /
        (iif(B365H is null, 0, 1) + iif(BWH is null, 0, 1) + iif(IWH is null, 0, 1) + iif(LBH is null, 0, 1) + iif(PSH is null, 0, 1) + iif(WHH is null, 0, 1) + iif(VCH is null, 0, 1))
       )  AS AvgHome
FROM [Bund];

赞(0）回复(0）举报 2021-07-26

我来回答

sql—平均多个字段，同时忽略access 2016中的空格

3条答案

相关问题

热门标签

最新问答