请教一个 查询语句

Question

数据表年级 班级 姓名 类型
1    2    a    老师
1    2    b    男同学
1    3    c    女同学
1    2    d    男同学
2    2    e    男同学
1    3    f    男同学
1    2    g    男同学结果年级 班级  总人数 老师数 男同学数 女同学数
1    2      3        1       1       1

Answer 1

  select 年级,班级, count(姓名) 总人数,
         sum(decode(类型),'老师',1,0) 老师数 ,
         sum(decode(类型),'男同学',1,0) 男同学数, 
         sum(decode(类型),'女同学',1,0) 女同学数
   from 数据表
   group by 年级,班级
随手写得，没测过，试试看

Answer 2

with temp as(
select 1 年级, 2 班级,'a' 姓名,'老师' 类型 from dual
union all
select 1 年级, 2 班级,'b' 姓名,'男同学' 类型 from dual
union all
select 1 年级, 3 班级,'c' 姓名,'女同学' 类型 from dual
union all
select 1 年级, 2 班级,'d' 姓名,'男同学' 类型 from dual
union all
select 2 年级, 2 班级,'e' 姓名,'男同学' 类型 from dual
union all
select 1 年级, 3 班级,'f' 姓名,'男同学' 类型 from dual
union all
select 1 年级, 2 班级,'g' 姓名,'男同学' 类型 from dual
)select 年级,班级,sum(总人数),sum(nvl(男同学数,0)) 男同学数,sum(nvl(女同学数,0)) 女同学数 from( 
select 年级,班级,count(班级) 总人数,case when 类型='男同学' then count(类型) end 男同学数,
case when 类型='女同学' then count(类型) end 女同学数
 from temp where 班级 = 2 and 年级 = 1 group by 年级,班级,类型
 ) group by 年级,班级

Answer 3

   select 年级,班级, count(姓名) 总人数,
         sum(decode(类型,'老师',1,0)) 老师数 ,
         sum(decode(类型,'男同学',1,0)) 男同学数, 
         sum(decode(类型,'女同学',1,0)) 女同学数
   from 数据表
   group by 年级,班级括号套错了

Answer 4

SELECT NIANJI,
       BANJI,
       SUM(ZONGRENSHU),
       SUM(LAOSHI),
       SUM(NANTONGXUE),
       SUM(NVTONGXUE)
  FROM (select T.NIANJI NIANJI,
               T.BANJI BANJI,
               COUNT(*) ZONGRENSHU,
               CASE
                 WHEN T.LEIXING = '老师' THEN
                  COUNT(1)
                 ELSE
                  0
               END LAOSHI,
               CASE
                 WHEN T.LEIXING = '男同学' THEN
                  COUNT(1)
                 ELSE
                  0
               END NANTONGXUE,
               CASE
                 WHEN T.LEIXING = '女同学' THEN
                  COUNT(1)
                 ELSE
                  0
               END NVTONGXUE
          from test_2 t
         GROUP BY T.NIANJI, T.BANJI, T.LEIXING) TEMP
 GROUP BY NIANJI, BANJI

Answer 5

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as csdn
SQL> select * from test001;GRADE        CLASST                                                                           NAME                                                                             TYPET
------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1            2                                                                                 a                                                                               老师
1            2                                                                                b                                                                                男同学
1            3                                                                                c                                                                                女同学
1            2                                                                                d                                                                                男同学
2            2                                                                                e                                                                                男同学
1            3                                                                                f                                                                                男同学
1            2                                                                                g                                                                                男同学7 rows selectedSQL> 
SQL> select t.grade,
  2         t.classt,
  3         count(*) totalCount,
  4         sum(decode(t.typet, '老师', 1, 0)) teacher,
  5         sum(decode(t.typet, '男同学', 1, 0)) boy,
  6         sum(decode(t.typet, '女同学', 1, 0)) girl
  7    from test001 t
  8   group by t.grade, t.classt
  9  ;GRADE        CLASST                                                                           TOTALCOUNT    TEACHER        BOY       GIRL
------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
2            2                                                                                         1          0          1          0
1            2                                                                                         4          1          3          0
1            3                                                                                         2          0          1          1SQL>

Answer 6

select 年级,班级,sum(总人数),sum(nvl(男同学数,0)) 男同学数,sum(nvl(女同学数,0)) 女同学数 from( 
select 年级,班级,count(班级) 总人数,case when 类型='男同学' then count(类型) end 男同学数,
case when 类型='女同学' then count(类型) end 女同学数
 from temp group by 年级,班级,类型
 ) group by 年级,班级

Answer 7

select 年级,班级,
count(*) as 总人数,
sum(case 类型 when '老师' then 1 end) as 老师数,
sum(case 类型 when '男同学' then 1 end) as 男同学数,
sum(case 类型 when '女同学' then 1 end) as 女同学数
from 数据表
group by 年级,班级

Answer 8

--建表
create table myclass(grade number(2),classes number(2),name varchar2(20),type varchar2(20));--插入数据                                                                                      
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 2, 'a', '老师');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 2, 'b', '男同学');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 3, 'c', '女同学');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 2, 'd', '男同学');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (2, 2, 'e', '男同学');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 3, 'f', '男同学');
insert into MYCLASS (GRADE, CLASSES, NAME, TYPE)
values (1, 2, 'g', '男同学');
commit;  
                                                                                          
--取得结果                                                                                   
SELECT grade "年级",
       classes "班级",
       t_num + m_num + f_num "总人数",
       t_num "老师数",
       m_num "男同学数",
       f_num "女同学数"
  FROM (SELECT grade,
               classes,
               SUM(decode(TYPE, '老师', num, 0)) t_num,
               SUM(decode(TYPE, '男同学', num, 0)) m_num,
               SUM(decode(TYPE, '女同学', num, 0)) f_num
          FROM (SELECT t.grade, t.classes, t.type, COUNT(*) AS num
                  FROM myclass t
                 GROUP BY t.grade, t.classes, t.type)
         GROUP BY grade, classes);

Answer 9

todayandtomorrow的可以了，
我想问一下，我的类型不是一定等于老师的，而是like,
例如：男老师也归入老师那一类。
解决了立即结贴

Answer 10

select t.grade,
       t.classt,
       count(*) totalCount,
       sum(decode(t.typet, '老师', 1, '男同学', 1, 0)) teacher,
       sum(decode(t.typet, '女同学', 1, 0)) girl
  from test001 t
 group by t.grade, t.classt

Answer 11

--包含老师即可
SELECT grade "年级",
       classes "班级",
       total "总人数",
       t_num "老师数",
       m_num "男同学数",
       f_num "女同学数"
  FROM (SELECT grade,
               classes,
               count(1) total,
               SUM(case when instr(TYPE,'老师')>0 then 1 else 0 end) t_num,
               SUM(decode(TYPE, '男同学', 1, 0)) m_num,
               SUM(decode(TYPE, '女同学', 1, 0)) f_num
          FROM myclass
         GROUP BY grade, classes);