DOC

function

By Hector Howard,2014-06-17 07:56
37 views 0
sql server

字符函数

数字函数

转换函数

select ename, job, row_number() over(order by sal) as sal_rank from emp;

1 SMITH CLERK 1

    2 JAMES CLERK 2

    3 ADAMS CLERK 3

    4 WARD SALESMAN 4

    5 MARTIN SALESMAN 5

    6 MILLER CLERK 6

    7 TURNER SALESMAN 7

    8 ALLEN SALESMAN 8

    9 CLARK MANAGER 9

    10 BLAKE MANAGER 10

    11 JONES MANAGER 11

    12 SCOTT ANALYST 12

    13 FORD ANALYST 13

    14 KING PRESIDENT 14

1 10 MILLER CLERK 1

    2 10 CLARK MANAGER 2

    3 10 KING PRESIDENT 3 4 20 SMITH CLERK 1

    5 20 ADAMS CLERK 2

    6 20 JONES MANAGER 3

    7 20 SCOTT ANALYST 4

    8 20 FORD ANALYST 5 9 30 JAMES CLERK 1

    10 30 WARD SALESMAN 2

    11 30 MARTIN SALESMAN 3

    12 30 TURNER SALESMAN 4

    13 30 ALLEN SALESMAN 5

    14 30 BLAKE MANAGER 6

select deptno, ename, sal, rank() over(partition by deptno order by sal

    desc) as rank from emp; 1 10 KING 5000.00 1

    2 10 CLARK 2450.00 2

    3 10 MILLER 1300.00 3

    4 20 SCOTT 3000.00 5 20 FORD 3000.00 6 20 JONES 2975.00 7 20 ADAMS 1100.00 8 20 SMITH 800.00 9 30 BLAKE 2850.00 1

    10 30 ALLEN 1600.00 2

    11 30 TURNER 1500.00 3

    12 30 WARD 1250.00 13 30 MARTIN 1250.00 14 30 JAMES 950.00

select d.deptno, e.ename, e.sal,

    dense_rank() over(partition by e.deptno order by e.sal desc) denserank

    from emp e, dept d where e.deptno = d.deptno;

    1 10 KING 5000.00 1

    2 10 CLARK 2450.00 2

    3 10 MILLER 1300.00 3 4 20 SCOTT 3000.00 1

    5 20 FORD 3000.00 1

    6 20 JONES 2975.00 2

    7 20 ADAMS 1100.00 3

    8 20 SMITH 800.00 4 9 30 BLAKE 2850.00 1

    10 30 ALLEN 1600.00 2

    11 30 TURNER 1500.00 3

    12 30 WARD 1250.00 13 30 MARTIN 1250.00

    14 30 JAMES 950.00

select rownum, ename, job from emp order by sal;

    1 1 SMITH CLERK

    2 12 JAMES CLERK

    3 11 ADAMS CLERK

    4 3 WARD SALESMAN

    5 5 MARTIN SALESMAN 6 14 MILLER CLERK 7 10 TURNER SALESMAN 8 2 ALLEN SALESMAN 9 7 CLARK MANAGER 10 6 BLAKE MANAGER 11 4 JONES MANAGER 12 8 SCOTT ANALYST 13 13 FORD ANALYST

     14 9 KING RESIDENT

Report this document

For any questions or suggestions please email
cust-service@docsford.com