一个电影的数据库,我想统计各类电影的数目,并将它们的名称、类型、该类型电影数目显示出来
问题是在category里有的列值为null,在计算的时候SQL就把值为Null那些行给忽略了,要怎么写代码才对?
用上isnull函数的话?
代码:
use OracleMoviegoselect m.title, m.category, num 'number of the category'
from movie m,
(select category, count(*) num
from movie
group by category)m2
where m.category = m2.category
order by title
结果:
title category number of the category
A Beautiful Mind Drama 6
Amelie Foreign 3
Beautiful Life Foreign 3
Cast Away Drama 6
City of Angels Drama 6
E.T. Kids 3
Field of Dreams Family 1
Gone in 60 Seconds Action 3
Himalaya Foreign 3
Horse Whisperer Drama 6
Monster's Ball Drama 6
Monsters, Inc. Kids 3
Ocean's Eleven Action 3
Road to Perdition Drama 6
Spiderman Kids 3
The Royal Comedy 2
U-571 Action 3
Wild, Wild West Comedy 2
问题是在category里有的列值为null,在计算的时候SQL就把值为Null那些行给忽略了,要怎么写代码才对?
用上isnull函数的话?
代码:
use OracleMoviegoselect m.title, m.category, num 'number of the category'
from movie m,
(select category, count(*) num
from movie
group by category)m2
where m.category = m2.category
order by title
结果:
title category number of the category
A Beautiful Mind Drama 6
Amelie Foreign 3
Beautiful Life Foreign 3
Cast Away Drama 6
City of Angels Drama 6
E.T. Kids 3
Field of Dreams Family 1
Gone in 60 Seconds Action 3
Himalaya Foreign 3
Horse Whisperer Drama 6
Monster's Ball Drama 6
Monsters, Inc. Kids 3
Ocean's Eleven Action 3
Road to Perdition Drama 6
Spiderman Kids 3
The Royal Comedy 2
U-571 Action 3
Wild, Wild West Comedy 2
select m.title, m.category, num 'number of the category'
from movie m,
(select category, sum( case category when is null then 0 esle 1 end) num
from movie
group by category)m2
where m.category = m2.category
order by title
from movie m
order by m.title
select m.title, m.category, num 'number of the category'
from movie m,
(select category, count(*) num
from movie
group by category)m2
where isnull(m.category,'') = isnull(m2.category,'')
order by title-- 2005
select title, category, count(*)over(partition by category) 'number of the category]' from movie
-->
where isnull(m.category,'') = isnull(m2.category,'')