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;