应用count结合nvl函数时撞的题目

count()函数功能:统计表中面临有只字段或持有记录个数,字段值为null的不举行统计。

手册中解释:

COUNT returns the number of rows
returned by the query. You can use it as an aggregate or analytic
function.

If you specify DISTINCT, then you can
specify only the query_partition_clause of the analytic_clause. The
order_by_clause and windowing_clause are not allowed.

If you specify expr, then COUNT
returns the number of rows where expr is not null. You can count
either all rows, or only distinct values of expr.

If you specify the asterisk (\), then
this function returns all rows, including duplicates and nulls. COUNT
never returns null.*

 

自从手册可以得视是count()返回的值为Number,也就是说即使有表中一致长记下没有,返回的是0。而nvl()或者nvl2()函数判断也规范也是否为Null

(函数用法参见:http://www.cnblogs.com/space-place/p/5151913.html),故而进行nvl(count(\*),1)会出现应用错误。

 

化解措施:用decode函数。

 

举例:

1.树平等摆放空表,不栽任何数。

wwwlehu6.vip乐虎官网 1

 

2.nvl2和count()结合使用:wwwlehu6.vip乐虎官网

wwwlehu6.vip乐虎官网 2

可见返回了错误信息,表test2中连从未数量也回到了1.原因纵然是count(id)返回了0,而nvl2()判断为null:

wwwlehu6.vip乐虎官网 3

3.据此decode()函数解决:

wwwlehu6.vip乐虎官网 4

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图