access SQL 多表查询统计问题，烦请帮忙看一下，谢谢你！

select a.class,
(select count(*) from biao2 c where a.id = c.id group by c.id having count(*)>=2) as 'total>=2',
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from biao1 a inner join biao2 b on a.id = b.id
group by a.class ,a.id

和之前的写法类似，测试一下

select a.class,
sum(iif(select count(*) from table2 c where a.id = c.id >= 2,1,0)) as total,
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from table1 a inner join table2 b on a.id = b.id
group by a.class追问

sum(IIf((select count(*) from 表2 c where a.id = c.id)>=2,1,0)) as total, 这句有问题，其它句没问题

追答

试试这个

select a.class,
count(d.id) as total,

sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from table1 a inner join table2 b on a.id = b.id
left join ( select id from table2 group by id having count(*) >= 2) as d on a.id = d.id
group by a.class

追问

a.id = b.id
left join ( select id from table2 group by id having count(*) >= 2) as d on a.id = d.id

你好，提示上门这句语法有问题，麻烦你了~

追答

打个括号试下
select a.class,
count(d.id) as total,
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from （table1 a inner join table2 b on a.id = b.id）
left join ( select id from table2 group by id having count(*) >= 2) as d on a.id = d.id
group by a.class

追问

可以运行，但结果TOTAL 数值不对

Class total(>=2) 表扬 金奖 银奖
一（1） 4 2 1 1
二（6） 2 1 1 0
三（4） 0 0 0 1

total 好像计算的是总数，大于2的才显示出来

问题还是处在 TOTAL 的计算，麻烦你帮忙研究一下，谢谢~

追答

select a.class,
count(distinct d.id) as total,
sum(IIf(b.type = '表扬',1,0)) as 表扬,
sum(IIf(b.type = '金奖',1,0)) as 金奖,
sum(IIf(b.type = '银奖',1,0)) as 银奖
from （table1 a inner join table2 b on a.id = b.id）
left join ( select id from table2 group by id having count(*) >= 2) as d on a.id = d.id
group by a.class

来自：求助得到的回答本回答被网友采纳

使用Access里的交叉表查询就可以。
type作为分组的列，class作为分组的行。id作为值，计数。不用写SQL语句。