有如下数据
name address gender birthdate
insert into actor values('Djimon Hounsou', 'Paris FR', 'M', 1970);
insert into actor values('Anthony Hopkins', 'Port Talbort, Wales UK', 'M', 1937);
insert into actor values('Ming-Na Wen', 'Macau', 'F', 1963);
insert into actor values('Eddie Murphy', 'New York', 'M', 1961);
insert into actor values('Tom Hanks', 'California', 'M', 1956);
insert into actor values('Ed Harris', 'New Jersey', 'M', 1950);
insert into actor values('Bill Paxton', 'Texas', 'M', 1955);
insert into actor values('Sally Field', 'California', 'F', 1946);
insert into actor values('Liam Neeson', 'Northern Ireland, UK', 'M', 1952);
insert into actor values('Ben Kingsley', 'Yorkshire, England, UK', 'M', 1943);
insert into actor values('Danny Glover', 'California', 'M', 1947);
insert into actor values('Whoopi Goldberg', 'New York', 'F', 1955);
insert into actor values('Robin Williams', 'Illinois', 'M', 1952);
insert into actor values('Marcia Gay Harden', NULL, 'F', NULL);
insert into actor values('Mat Damon', 'Massachusetts', 'M', 1970);
insert into actor values('John Cusak', NULL, 'M', NULL);
insert into actor values('Jack Warden', 'New Jersey', 'M', 1920);
insert into actor values('Gwyneth Paltrow', 'California', 'F', 1972);
insert into actor values('James Cosmo', NULL, 'M', NULL);要求出出生在各个年份的男演员和女演员的人数。
怎么做?
name address gender birthdate
insert into actor values('Djimon Hounsou', 'Paris FR', 'M', 1970);
insert into actor values('Anthony Hopkins', 'Port Talbort, Wales UK', 'M', 1937);
insert into actor values('Ming-Na Wen', 'Macau', 'F', 1963);
insert into actor values('Eddie Murphy', 'New York', 'M', 1961);
insert into actor values('Tom Hanks', 'California', 'M', 1956);
insert into actor values('Ed Harris', 'New Jersey', 'M', 1950);
insert into actor values('Bill Paxton', 'Texas', 'M', 1955);
insert into actor values('Sally Field', 'California', 'F', 1946);
insert into actor values('Liam Neeson', 'Northern Ireland, UK', 'M', 1952);
insert into actor values('Ben Kingsley', 'Yorkshire, England, UK', 'M', 1943);
insert into actor values('Danny Glover', 'California', 'M', 1947);
insert into actor values('Whoopi Goldberg', 'New York', 'F', 1955);
insert into actor values('Robin Williams', 'Illinois', 'M', 1952);
insert into actor values('Marcia Gay Harden', NULL, 'F', NULL);
insert into actor values('Mat Damon', 'Massachusetts', 'M', 1970);
insert into actor values('John Cusak', NULL, 'M', NULL);
insert into actor values('Jack Warden', 'New Jersey', 'M', 1920);
insert into actor values('Gwyneth Paltrow', 'California', 'F', 1972);
insert into actor values('James Cosmo', NULL, 'M', NULL);要求出出生在各个年份的男演员和女演员的人数。
怎么做?
SQL> select birthdate,
2 count(decode(gender,'M',1,0)) m,
3 count(decode(gender,'F',1,0)) f
4 from actor
5 where birthdate is not null
6 group by birthdate
7 /
BIRTHDATE M F
--------- ---------- ----------
1920 1 1
1937 1 1
1943 1 1
1946 1 1
1947 1 1
1950 1 1
1952 2 2
1955 2 2
1956 1 1
1961 1 1
1963 1 1
1970 2 2
1972 1 1
应该是
select birthdate,
sum(decode(gender,'M',1,0)) m,
sum(decode(gender,'F',1,0)) f
from actor
where birthdate is not null
group by birthdate
--更正2楼的:
select birthdate,
nvl(sum(decode(gender,'M',1,0)),0) m,
nvl(sum(decode(gender,'F',1,0)),0) f
from actor
where birthdate is not null
group by birthdate
/
BIRTHDATE M F
--------- ---------- ----------
1920 1 0
1937 1 0
1943 1 0
1946 0 1
1947 1 0
1950 1 0
1952 2 0
1955 1 1
1956 1 0
1961 1 0
1963 0 1
1970 2 0
1972 0 1
13 rows selected
--
--这里需要注意一个小问题:
--小测试
with t as(
select 1 id,count(0) sm from dual union all
select 2,count(null) from dual union all
select 3,sum(0) from dual union all
select 4,sum(null) from dual)
select * from t
/
ID SM
---------- ----------
1 1
2 0
3 0
4
--count(),函数只是统计括号内的值,并不管是否为0
--为什么上面的答案错误呢?
--原因我们使用decode(gender,'M',1,0)这个结果,要么为1,要么为0
--不管结果为0还是1,count后它将为1,因为:
--count(1)=1,count(0)=1,count(1000)=1,...
--count只是统计数据类型数的存在数据个数,并不关心具体的值
--所以我们得到了错误的答案,而使用sum函数,则能得到正确的答案
--假设我们将decode函数改写为下面的样子,我们也能得到正确的答案:
select birthdate,
count(decode(gender,'M',1,null)) m,
count(decode(gender,'F',1,null)) f
from actor
where birthdate is not null
group by birthdate
/
BIRTHDATE M F
--------- ---------- ----------
1920 1 0
1937 1 0
1943 1 0
1946 0 1
1947 1 0
1950 1 0
1952 2 0
1955 1 1
1956 1 0
1961 1 0
1963 0 1
1970 2 0
1972 0 1
13 rows selected
--我们从上面的小测试知道count(null)=0,所以这里得到了正确答案
2 count(decode(gender,'M',1,0)) m,
3 count(decode(gender,'F',1,0)) f
decode是什么?