【SQL】求统计查询的SQL语句
有学生评分表，字段有：ID,班级,学生,学科,教师,A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4,D1,D2,D3,D4
由不同学科的教师对 A1-A4，B1-B4，C1-C4，D1-D4 作评分，分值为 1-4分，代表四个等级。
现要统计出每个学生在 A1-A4，B1-B4，C1-C4，D1-D4 各项中每个等级的个数，即得 1 分有几个，得 2 分的有几个，得 3 分的有几个，得 4 分的有几个。
求 SQL 语句，定重谢。
===== 4 月 9 日下午 4 点补充说明 =====
该评分表实际上为学生综合素质评分表。学生的综合素质评价分为 A，B，C，D 四个大类，下面分别有四个小类，即，A 类有 A1-A4，B 类有 B1-B4，C 类有 C1-C4，D 类有 D1-D4。教师要分给给这个十六个小类分别评分，分值为 1-4 分，也可以留空不评。如图：
现在是想得到每个学生在这十六个小类中得 1 分的几个，得 2 分的几个，得 3 分的几个，得 4 分的几个。如图：
A1~A4每个都要评分，还是说A1就是1分，A2就是2分；A,B,C,D代表4个科目？每个科目教师为一个元组？一个学生需要4个元组。
SELECT * FROM (SELECT ID,A1,A2,A3,A4 FROM 评分表 GROUP BY ID,A1,A2,A3,A4 ) A UNION
SELECT * FROM (SELECT ID,B1,B2,B3,B4 FROM 评分表 GROUP BY ID,B1,B2,B3,B4 ) B UNION
SELECT * FROM (SELECT ID,C1,C2,C3,C4 FROM 评分表 GROUP BY ID,C1,C2,C3,C4 ) C UNION
SELECT * FROM (SELECT ID,D1,D2,D3,D4 FROM 评分表 GROUP BY ID,D1,D2,D3,D4 ) D
生成四条记录，每条记录是一项评分：比如，思品，科学，体育，美术。
如果统计 A1，则 select A1,count(id) from 学生评分表 group by A1;
其他以此类推。
select sid,(count(A1) + count(A2) + count(A3) + count(A4)) as a , (count(B1) + count(B2) + count(B3) + count(B4)) as b,(count(C1) +count(C2) + count(C3) + count(C4)) as c , (count(D1) + count(D2) + count(D3) + count(D4)) as d from table
这个表设计的真坑爹啊，业务主键应该是学生+学科+老师吧？（正常情况下一个学科只能一个老师，不过我读书的时候有其他老师代课的情况，得看需求是否到老师的维度，如果不到只关心学生+学科即可）
SQL如下：
create table testxzq (bnid varchar2(50),
classnum int,studentid varchar2(50),sourceid varchar2(50),teacherid varchar2(50),
A1 number,A2 number ,B1 number,B2 number,c1 number ,c2 number
);
select t.studentid,t.sourceid,
sum(nvl(t.a1,0)+nvl(t.a2,0)) A,sum(nvl(t.b1,0) + nvl(t.b2,0)) B,sum(nvl(t.c1,0)+nvl(t.c2,0)) C
from testxzq t
group by t.studentid,t.sourceid;
问题简化：您陈述的这个问题应该细化一下，比如：一个教师（确定下来）对应全部学生。（这个是前提）。统计每个学生在A1-A4，B1-B4，C1-C4，D1-D4 各项中得 1 分有几个，得 2 分的有几个，得 3 分的有几个，得 4 分的有几个。
然后设计一个针对这个问题的表，再做统计就简单了。
具体做法如下：
第一步：比如一个数学教师，对每个学生评分的表。统计每个学生在A1-A4，B1-B4，C1-C4，D1-D4 各项中得 1 分有几个，得 2 分的有几个，得 3 分的有几个，得 4 分的有几个。
第二步：用MySQL查询语句查询A1=1的有几人：SELECT SUM(A1) AS A1_total1 FROM 表 WHERE A1=1;
用MySQL查询语句查询A1=2的有几人：SELECT SUM(A1) AS A1_total2 FROM 表 WHERE A1=2;
用MySQL查询语句查询A1=3的有几人：SELECT SUM(A1) AS A1_total3 FROM 表 WHERE A1=3;
......
用MySQL查询语句查询D1=4的有几人：SELECT SUM(D4) AS D4_total4 FROM 表 WHERE D4=4;
select d.sourceid,
sum(case when d.a = 1 then 1 else 0 end ) A1,
sum(case d.a when 2 then 1 else 0 end ) A2,
sum(case d.a when 3 then 1 else 0 end ) A3,
sum(case d.a when 4 then 1 else 0 end ) A4,
sum(case d.b when 1 then 1 else 0 end ) B1,
sum(case d.b when 2 then 1 else 0 end ) B2,
sum(case d.b when 3 then 1 else 0 end ) B3,
sum(case d.b when 4 then 1 else 0 end ) B4,
sum(case d.c when 1 then 1 else 0 end ) C1,
sum(case d.c when 2 then 1 else 0 end ) C2,
sum(case d.c when 3 then 1 else 0 end ) C3,
sum(case d.c when 4 then 1 else 0 end ) C4
from (
select t.studentid,t.sourceid,
sum(nvl(t.a1,0)+nvl(t.a2,0)) a,sum(nvl(t.b1,0) + nvl(t.b2,0)) b,sum(nvl(t.c1,0)+nvl(t.c2,0)) c
from testxzq t
group by t.studentid,t.sourceid
) d group by d.sourceid
;
if (object_id('tgr_update', 'TR') is not null)
drop trigger tgr_update
go
create trigger tgr_clasupdate' B
for update
as
declare @A1 int, @newB1 int;
--更新前的数据
select @oldnewB1=b1,@A1=a1 From inserted
if (@olnewB1==1)
begin
--更新后的数据
update A set A2= 1 where A1 = @A1
end
go
需求没描述清楚，可以使用case when条件语句和group by结合筛选查询
--表名： b_test,假设A1-D4字段类型是字符如果是数字则先转字符,并且不会为空如果为空则去空为0
--SQL思路就是先将每条评分记录连成字符串，再从中取各评分字符的出现次数，最后将不同教师不同评分个数合计
select 班级,学生,SUM(LENGTHB(TRANSLATE(合计,'1'||合计,'1'))) 1分个数,
SUM(LENGTHB(TRANSLATE(合计,'2'||合计,'2'))) 2分个数,
SUM(LENGTHB(TRANSLATE(合计,'3'||合计,'3'))) 3分个数,
SUM(LENGTHB(TRANSLATE(合计,'4'||合计,'4'))) 4分个数
from (select 班级,学生,学科,教师,A1||A2||A3||A4||B1||B2||B3||B4||C1||C2||C3||C4||D1||D2||D3||D4 合计 from b_test )
group by 班级,学生
SELECT * FROM (SELECT ID,A1,A2,A3,A4 FROM 评分表 GROUP BY ID,A1,A2,A3,A4 ) A UNION
SELECT * FROM (SELECT ID,B1,B2,B3,B4 FROM 评分表 GROUP BY ID,B1,B2,B3,B4 ) B UNION
SELECT * FROM (SELECT ID,C1,C2,C3,C4 FROM 评分表 GROUP BY ID,C1,C2,C3,C4 ) C UNION
SELECT * FROM (SELECT ID,D1,D2,D3,D4 FROM 评分表 GROUP BY ID,D1,D2,D3,D4 ) D
试试
有学生评分表，字段有：ID,班级,学生,学科,教师,A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4,D1,D2,D3,D4
由不同学科的教师对 A1-A4，B1-B4，C1-C4，D1-D4 作评分，分值为 1-4
现要统计出每个学生在 A1-A4，B1-B4，C1-C4，D1-D4 各项中得 1 分有几个，得 2 分的有几个，得 3 分的有几个，得 4 分的有几个。
求 SQL 语句，定重谢。
1.不同数据库的sql语句不一样
2.一个老师只能对A1-A4，B1-B4，C1-C4，D1-D4的一个进行评分吧,不过即使一个老师对两个评分下面的SQL依旧可用
ORACLE SQL语句
--制造数据
set lines 132
col a1 for 99
col a2 for 99
col a3 for 99
col a4 for 99
col b1 for 99
col b2 for 99
col b3 for 99
col b4 for 99
col c1 for 99
col c2 for 99
col c3 for 99
col c4 for 99
col d1 for 99
col d2 for 99
col d3 for 99
col d4 for 99
with temp as(
select 1001 id,'1-1' class,'zhangsan' sxm,'language' subject,'teacher1' txm,1 a1,1 a2,2 a3,3 a4,0 b1,0 b2,0 b3,0 b4,0 c1,0 c2,0 c3,0 c4,0 d1,0 d2,0 d3,0 d4 from dual
union all
select 1001,'1-1','zhangsan','math','teacher2',0,0,0,0,2,2,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','english','teacher3',0,0,0,0,0,0,0,0,1,2,3,4,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,3,3,4,4 from dual
union all
select 1002,'1-2','lisi','language','teacher1',1,2,2,3,0,0,0,0,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','math','teacher2',0,0,0,0,2,3,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','english','teacher3',0,0,0,0,0,0,0,0,1,2,2,4,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,2,3,4,4 from dual
)
select * from temp;
ID CLA SXM SUBJECT TXM A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4
1001 1-1 zhangsan language teacher1 1 1 2 3 0 0 0 0 0 0 0 0 0 0 0 0
1001 1-1 zhangsan math teacher2 0 0 0 0 2 2 3 4 0 0 0 0 0 0 0 0
1001 1-1 zhangsan english teacher3 0 0 0 0 0 0 0 0 1 2 3 4 0 0 0 0
1001 1-1 zhangsan phsical teacher4 0 0 0 0 0 0 0 0 0 0 0 0 3 3 4 4
1002 1-2 lisi language teacher1 1 2 2 3 0 0 0 0 0 0 0 0 0 0 0 0
1002 1-2 lisi math teacher2 0 0 0 0 2 3 3 4 0 0 0 0 0 0 0 0
1002 1-2 lisi english teacher3 0 0 0 0 0 0 0 0 1 2 2 4 0 0 0 0
1002 1-2 lisi phsical teacher4 0 0 0 0 0 0 0 0 0 0 0 0 2 3 4 4
按照上面的数据,其结果应该是
ID CLA SXM A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4
1001 1-1 zhangsan 2 1 1 0 0 2 1 1 1 1 1 1 0 0 2 2
1002 1-2 lisi 1 2 1 0 0 1 2 1 1 2 0 1 0 1 1 2
ORACLE下SQL语句
with temp as(
select 1001 id,'1-1' class,'zhangsan' sxm,'language' subject,'teacher1' txm,1 a1,1 a2,2 a3,3 a4,0 b1,0 b2,0 b3,0 b4,0 c1,0 c2,0 c3,0 c4,0 d1,0 d2,0 d3,0 d4 from dual
union all
select 1001,'1-1','zhangsan','math','teacher2',0,0,0,0,2,2,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','english','teacher3',0,0,0,0,0,0,0,0,1,2,3,4,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,3,3,4,4 from dual
union all
select 1002,'1-2','lisi','language','teacher1',1,2,2,3,0,0,0,0,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','math','teacher2',0,0,0,0,2,3,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','english','teacher3',0,0,0,0,0,0,0,0,1,2,2,4,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,2,3,4,4 from dual
)
select id,class,sxm,
max(decode(a1/1,1,1,0)+decode(a2/1,1,1,0)+decode(a3/1,1,1,0)+decode(a4/1,1,1,0)) a1,
max(decode(a1/2,1,1,0)+decode(a2/2,1,1,0)+decode(a3/2,1,1,0)+decode(a4/2,1,1,0)) a2,
max(decode(a1/3,1,1,0)+decode(a2/3,1,1,0)+decode(a3/3,1,1,0)+decode(a4/3,1,1,0)) a3,
max(decode(a1/4,1,1,0)+decode(a2/4,1,1,0)+decode(a3/4,1,1,0)+decode(a4/4,1,1,0)) a4,
max(decode(b1/1,1,1,0)+decode(b2/1,1,1,0)+decode(b3/1,1,1,0)+decode(b4/1,1,1,0)) b1,
max(decode(b1/2,1,1,0)+decode(b2/2,1,1,0)+decode(b3/2,1,1,0)+decode(b4/2,1,1,0)) b2,
max(decode(b1/3,1,1,0)+decode(b2/3,1,1,0)+decode(b3/3,1,1,0)+decode(b4/3,1,1,0)) b3,
max(decode(b1/4,1,1,0)+decode(b2/4,1,1,0)+decode(b3/4,1,1,0)+decode(b4/4,1,1,0)) b4,
max(decode(c1/1,1,1,0)+decode(c2/1,1,1,0)+decode(c3/1,1,1,0)+decode(c4/1,1,1,0)) c1,
max(decode(c1/2,1,1,0)+decode(c2/2,1,1,0)+decode(c3/2,1,1,0)+decode(c4/2,1,1,0)) c2,
max(decode(c1/3,1,1,0)+decode(c2/3,1,1,0)+decode(c3/3,1,1,0)+decode(c4/3,1,1,0)) c3,
max(decode(c1/4,1,1,0)+decode(c2/4,1,1,0)+decode(c3/4,1,1,0)+decode(c4/4,1,1,0)) c4,
max(decode(d1/1,1,1,0)+decode(d2/1,1,1,0)+decode(d3/1,1,1,0)+decode(d4/1,1,1,0)) d1,
max(decode(d1/2,1,1,0)+decode(d2/2,1,1,0)+decode(d3/2,1,1,0)+decode(d4/2,1,1,0)) d2,
max(decode(d1/3,1,1,0)+decode(d2/3,1,1,0)+decode(d3/3,1,1,0)+decode(d4/3,1,1,0)) d3,
max(decode(d1/4,1,1,0)+decode(d2/4,1,1,0)+decode(d3/4,1,1,0)+decode(d4/4,1,1,0)) d4
from temp
group by id,class,sxm;
结果如下:
ID CLA SXM A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4
1002 1-2 lisi 1 2 1 0 0 1 2 1 1 2 0 1 0 1 1 2
1001 1-1 zhangsan 2 1 1 0 0 2 1 1 1 1 1 1 0 0 2 2
如果一个老师只能对A1-A4，B1-B4，C1-C4，D1-D4的一个进行评分,上面的SQL就够了,但是如果一个老师可以对不同学科评分,则还需要加个外层查询,如下:
with temp as(
select 1001 id,'1-1' class,'zhangsan' sxm,'language' subject,'teacher1' txm,1 a1,1 a2,2 a3,3 a4,0 b1,0 b2,0 b3,0 b4,0 c1,0 c2,0 c3,0 c4,0 d1,0 d2,0 d3,0 d4 from dual
union all
select 1001,'1-1','zhangsan','math','teacher2',0,0,0,0,2,2,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','english','teacher3',0,0,0,0,0,0,0,0,1,2,3,4,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,3,3,4,4 from dual
union all
select 1002,'1-2','lisi','language','teacher1',1,2,2,3,0,0,0,0,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','math','teacher2',0,0,0,0,2,3,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','english','teacher3',0,0,0,0,0,0,0,0,1,2,2,4,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,2,3,4,4 from dual
)
select id,class,sxm,
sum(a1) a1,sum(a2) a2,sum(a3) a3,sum(a4) a4,
sum(b1) b1,sum(b2) b2,sum(b3) b3,sum(b4) b4,
sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,
sum(d1) d1,sum(d2) d2,sum(d3) d3,sum(d4) d4
from
(
select id,class,sxm,
decode(a1/1,1,1,0)+decode(a2/1,1,1,0)+decode(a3/1,1,1,0)+decode(a4/1,1,1,0) a1,
decode(a1/2,1,1,0)+decode(a2/2,1,1,0)+decode(a3/2,1,1,0)+decode(a4/2,1,1,0) a2,
decode(a1/3,1,1,0)+decode(a2/3,1,1,0)+decode(a3/3,1,1,0)+decode(a4/3,1,1,0) a3,
decode(a1/4,1,1,0)+decode(a2/4,1,1,0)+decode(a3/4,1,1,0)+decode(a4/4,1,1,0) a4,
decode(b1/1,1,1,0)+decode(b2/1,1,1,0)+decode(b3/1,1,1,0)+decode(b4/1,1,1,0) b1,
decode(b1/2,1,1,0)+decode(b2/2,1,1,0)+decode(b3/2,1,1,0)+decode(b4/2,1,1,0) b2,
decode(b1/3,1,1,0)+decode(b2/3,1,1,0)+decode(b3/3,1,1,0)+decode(b4/3,1,1,0) b3,
decode(b1/4,1,1,0)+decode(b2/4,1,1,0)+decode(b3/4,1,1,0)+decode(b4/4,1,1,0) b4,
decode(c1/1,1,1,0)+decode(c2/1,1,1,0)+decode(c3/1,1,1,0)+decode(c4/1,1,1,0) c1,
decode(c1/2,1,1,0)+decode(c2/2,1,1,0)+decode(c3/2,1,1,0)+decode(c4/2,1,1,0) c2,
decode(c1/3,1,1,0)+decode(c2/3,1,1,0)+decode(c3/3,1,1,0)+decode(c4/3,1,1,0) c3,
decode(c1/4,1,1,0)+decode(c2/4,1,1,0)+decode(c3/4,1,1,0)+decode(c4/4,1,1,0) c4,
decode(d1/1,1,1,0)+decode(d2/1,1,1,0)+decode(d3/1,1,1,0)+decode(d4/1,1,1,0) d1,
decode(d1/2,1,1,0)+decode(d2/2,1,1,0)+decode(d3/2,1,1,0)+decode(d4/2,1,1,0) d2,
decode(d1/3,1,1,0)+decode(d2/3,1,1,0)+decode(d3/3,1,1,0)+decode(d4/3,1,1,0) d3,
decode(d1/4,1,1,0)+decode(d2/4,1,1,0)+decode(d3/4,1,1,0)+decode(d4/4,1,1,0) d4
from temp
)
group by id,class,sxm;
结果是一样的
ID CLA SXM A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4
1002 1-2 lisi 1 2 1 0 0 1 2 1 1 2 0 1 0 1 1 2
1001 1-1 zhangsan 2 1 1 0 0 2 1 1 1 1 1 1 0 0 2 2
MYSQL SQL语句
--建立测试数据
mysql> create table temptab(
id varchar(10),
class varchar(10),
sxm varchar(10),
subject varchar(10),
txm varchar(10),
a1 tinyint unsigned,
a2 tinyint unsigned,
a3 tinyint unsigned,
a4 tinyint unsigned,
b1 tinyint unsigned,
b2 tinyint unsigned,
b3 tinyint unsigned,
b4 tinyint unsigned,
c1 tinyint unsigned,
c2 tinyint unsigned,
c3 tinyint unsigned,
c4 tinyint unsigned,
d1 tinyint unsigned,
d2 tinyint unsigned,
d3 tinyint unsigned,
d4 tinyint unsigned
);
Query OK, 0 rows affected
mysql> insert into temptab
select 1001 id,'1-1' class,'zhangsan' sxm,'language' subject,'teacher1' txm,1 a1,1 a2,2 a3,3 a4,0 b1,0 b2,0 b3,0 b4,0 c1,0 c2,0 c3,0 c4,0 d1,0 d2,0 d3,0 d4 from dual
union all
select 1001,'1-1','zhangsan','math','teacher2',0,0,0,0,2,2,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','english','teacher3',0,0,0,0,0,0,0,0,1,2,3,4,0,0,0,0 from dual
union all
select 1001,'1-1','zhangsan','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,3,3,4,4 from dual
union all
select 1002,'1-2','lisi','language','teacher1',1,2,2,3,0,0,0,0,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','math','teacher2',0,0,0,0,2,3,3,4,0,0,0,0,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','english','teacher3',0,0,0,0,0,0,0,0,1,2,2,4,0,0,0,0 from dual
union all
select 1002,'1-2','lisi','phsical','teacher4',0,0,0,0,0,0,0,0,0,0,0,0,2,3,4,4 from dual;
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from temptab;
+------+-------+----------+----------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
| id | class | sxm | subject | txm | a1 | a2 | a3 | a4 | b1 | b2 | b3 | b4 | c1 | c2 | c3 | c4 | d1 | d2 | d3 | d4 |
+------+-------+----------+----------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
| 1001 | 1-1 | zhangsan | language | teacher1 | 1 | 1 | 2 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1001 | 1-1 | zhangsan | math | teacher2 | 0 | 0 | 0 | 0 | 2 | 2 | 3 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1001 | 1-1 | zhangsan | english | teacher3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 3 | 4 | 0 | 0 | 0 | 0 |
| 1001 | 1-1 | zhangsan | phsical | teacher4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 3 | 4 | 4 |
| 1002 | 1-2 | lisi | language | teacher1 | 1 | 2 | 2 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1002 | 1-2 | lisi | math | teacher2 | 0 | 0 | 0 | 0 | 2 | 3 | 3 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1002 | 1-2 | lisi | english | teacher3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2 | 4 | 0 | 0 | 0 | 0 |
| 1002 | 1-2 | lisi | phsical | teacher4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 3 | 4 | 4 |
+------+-------+----------+----------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
8 rows in set
SQL结果应该和ORACLE一样
select id,class,sxm,
sum(a1) a1,sum(a2) a2,sum(a3) a3,sum(a4) a4,
sum(b1) b1,sum(b2) b2,sum(b3) b3,sum(b4) b4,
sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,
sum(d1) d1,sum(d2) d2,sum(d3) d3,sum(d4) d4
from
(
select id,class,sxm,
if(a1/1=1,1,0)+if(a2/1=1,1,0)+if(a3/1=1,1,0)+if(a4/1=1,1,0) a1,
if(a1/2=1,1,0)+if(a2/2=1,1,0)+if(a3/2=1,1,0)+if(a4/2=1,1,0) a2,
if(a1/3=1,1,0)+if(a2/3=1,1,0)+if(a3/3=1,1,0)+if(a4/3=1,1,0) a3,
if(a1/4=1,1,0)+if(a2/4=1,1,0)+if(a3/4=1,1,0)+if(a4/4=1,1,0) a4,
if(b1/1=1,1,0)+if(b2/1=1,1,0)+if(b3/1=1,1,0)+if(b4/1=1,1,0) b1,
if(b1/2=1,1,0)+if(b2/2=1,1,0)+if(b3/2=1,1,0)+if(b4/2=1,1,0) b2,
if(b1/3=1,1,0)+if(b2/3=1,1,0)+if(b3/3=1,1,0)+if(b4/3=1,1,0) b3,
if(b1/4=1,1,0)+if(b2/4=1,1,0)+if(b3/4=1,1,0)+if(b4/4=1,1,0) b4,
if(c1/1=1,1,0)+if(c2/1=1,1,0)+if(c3/1=1,1,0)+if(c4/1=1,1,0) c1,
if(c1/2=1,1,0)+if(c2/2=1,1,0)+if(c3/2=1,1,0)+if(c4/2=1,1,0) c2,
if(c1/3=1,1,0)+if(c2/3=1,1,0)+if(c3/3=1,1,0)+if(c4/3=1,1,0) c3,
if(c1/4=1,1,0)+if(c2/4=1,1,0)+if(c3/4=1,1,0)+if(c4/4=1,1,0) c4,
if(d1/1=1,1,0)+if(d2/1=1,1,0)+if(d3/1=1,1,0)+if(d4/1=1,1,0) d1,
if(d1/2=1,1,0)+if(d2/2=1,1,0)+if(d3/2=1,1,0)+if(d4/2=1,1,0) d2,
if(d1/3=1,1,0)+if(d2/3=1,1,0)+if(d3/3=1,1,0)+if(d4/3=1,1,0) d3,
if(d1/4=1,1,0)+if(d2/4=1,1,0)+if(d3/4=1,1,0)+if(d4/4=1,1,0) d4
from temptab
) t
group by id,class,sxm;
+------+-------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
| id | class | sxm | a1 | a2 | a3 | a4 | b1 | b2 | b3 | b4 | c1 | c2 | c3 | c4 | d1 | d2 | d3 | d4 |
+------+-------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
| 1001 | 1-1 | zhangsan | 2 | 1 | 1 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 2 | 2 |
| 1002 | 1-2 | lisi | 1 | 2 | 1 | 0 | 0 | 1 | 2 | 1 | 1 | 2 | 0 | 1 | 0 | 1 | 1 | 2 |
+------+-------+----------+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
SELECT * FROM (SELECT ID,D1,D2,D3,D4 FROM 评分表 GROUP BY ID,D1,D2,D3,D4 ) D
按照要求原始数据放到给出的表中的话，表中学生和老师和评分项存在多对多对多的关系，如果每一行都是一个学生的信息的话每一个学生对应的应该是老师和评分项的统计结果是个二维的结果，所以这是个三维的，不知道你想要怎么放到二维表格中展示。或者是你的题意不太明白，得一分的有几个的“”几个“”是指评分项还是老师
create table testtable_csdn (ID numeric(12,0) identity(1,1) primary key,班级 varchar(10),学生 nvarchar(100) not null,学科 nvarchar(100),教师 nvarchar(100),A1 tinyint,A2 tinyint,A3 tinyint,A4 tinyint,B1 tinyint,B2 tinyint,B3 tinyint,B4 tinyint,C1 tinyint,C2 tinyint,C3 tinyint,C4 tinyint,D1 tinyint,D2 tinyint,D3 tinyint,D4 tinyint)
create table testtable_csdn_scorelist (s char(1) not null) ---存放1,2,3,4
create table testtable_csdn_kindlist (k varchar(10) not null)
insert into testtable_csdn_kindlist select top 16 name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='testtable_csdn') and colorder between 6 and 21
declare @sql varchar(max);
select @sql=isnull(@sql+',','')+' sum(case isnull('+tbk.k+',0) when fn.s then 1 else 0 end) ['+tbk.k+']' from testtable_csdn_kindlist tbk;
exec('select 学生,s,'+@sql+' from (select tbs.s,tbc.* from testtable_csdn tbc,testtable_csdn_scorelist tbs) as fn group by fn.s,fn.学生')
