大哥们帮帮忙啊！!!

[wj7907]

有两个表，其结构如下：
tblOperater

OperaterNo OperaterName
-----------------------------------------
1 王一
2 张三
3 李四
4 麻五
...........


tblBallot

OperaterNo BallotLevel
-----------------------------------------
1 3
1 4
2 4
3 3
1 1
3 2
3 4
2 4
1 3
..........
（每行为一条记录，记为一票）


tblBallot.BallotLevel字段为投票类别，其值为1、2、3、4四种，对应（下表）Level1、Level2、Level3、Level4，现欲统计出一张结构如下的表：


Name Total Level1 Level2 　　　　Level3 Level4
--------------------------------------------------------------
王一 总67票 9% 5% 7% 79%

................

请问这个语句应怎样做？
我的数据库用的是Access。

[dlkfth]

select OperaterName as name ,
count(1) as Total,sum(case when b.BallotLevel=1 then 1 else 0 end) as Level1,
sum(case when b.BallotLevel=2 then 1 else 0 end) as Level2,
sum(case when b.BallotLevel=3 then 1 else 0 end) as Level3
,sum(case when b.BallotLevel=4 then 1 else 0 end) as Level4
from tblOperater a inner join tblBallot b on a.OperaterNo=b.OperaterNo
group by a.OperaterNo,b.OperaterName

[pengdali]

select tblOperater.OperaterNo,count(*) Total,sum(iif(tblBallot.BallotLevel=1,1,0)) Level1,sum(iif(tblBallot.BallotLevel=2,1,0)) Level2,sum(iif(tblBallot.BallotLevel=3,1,0)) Level3,sum(iif(tblBallot.BallotLevel=4,1,0)) Level4 from tblOperater join tblBallot on tblOperater.OperaterNo=tblBallot.OperaterNo group by tblOperater.OperaterNo,tblOperater.OperaterName

[CrazyFor]

select a.OperaterName,(level1+level2+level3+level4) as Total
,cast((level*100)/(level1+level2+level3+level4) as varchar)+'%' as Level1
,cast((leve2*100)/(level1+level2+level3+level4) as varchar)+'%' as Level2
,cast((leve3*100)/(level1+level2+level3+level4) as varchar)+'%' as Level3
,cast((leve4*100)/(level1+level2+level3+level4) as varchar)+'%' as Level4
from tblOperater a left join
(
select OperaterNo
,sum(case when BallotLevel=1 then 1 else 0 end) Level1
,sum(case when BallotLevel=2 then 1 else 0 end) Level2
,sum(case when BallotLevel=3 then 1 else 0 end) Level3
,sum(case when BallotLevel=4 then 1 else 0 end) Level4
from tblBallot group by OperaterNo
)b on a.OperaterNo=b.OperaterNo

[CrazyFor]

select a.OperaterName,(level1+level2+level3+level4) as Total
,cast((level*100)/(level1+level2+level3+level4) as varchar)+'%' as Level1
,cast((leve2*100)/(level1+level2+level3+level4) as varchar)+'%' as Level2
,cast((leve3*100)/(level1+level2+level3+level4) as varchar)+'%' as Level3
,cast((leve4*100)/(level1+level2+level3+level4) as varchar)+'%' as Level4
from tblOperater a left join
(
select OperaterNo
,sum(case when BallotLevel=1 then 1 else 0 end) Level1
,sum(case when BallotLevel=2 then 1 else 0 end) Level2
,sum(case when BallotLevel=3 then 1 else 0 end) Level3
,sum(case when BallotLevel=4 then 1 else 0 end) Level4
from tblBallot group by OperaterNo
)b on a.OperaterNo=b.OperaterNo

[txlicenhe]

