1
table:citys
city ran
廣州 A
廣州 B
廣州 C
廣州 D
city ran
廣州 A,B,C,D
請問oracle 的sql語句要怎么寫?
select city,wmsys.wm_concat(ran)
from citys
group by city
2 備忘
insert into emp (id,name,sex,tim) --dual
values(id_seq.nextval,'junly',default,to_date('2010-5-11 11:25:00','yyyy-mm-dd hh24:mi:ss'))
3 直接裝載
--直接裝載方式
insert /**//*+append*/ into emp (field1,field2,field3)
select f1,f2,f3 from tep
where f4=20;
4 更新
--更新
update emp set (field1,field2,field3)=(
select f1,f2,f3 from tep where f4=20)
where field4=100;
5 取消重復
select count(distinct user_name) as num
from user_info
6 group by + rollup 橫向小計
-- group by + rollup 橫向小計
select num1,sum(num2),sum(num3) from tmp_tb
group by rollup (num1)
7 group by + cube 縱向小計
-- group by + cube 縱向小計
select num1,sum(num2),sum(num3) from tmp_tb
group by cube(num1)
8 自連接
-- 自連接
/**//* user_info
id name pid
-------------------
7888 king
7889 blank 7888
7900 jones 7888
*/
select manager.name from user_info manager,user_info worker
where manager.id=worker.pid
and worker.name='jones';
----------
king
9 ALL和ANY（不能單獨使用，與單行比較符［=,>,<,>=,<=,<>］結合使用）
--ALL和ANY（不能單獨使用，與單行比較符［=,>,<,>=,<=,<>］結合使用）
/**//*
ALL 必須要符合子查詢結果的所有值
ANY 只要符合子查詢結果的任一個值即可
*/
select user_name,money from user_info where money >all(
select money form user_game where id=10);
select user_name,money from user_info where money >any(
select money form user_game where id>10);
10 合并查詢
--（1）union 取兩個結果集的并集，自動去掉重復行并以第一列的結果排序
--（2）union all 取兩個結果集的并集，不去重復行也不進行排序
--（3）intersect 取兩個結果集的交集
--（4）minus 取兩個結果集的差集
select uname,sal from emp where sal>200
union
select uname,sal from emp where job='aaa'
11 case條件分支
select name,case when money>3000 then 3
when money>2000 then 2 when money>1000 then 1 end
from user_info where user_id=10000;
12 with子名重用子查詢
--with子名重用子查詢
with summary as (
select name,sum(money) as total from user_info
group by name
)
select name,total from summary
where total>3000;
13 connect by (感謝廣州Nicholas兄)
select sysdate - rownum rn from dual connect by rownum<100
--日
select to_number(to_char(rn,'yyyymmdd'))rn from(select sysdate - rownum rn
from dual connect by rownum<(
select floor(sysdate-regist_time)from sales_info where user_id=15587657))
--月份
select to_number(to_char(rn,'yyyymm'))rn from(
select add_months(sysdate,-rownum) rn
from dual connect by rownum<(
select floor(months_between(sysdate,regist_time)) from sales_info where user_id=15587657))
14 批理修改
merge into sales_info s
using tb_rd_user_info u
on (s.user_id=u.user_id)
when matched then
update
set s.user_name=u.user_name;
commit;
15 刪除重復記錄
delete from user where rowid in (
select max(rowid) from user group by userName having count(userName)>1)
posted on 2010-07-28 09:46
junly 閱讀(387)
評論(0) 編輯 收藏 所屬分類:
oracle/mysql/sql