Question

access sql 多表查询统计难题，请高手帮忙，谢谢！！！

表1
Class name id
一（1） 张三 10001
二（6） 李四 10004
三（4） 王二 10005

。。。。

表二
id type
10001 表扬
10001 表扬
10004 金奖
10001 金奖
10005 银奖
10004 表扬
10001 银奖

最终查询结果如下表
Class total(>=2) 表扬 金奖 银奖
一（1） 1 2 1 1
二（6） 0 2 1 1
三（4） 0 1

要求按班级排序统计 total 是该班级里得到2个以上荣誉的人头数COUNT，后面的就是各个荣誉的数量了SUM

select a.class,sum(d.total)as total,sum(e.表扬) as 表扬, sum(f.金奖) as 金奖,sum(g.银奖) as 银奖
from
表1 a,
(select id,iif(count(id)>=2,1,0) as total from 表2 group by id ) d ,
(select id,count(id)as 表扬 from 表2 where type='表扬' group by id) e ,
(select id,count(id)as 金奖 from 表2 where type='金奖' group by id) f ,
(select id,count(id) as 银奖 from 表2 where type='银奖' group by id) g
where a.id=d.id and a.id=e.id and a.id=f.id and a.id=g.id
group by a.class

这个查询语句 得出结果为：

Class total(>=2) 表扬 金奖 银奖
一（1） 1 2 1 1

就1行，其它班级没有了，请高手帮忙看看原因，谢谢！！

Answer 1

用外连接：
select a.class,sum(d.total)as total,sum(e.表扬) as 表扬, sum(f.金奖) as 金奖,sum(g.银奖) as 银奖
from 表1 a,
(select id,iif(count(id)>=2,1,0) as total from 表2 group by id ) d ,
(select id,count(id)as 表扬 from 表2 where type='表扬' group by id) e ,
(select id,count(id)as 金奖 from 表2 where type='金奖' group by id) f ,
(select id,count(id) as 银奖 from 表2 where type='银奖' group by id) g
where a.id=d.id(+) and a.id=e.id(+) and a.id=f.id(+) and a.id=g.id(+)
group by a.class

追问

运行不了哦~

追答

厄。。access的问题，试试这样写：
select a.class,sum(d.total)as total,sum(e.表扬) as 表扬, sum(f.金奖) as 金奖,sum(g.银奖) as 银奖
from 表1 a left outer join (select id,iif(count(id)>=2,1,0) as total from 表2 group by id ) d on a.id=d.id left outer join (select id,count(id)as 表扬 from 表2 where type='表扬' group by id) e on a.id=e.id left outer join (select id,count(id)as 金奖 from 表2 where type='金奖' group by id) f on a.id=f.id left outer join (select id,count(id) as 银奖 from 表2 where type='银奖' group by id) g on a.id=g.id group by a.class

追问

不行哦，语法问题~ 停在 a.class 这里

追答

把left outer jion全改成full outer join试试？
另外，先做一个的，看看可行否（比如a和d的）

Answer 2

select a.class,sum(d.total)as total,sum(e.表扬) as 表扬, sum(f.金奖) as 金奖,sum(g.银奖) as 银奖
from
表1 a
left join (select id,iif(count(id)>=2,1,0) as total from 表2 group by id ) d on a.id=d.id
left join (select id,count(id)as 表扬 from 表2 where type='表扬' group by id) e on a.id=e.id
left join (select id,count(id)as 金奖 from 表2 where type='金奖' group by id) f on a.id=f.id
left join (select id,count(id) as 银奖 from 表2 where type='银奖' group by id) g on a.id=g.id
group by a.class