table students结构如下:
sex qty
男 20
男 20
女 20
男 20
男 20要用一个select语句(不要有子查询),不能用goup by
得到总人数(40)说明:students只有2种数据:男 20;女 20,再没有其他数据,只是有重复
sex qty
男 20
男 20
女 20
男 20
男 20要用一个select语句(不要有子查询),不能用goup by
得到总人数(40)说明:students只有2种数据:男 20;女 20,再没有其他数据,只是有重复
--這個應不算子查詢吧
select
((select qty from students where sex='男' and rownum=1)+
(select qty from students where sex='女' and rownum=1))
sumqty
from dual;
with students as
(
select '男' sex ,20 qty from dual
union all
select '女' sex ,40 qty from dual
union all
select '女' sex ,30 qty from dual
union all
select '男' sex ,50 qty from dual
)
select distinct
sum(case when sex='男' then 1 else 0 end ) over()+
sum(case when sex='女' then 1 else 0 end ) over()
from students;
这样行吗?
with students as
(
select '男' sex ,20 qty from dual
union all
select '女' sex ,40 qty from dual
union all
select '女' sex ,30 qty from dual
union all
select '男' sex ,50 qty from dual
)
select distinct count(* ) over() from students;
这样貌似还简单一些
(
select '男' sex ,20 qty from dual
union all
select '男' sex ,20 qty from dual
union all
select '女' sex ,20 qty from dual
union all
select '男' sex ,20 qty from dual
union all
select '男' sex ,20 qty from dual
)
select
max(case when sex='男' then qty else 0 end ) +max(case when sex='女' then qty else 0 end )
from students;
from ty a,ty b
where a.sex=b.sex and rownum=1