select a.OperaterName,(level1+level2+level3+level4) as Total
,cast((level*100)/(level1+level2+level3+level4) as varchar)+'%' as Level1
,cast((leve2*100)/(level1+level2+level3+level4) as varchar)+'%' as Level2
,cast((leve3*100)/(level1+level2+level3+level4) as varchar)+'%' as Level3
,cast((leve4*100)/(level1+level2+level3+level4) as varchar)+'%' as Level4
from tblOperater a left join
(
select OperaterNo
,sum(case when BallotLevel=1 then 1 else 0 end) Level1
,sum(case when BallotLevel=2 then 1 else 0 end) Level2
,sum(case when BallotLevel=3 then 1 else 0 end) Level3
,sum(case when BallotLevel=4 then 1 else 0 end) Level4
from tblBallot group by OperaterNo
)b on a.OperaterNo=b.OperaterNo

[wj7907]

老大们，怎么都提示在sum(...)中出错啊？？

[kimcz]

select OperaterName as name , count(*) as Total,
sum(case when b.BallotLevel=1 then 1 else 0 end) / count(*) as Level1,
sum(case when b.BallotLevel=2 then 1 else 0 end) / count(*) as Level2,
sum(case when b.BallotLevel=3 then 1 else 0 end) / count(*) as Level3,
sum(case when b.BallotLevel=4 then 1 else 0 end) / count(*) as Level4
from tblOperater a, tblBallot b
where a.OperaterNo *= b.OperaterNo
group by a.OperaterNo,b.OperaterName

[wj7907]

感谢大家，人人有分

[pengdali]

select tblOperater.OperaterNo,count(*) as Total,

sum(Level1) as Level1,
sum(Level2) as Level2,
sum(Level3) as Level3,
sum(Level4) as Level4

from tblOperater left join

(select OperaterNo,
iif(BallotLevel=1,1,0) as Level1,
iif(BallotLevel=2,1,0) as Level2,
iif(BallotLevel=3,1,0) as Level3,
iif(BallotLevel=4,1,0) as Level4
from tblBallot) as tblBallot on tblOperater.OperaterNo=tblBallot.OperaterNo

group by tblOperater.OperaterNo,tblOperater.OperaterName

[playyuer]

http://www.csdn.net/Develop/Read_Article.asp?Id=15989
一道褒贬不一的 SQL 考试题 playyuer（原作）

6.按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
课程ID,课程名称,平均成绩,及格百分数

SELECT 课程ID,MAX(课程名称) AS 课程名称,AVG(成绩) AS 平均成绩,100 * SUM(CASE WHEN 成绩 >=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM T
GROUP BY 课程ID
ORDER BY 及格百分数 DESC

7.列印四门课程平均成绩和及格率的百分数(用"1行4列"表示): (就是分析哪门课程难)
数学平均分,数学及格百分数,语文平均分,语文及格百分数,英语平均分,英语及格百分数,政治平均分,政治及格百分数

SELECT SUM(CASE WHEN 课程ID = 'K1' THEN 成绩 ELSE 0 END)/ (SELECT COUNT(*) FROM T WHERE 课程ID = 'K1') AS 数学平均分
,100 * SUM(CASE WHEN 课程ID = 'K1' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = 'K1' THEN 1 ELSE 0 END) AS 数学及格百分数
,SUM(CASE WHEN 课程ID = 'K2' THEN 成绩 ELSE 0 END)/ (SELECT COUNT(*) FROM T WHERE 课程ID = 'K2') AS 语文平均分
,100 * SUM(CASE WHEN 课程ID = 'K2' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = 'K2' THEN 1 ELSE 0 END) AS 语文及格百分数
,SUM(CASE WHEN 课程ID = 'K3' THEN 成绩 ELSE 0 END)/ (SELECT COUNT(*) FROM T WHERE 课程ID = 'K3') AS 英语平均分
,100 * SUM(CASE WHEN 课程ID = 'K3' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = 'K3' THEN 1 ELSE 0 END) AS 英语及格百分数
,SUM(CASE WHEN 课程ID = 'K4' THEN 成绩 ELSE 0 END)/ (SELECT COUNT(*) FROM T WHERE 课程ID = 'K4') AS 政治平均分
,100 * SUM(CASE WHEN 课程ID = 'K4' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = 'K4' THEN 1 ELSE 0 END) AS 政治及格百分数
FROM T