求SQl,分组求和

Question

学生信息表 
stu_tb:表结构:
id,name(姓名),greadename(学位名),greadeid(学位id) 
其中greade有四种类型,以下为名称及其id号(博士(4),硕士(3),学士(2),大专(1)),选课表:class_tb:表结构:
id(课程id),name(课程名),stuid(学生号),
其中数学和计算机课程号分别为22,33证书登记表:car_tb:表结构:
id(证书id),name(证书名),stuid(学生号),
此处的英语六级id为3
--现在想要按学位等级分类统计本校中选修了高数,计算机,考过英语六级的,分别是多少人,用一句sql怎么统计出来呢 数据如下：stu_tb:
1 7001 Name1 大专 1
2 7002 Name2 学士 2
3 7003 Name3 硕士 3
4 7004 Name4 博士 4
5 7005 Name5 大专 1
6 7006 Name6 学士 2
7 7007 Name7 硕士 3class_tb:
1 33 计算 7001
2 22 数学 7004
3 33 计算 7003
4 44 物理 7007
5 22 数学 7001
6 22 数学 7002
7 33 计算 7005car_tb:
1 1 日3级 7001
2 2 英4级 7002
3 3 英6级 7003
4 1 日3级 7004
5 2 英4级 7005
6 3 英6级 7006
7 1 日3级 7007
8 3 英6级 7001本人sql:
select greadeid, sum(decode(courseid,22,1,0)) 数学,sum(decode(courseid,33,1,0)) 计算机,sum(decode(passid,3,1,0)) 已过英语6级
from (
select s.id stuid,s.name stuname,s.greadeid,c.id courseid,r.id passid
from stu s,class c,car r 
where s.id = c.stuid and s.id = r.stuid 
-- and (c.id = 22 or c.id = 33 and r.id != 3) 
and (c.id = 22 or c.id = 33 or r.id = 3) 
)
group by greadeid但是有重复的数据想实现的结果：
等级 数学(人） 计算机(人)   已过英6(人)
1   1    3        2
3   0    1        1
2   1    0        1
4   1    0        0

Answer 1

try: select sum(高数), sum(计算机), sum(英语六级) from (
select 
       (case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '高数') then 1 else 0 end) 高数, 
       (case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '计算机') then 1 else 0 end) 计算机,
       (case when exists(select 1 from car_tb i where i. stuid = t.id and i.id = '英语六级') then 1 else 0 end) 英语六级
       from stu_tb
) t

Answer 2

create table stu_tb (id number primary key,name varchar2(20),greadename varchar2(10),greadeid number);
insert into stu_tb values (7001, 'Name1', 'dz',1);
insert into stu_tb values (7002, 'Name2', 'xs',2);
insert into stu_tb values (7003, 'Name3', 'ss',3);
insert into stu_tb values (7004, 'Name4', 'bs',4);
insert into stu_tb values (7005, 'Name5', 'dz',1);
insert into stu_tb values (7006, 'Name6', 'xs',2);
insert into stu_tb values (7007, 'Name7', 'ss',3);
commit;create table class_tb (id number, name varchar2(20), stuid number);
insert into class_tb values ( 33, 'js', 7001);
insert into class_tb values ( 22, 'sx', 7004);
insert into class_tb values ( 33, 'js', 7003);
insert into class_tb values ( 44, 'wl', 7007);
insert into class_tb values ( 22, 'sx', 7001);
insert into class_tb values ( 22, 'sx', 7002);
insert into class_tb values ( 33, 'js', 7005);
commit;create table car_tb (id number, name varchar2(20), stuid number);
insert into car_tb values ( 1,'r3', 7001);
insert into car_tb values ( 2,'y4', 7002);
insert into car_tb values ( 3,'y6', 7003);
insert into car_tb values ( 1,'r3', 7004);
insert into car_tb values ( 2,'y4', 7005);
insert into car_tb values ( 3,'y6', 7006);
insert into car_tb values ( 1,'r3', 7007);
insert into car_tb values ( 3,'y6', 7001);
commit;select st.greadeid,
       sum(decode(CS.ID, 22, 1, 0)) "t_sx",
       sum(decode(CS.ID, 33, 1, 0)) "t_js",
       sum(decode(Ca.ID, 3, 1, 0)) "t_y6"
from stu_tb st, class_tb cs, car_tb ca
where ST.ID = CS.STUID
and   ST.ID = ca.stuid
group by ST.greadeid
order by st.greadeid;

Answer 3

