oracle sql常用sql - 《SQL相关》

select
    *
from
    o2o.c_order_history
where
    send_time >= to_date('2020-04-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    and rownum <= 100;
----
select count（*） from user_tables ;
----
select
    t1.pharmacy_id,
    t3.name,
    t1.product_id,
    t2.name,
    t1.status
from
    (
        select
            product_id,
            pharmacy_id,
            status
        from
            o2o.p_city_fever_sku_item
        where
            status = 1
            and type = 1
    ) t1
    join (
        select
            id,
            name
        from
            o2o.p_product
    ) t2 on t1.product_id = t2.id
    join (
        select
            id,
            name
        from
            o2o.p_pharmacy
        where
            city in ('1901', '1906')
    ) t3 on t1.pharmacy_id = t3.id

1.NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例如：

SQL> select ename,NVL(comm, -1) from emp;

2.NVL2函数的格式如下：NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。SQL> select ename,NVL2(comm,-1,1) from emp;

3.NULLIF函数

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
下面是一个例子。使用的是oracle中HR schema，如果HR处于锁定，请启用

这里的作用是显示出那些换过工作的人员原工作，现工作。

SQL> SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) “Old Job ID”

FROM employees e, job_history j

WHERE e.employee_id = j.employee_id

ORDER BY last_name;

4.Coalesce函数
Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。
格式如下：
Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
返回表达式中第一个非空表达式，如有以下语句：　　SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 　　其返回结果为：3
如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。　　COALESCE(expression1,…n) 与此 CASE 函数等价：
这个函数实际上是NVL的循环使用，在此就不举例子了。

5、日期相关函数用法：

trunc((to_char(sysdate,'yyyyMMdd')-to_char(birth_date,'yyyyMMdd'))/10000)
trunc((to_char(sysdate,'yyyyMMdd')-to_char(to_date('2004-11-26','yyyy-MM-dd'),'yyyyMMdd'))/10000)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转zhidao化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日回
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒答

6、其他可用函数

 --此函数专门用来脱敏
 translate(s1.user_id, '0123456789', 'asdfghjklp') 
 -- 将多列数据转为一行，以逗号分隔
 to_char(WM_CONCAT(product.name))
 --Oracle中两个日期相差天数--
select TO_NUMBER(TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
AS 相差天数 from dual
--Oracle中两个日期相差小时数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24)
AS 相差小时数 from dual;
--Oracle中两个日期相差分钟数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60)
AS 相差分钟数 from dual;
--Oracle中两个日期相差秒数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60*60)
AS 相差秒数 from dual;
--oracle两个日期的相差月数--
--1）月份都是最后一天，A日期 > B日期 ,返回整数 ---
select months_between(TO_DATE('2018-6-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份1 from dual;
--2）月份都是最后一天，B日期 > A日期 ,返回负数 ---
select months_between(TO_DATE('2018-4-30','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份2 from dual;
--3）月份天数不一样，A日期 > B日期 ,返回带小数的数字---
select months_between(TO_DATE('2018-6-25','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))
As 相差月份3 from dual;
--oracle两个日期的相差年份--
select ((months_between(TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'),TO_DATE('2016-5-31','yyyy-mm-dd hh24:mi:ss')))/12)
As 相差年份 from dual;