Oracle_Count函数

COUNT

Syntax

Description of count.gif follows
Description of the illustration count.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

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.

See Also:

"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"

Aggregate Examples

The following examples use COUNT as an aggregate function:

SELECT COUNT(*) "Total"
  FROM employees;

     Total
----------
       107

SELECT COUNT(*) "Allstars"
  FROM employees
  WHERE commission_pct > 0;

 Allstars
---------
       35

SELECT COUNT(commission_pct) "Count"
  FROM employees;

     Count
----------
        35

SELECT COUNT(DISTINCT manager_id) "Managers"
  FROM employees;

  Managers
----------
        18

Analytic Example

The following example calculates, for each employee in the employees table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.

SELECT last_name, salary,
       COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
                      150 FOLLOWING) AS mov_count
  FROM employees
  ORDER BY salary, last_name;

LAST_NAME                     SALARY  MOV_COUNT
------------------------- ---------- ----------
Olson                           2100          3
Markle                          2200          2
Philtanker                      2200          2
Gee                             2400          8
Landry                          2400          8
Colmenares                      2500         10
Marlow                          2500         10
Patel                           2500         10

SQL> select comm from emp;

      COMM
----------

       300
       500

      1400




         0


      COMM
----------




14 rows selected.

SQL> select distinct comm from emp;

      COMM
----------

      1400
       500
       300
         0

5 rows selected.

SQL> select comm from emp;

      COMM
----------

       300
       500

      1400




         0


      COMM
----------




14 rows selected.

SQL> select distinct comm from emp;

      COMM
----------

      1400
       500
       300
         0

5 rows selected.

SQL> select count(comm) from emp where comm is not null;

COUNT(COMM)
-----------
          4

1 row selected.

SQL> select count(comm) from emp where comm is null;

COUNT(COMM)
-----------
          0

1 row selected.

总结:count(*)结果包含NULL行,count(某一列)不包含空行。


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值