学生信息中同时包含了学位id和学位名？另外两张表也是..这些表的设计很不合理啊
学位字段名为什么叫greade..好别扭啊，呵呵
看你写的sql应该差不多，重复指的是什么？

Answer 4

做了一半实在是不想做了，建议楼主下次直接把表结构和测试sql 发上来
这样其他人做起来也比较简单一点

Answer 5

哦，不好意思按学位分级看落了：
select t.greadeid, sum(高数), sum(计算机), sum(英语六级) from (
select t.greadeid, 
       (case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '高数') then 1 else 0 end) 高数, 
       (case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '计算机') then 1 else 0 end) 计算机,
       (case when exists(select 1 from car_tb i where i. stuid = t.id and i.id = '英语六级') then 1 else 0 end) 英语六级
       from stu_tb t
) t group by t.greadeid

Answer 6

select t.greadeid, 
       sum((case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '高数') then 1 else 0 end)) 高数, 
       sum((case when exists(select 1 from class_tb i where i. stuid = t.id and i.id = '计算机') then 1 else 0 end)) 计算机,
       sum((case when exists(select 1 from car_tb i where i. stuid = t.id and i.id = '英语六级') then 1 else 0 end)) 英语六级
       from stu_tb t group by t.greadeid

Answer 7

select greadeid,sum(decode(nvl(cour_id,0),33,1) 数学(人）,
sum(decode(nvl(cour_id,0),22,1)) 计算机(人),
sum(decode(nvl(cerid,0),3,1) 已过英6(人) from (
select c.*,d.id as cer_id from 
(select a.*,b.id as cour_id from stu_tb a left join class_tb b where a.id=b.stuid and b.id in (22,33)) c left join car_tb where c.id=d.stuid and d.id=3) group by greadeid;

Answer 8

跟新一下上面的错误：
select greadeid,
       nvl(sum(decode(nvl(cour_id, 0), 22, 1)),0) 数学,
       nvl(sum(decode(nvl(cour_id, 0), 33, 1)),0) 计算机,
       nvl(sum(decode(nvl(cer_id, 0), 3, 1)),0) 已过英6
  from (select c.*, d.id as cer_id
          from (select a.*, b.id as cour_id
                  from stu_tb a
                  left join class_tb b
                 on a.id = b.stuid
                   and  (b.id =22 or  b.id= 33)) c
          left join car_tb d
         on c.id = d.stuid
           and  d.id = 3) 
 group by greadeid;

Answer 9

create table stu_tb (id number primary key,name varchar2(20),greadename varchar2(10),greadeid number);
insert into stu_tb values (7001, 'Name1', 'dz',1);
insert into stu_tb values (7002, 'Name2', 'xs',2);
insert into stu_tb values (7003, 'Name3', 'ss',3);
insert into stu_tb values (7004, 'Name4', 'bs',4);
insert into stu_tb values (7005, 'Name5', 'dz',1);
insert into stu_tb values (7006, 'Name6', 'xs',2);
insert into stu_tb values (7007, 'Name7', 'ss',3);
commit;create table class_tb (id number, name varchar2(20), stuid number);
insert into class_tb values ( 33, 'js', 7001);
insert into class_tb values ( 22, 'sx', 7004);
insert into class_tb values ( 33, 'js', 7003);
insert into class_tb values ( 44, 'wl', 7007);
insert into class_tb values ( 22, 'sx', 7001);
insert into class_tb values ( 22, 'sx', 7002);
insert into class_tb values ( 33, 'js', 7005);
commit;create table car_tb (id number, name varchar2(20), stuid number);
insert into car_tb values ( 1,'r3', 7001);
insert into car_tb values ( 2,'y4', 7002);
insert into car_tb values ( 3,'y6', 7003);
insert into car_tb values ( 1,'r3', 7004);
insert into car_tb values ( 2,'y4', 7005);
insert into car_tb values ( 3,'y6', 7006);
insert into car_tb values ( 1,'r3', 7007);
insert into car_tb values ( 3,'y6', 7001);
commit;SELECT SUM(GS) 高数,
       SUM(JSJ) 计算机,
       SUM(Y6) 英语6级,
       greadeid
  FROM (
        
        SELECT CASE
                  WHEN ID1 = '22' THEN
                   1
                  ELSE
                   0
                END GS,
                CASE
                  WHEN ID1 = '33' THEN
                   1
                  ELSE
                   0
                END JSJ,
                CASE
                  WHEN ID1 = '3' THEN
                   1
                  ELSE
                   0
                END Y6,
                GREADEID
          FROM (
                 
                 SELECT ST.ID,
                         ST.GREADEID,
                         CL.ID ID1
                   FROM STU_TB   ST,
                         class_tb CL
                  WHERE ST.ID = CL.STUID
                 UNION ALL
                 SELECT ST.ID,
                        ST.GREADEID,
                        CA.ID ID1
                   FROM STU_TB ST,
                        car_tb CA
                  WHERE ST.ID = CA.STUID
                    AND CA.ID = '3')) GROUP BY greadeid

Answer 10

select greadeid,sum(sx),sum(js),sum(y6)
from
(
select
    st.id,
    st.greadeid,
    (select count(id) from class_tb cs where Cs.STUID = st.id and cs.id = 22) as sx,
    (select count(id) from class_tb cs where Cs.STUID = st.id and cs.id = 33) as js,
    (select count(id) from car_tb cr where Cr.STUID = st.id and cr.id = 3) as y6    
from stu_tb st
) temp
group by greadeid
order by greadeid;

Answer 11

用下面的子查询可以得到你要的结果，虽然这样用并不好。更正一下，大专学计算机的有2个人才对。select t.greadeid,
       (select count(*)
          from class_tb
         where class_tb.id = 22
           and class_tb.stuid in
               (select stuid from stu_tb where stu_tb.greadeid = t.greadeid)) 数学,
       (select count(*)
          from class_tb
         where class_tb.id = 33
           and class_tb.stuid in
               (select stuid from stu_tb where stu_tb.greadeid = t.greadeid)) 计算机,
       (select count(*)
          from car_tb
         where car_tb.id = 3
           and car_tb.stuid in
               (select stuid from stu_tb where stu_tb.greadeid = t.greadeid)) 英语
  from (select distinct greadeid from stu_tb) t; GREADEID       数学     计算机       英语
--------- ---------- ---------- ----------
        1          1          2          1
        2          1          0          1
        3          0          1          1
        4          1          0          0

Answer 12

create table stu_tb (id number primary key,name varchar2(20),greadename varchar2(10),greadeid number);
insert into stu_tb values (7001, 'Name1', '大专',1);
insert into stu_tb values (7002, 'Name2', '学士',2);
insert into stu_tb values (7003, 'Name3', '硕士',3);
insert into stu_tb values (7004, 'Name4', '博士',4);
insert into stu_tb values (7005, 'Name5', '大专',1);
insert into stu_tb values (7006, 'Name6', '学士',2);
insert into stu_tb values (7007, 'Name7', '硕士',3);
commit;create table class_tb (id number, name varchar2(20), stuid number);
insert into class_tb values ( 33, '计算', 7001);
insert into class_tb values ( 22, '数学', 7004);
insert into class_tb values ( 33, '计算', 7003);
insert into class_tb values ( 44, '物理', 7007);
insert into class_tb values ( 22, '数学', 7001);
insert into class_tb values ( 22, '数学', 7002);
insert into class_tb values ( 33, '计算', 7005);
commit;create table car_tb (id number, name varchar2(20), stuid number);
insert into car_tb values ( 1,'日3级', 7001);
insert into car_tb values ( 2,'英4级', 7002);
insert into car_tb values ( 3,'英6级', 7003);
insert into car_tb values ( 1,'日3级', 7004);
insert into car_tb values ( 2,'英4级', 7005);
insert into car_tb values ( 3,'英6级', 7006);
insert into car_tb values ( 1,'日3级', 7007);
insert into car_tb values ( 3,'英6级', 7001);
commit;SELECT
a.greadeid,
    SUM(decode(b.id,22,1,0)) AS 数学,
    SUM(decode(b.id,33,1,0)) AS 计算,
    SUM(decode(c.id,3,1,0)) AS 英6级
FROM stu_tb a,class_tb b,car_tb c
WHERE a.id = b.stuid AND a.id = c.stuid
GROUP BY a.greadeid--结果
1 2 3 2
2 1 0 0
3 0 1 1
4 1 0 0DROP TABLE stu_tb;
DROP TABLE class_tb;
DROP TABLE car_tb;

Answer 13

select greadeid,
       greadename,
       count(case
               when exists (select 1
                       from class_tb
                      where stuid = t.id
                        and id = '22') then
                1
             end) "数学",
       count(case
               when exists (select 1
                       from class_tb
                      where stuid = t.id
                        and id = '33') then
                1
             end) "计算",
       count(case
               when exists (select 1
                       from car_tb
                      where stuid = t.id
                        and id = '3') then
                1
             end) "英语6级"
  from stu_tb t
 group by greadeid, greadename
如果6楼有错的话就是字段对应错了，你自己可以发现并修改的