假设有table:
CREATE TABLE Test (
cid number(3),
sex char(1),
PRIMARY KEY (cid)
); sex = 'm' or 'f'如何写sql语句获得m和f的比例(即m/f)?Thanks!
CREATE TABLE Test (
cid number(3),
sex char(1),
PRIMARY KEY (cid)
); sex = 'm' or 'f'如何写sql语句获得m和f的比例(即m/f)?Thanks!
调试欢乐多
select sum(case when sex='m' then 1 else 0 end)/count(1),
sum(case when sex='f' then 1 else 0 end)/count(1)
from test
FROM(
SELECT SUM(DECODE(SEX,'m',1,0)) 'M',SUM(DECODE(SEX,'f',1,0)) 'F'
FROM TABLE
GROUP BY SEX
)T
select sum(case when sex='m' then 1 else 0 end)/count(1),
sum(case when sex='f' then 1 else 0 end)/count(1)
from test
我现在正在做web based 的data warehouse system. 因为对DB不熟悉,不知道有这些函数decode(), case()可以使用,今天真是学习了!我原本已经在Perl中写subroutine得到了结果。但是看来sql更简洁。