又一个SQL题目, 谢谢各位的帮忙!

Question

注意:要求必须要用子查询,而且不允许用group by和having来解答SQL> select * from my_emp;
    USERID USERNAME     GENDER          DEPT_ID
---------- ------------ ------------ ----------
         1 adam         male                  1
         2 hellen       female                2
         3 larry        male                  1
         4 kim          male                  1
         5 bash         male                  2
         6 tom          female                1SQL> select * from my_dept;
   DEPT_ID DEPT_NAME                                          
---------- ------------
         1 it
         2 sales
结果要变成这样:
dept_name        male      female
------------ ---------- ----------
it                    3          1
sales                 1          1 

Answer 1

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female')
from  my_dept m

Answer 2

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') male, 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female') female
from  my_dept m

Answer 3

子查询不是最优解select d.DEPT_NAME,
    (select count(*) from my_emp where DEPT_ID =d.DEPT_ID and GENDER ='male') as male,
    (select count(*) from my_emp where DEPT_ID =d.DEPT_ID and GENDER ='female') as female,
from my_dept d

Answer 4

应该：select d.DEPT_NAME,
    t.male,
    t.female
from my_dept d,(
  select DEPT_ID,sum(case when GENDER ='male' then 1 else 0 end) as male,
       sum(case when GENDER ='female' then 1 else 0 end) as female
  from my_emp
  group by DEPT_ID
  ) as t
where d.DEPT_ID=t.DEPT_IDps:我上面的多了个,

Answer 5

select d.DEPT_NAME,
    sum(case when GENDER ='male' then 1 end) as male,
    sum(case when GENDER ='female' then 1 end) as female
from my_dept d
left join my_emp e
on e.DEPT_ID =d.DEPT_ID
group by d.DEPT_NAME

Answer 6

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female')
from  my_dept m

Answer 7

create table t1 (userid int,username varchar(8),genger varchar(8),dept_id int)
insert into t1 select 1,'adam','male',1
     union all  select 2,'hellen','female',2
     union all  select 3,'larry','male',1
     union all  select 4,'kim','male',1
     union all  select 5,'bash','male',2
     union all  select 6,'tom','female',1
--select * from @t1
create table t2 (dept_id int,dept_name varchar(10))
insert into t2 select 1,'it'
     union all  select 2,'sales'
;with cte as
(
select b.dept_name,a.genger,count(*) 次数 from t1 a join t2 b on a.dept_id=b.dept_id
   group by dept_name,genger
)
select dept_name,male= sum(case when genger='male' then 次数 else ''end),
                 female=sum(case when genger='female' then 次数 else '' end)
        from cte
        group by dept_name
---------------------
drop table t1
drop table t2
it 3 1
sales 1 1

Answer 8

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') male, 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female') female
from  my_dept m

Answer 9

if object_id('t1')is not null
   drop table t1
if object_id('t2')is not null
   drop table t2
create table t1 (userid int,username varchar(8),genger varchar(8),dept_id int)
insert into t1 select 1,'adam','male',1
     union all  select 2,'hellen','female',2
     union all  select 3,'larry','male',1
     union all  select 4,'kim','male',1
     union all  select 5,'bash','male',2
     union all  select 6,'tom','female',1
--select * from @t1
create table t2 (dept_id int,dept_name varchar(10))
insert into t2 select 1,'it'
     union all  select 2,'sales'
;with cte as
(
select b.dept_name,a.genger,count(*) 次数 from t1 a join t2 b on a.dept_id=b.dept_id
   group by dept_name,genger
)
select * from (select * from cte) as a pivot (sum(次数) for a.genger in (male,female)) as c

Answer 10

create  table table_a(USERID int,USERNAME varchar(6),GENDER varchar(6),DEPT_ID int)
insert into table_a
select 1,'adam','male',1 union all
select 2,'hellen','female',2 union all
select 3,'larry','male',1 union all
select 4,'kim','male',1 union all
select 5,'bash','male',2 union all
select 6,'tom','female',1
select * from table_acreate table table_b(DEPT_ID int,DEPT_NAME varchar(5))
insert into table_b
select 1,'it' union all
select 2,'sales'
select * from table_bselect bb.DEPT_NAME,
    (select count(*) from table_a where DEPT_ID =bb.DEPT_ID and GENDER ='male') as male,
    (select count(*) from table_a where DEPT_ID =bb.DEPT_ID and GENDER ='female') as female
from table_b bb/*
DEPT_NAME male        female
--------- ----------- -----------
it        3           1
sales     1           1(2 row(s) affected)
*/

Answer 11

select m.DEPT_NAME , 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'male') male, 
       (select count(1) from my_emp where DEPT_ID = m.DEPT_ID and GENDER = 'female') female
from  my_dept m