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 阅读(375)
评论(0) 编辑 收藏 所属分类:
oracle/mysql/sql