425   Oracle

1, 分组计数,计数每个地市的用户数,关键函数 group by col_name

select city_code city_code,count(*) count_by_id from user group by city_code;

2,分组计数并去重,计数每个地市的店铺数量,关键函数distinct(col_name);

select city_code city_code,count(*) count_by_id,count(distinct(shop_id)) count_by_shop from user group by city_code;

3,两个表格左关联,以左表为基准,右表显示相应记录;user为左表,根据shop_id关联右表shop,显示user对应的shop信息;

select * from user left join shop on user.shop_id = shop.id

4,取上个月当天的日期

select add_months(trunc(sysdate,'DD'),-1) from dual

5,截取日期函数

select to_char(sysdate,'MM,DD') from dual

6,判断函数

decode(state, 1, '是', null)
如果state==1则为是,否则为null
decode(state, 3, '成功', 2,'失败',1,'未知')
如果state==3,2,1,对应状态分别为成功,失败,未知

select city_code city_code , count(decode(state, 3, 1, null)) count_by_success from order
计算各地市订单成功的数量

7,判断函数2

select city_code city_code , count(case when state = 3 then 1  else null end ) count_by_success from order

8,判断和去重函数共用

count(distinct(case when state = 3 then 1  else null end then "shop_id"  else null end )) count_by_shop from order

9,取得上个月第一天

select trunc(add_months(sysdate,-1),'MM') from dual;

10,创建自增列

select level   from dual connect by level <= 10

11,字符串转时间

select  to_date('20151118','yyyy-mm-dd') from dual;

12,字符串截取函数

select to_date(substr('2015-11-17,2015-11-19',12,10),'yyyy-mm-dd') from dual;

13,取上月,前月的最后一天

select to_char(add_months(sysdate,-1),'yyyymmdd') from dual
select to_char(add_months(sysdate,-2),'yyyymmdd') from dual

14,逐行累加

select num,
sum(num) over(order by num)
from( select level num from dual connect by level <= 10)

15,查看版本信息

select * from v$version;



Leave a Reply

Your email address will not be published. Required fields are marked *