一个分页内嵌套的查询脚本
select *
from ( select E. * ,rownum row_id from
( select A.ST_ID st_id,
A.SUBSCRBID subscrbid,
A.SVCNUM svcnum,
A.USERNAME username,
A.CLUBLEVEL clublevel,
A.SCORE score,
( select nvl(name, '' ) from CODE_SVCID where code = A.Svcid) svcid,
( select nvl(substr(name,instr(name, ' . ' ) + 1 , 4 ), '' ) from code_areaid where code = A.cityid) cityid,
( select nvl(BCT_NAME, '' ) from BC_TEAM where BCT_ID = A.Bct_Id) bct_id,
nvl(( select name from bc_managerinfo where bcmi_id = A.Exeman ), ' 不详 ' ) exeman,
A.LINKMAN linkman,
A.LINKPHONE linkphone,
( select nvl(code_abbr, '' ) from bcms_code_base where type_id = ' 00009 ' and code_id = A.State) state,
A.CREATEDATE createdate,
A.REMARK remark
from ss_Club_SaleTask A
where A.STATE = ' 1 ' and A.svcnum = ' 13071593789 ' and 1 = 1 ) E where rownum <= 30
) where row_id > 0
连接查询的作用是从多个表中使用相等或不相等的连接方式存储资料，
1、表格连接
相等的连接一般使用两上表中有相同的字段及字段值，当两上表被join后，只能看到两个条件联系的字段价是相等到的记录，除非使用了外连结(outer join 或+)。N个表格的连接需要N-1个条件表达式，所以两个表的连结需要一个条件以上的表达式。
SQL > SELECT name,sal FROM employees e,dept d WHERE e.deptno=d.deptno ← 一般
SQL > SELECT name,sal FROM employees e JOIN dept d ON e.deptno=d.deptno ← ANSI语法
以上两行条码表示了同一个意思。
2、外连结
当两个表外部连接条件，两个字段值没有区配(match)时可以使用外部连接
3、自我连结
SELECT e1.name,e1.sal FROM employee e1,employee e2 WHERE e1.empno=e2.empno ;
4、卡笛儿积
SELECT last_name, department_name
FROM employees
CROSS JOIN departments;
5、自动连结两表查询
两表必须有相同的字段名和字段类型才能使用这种方法查询
select seq_id,u_id,email from t_email A natural join t_user B
6、使用join on建立多表查询
SELECT employee_id , city , department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id ;
当使用连结果查询时，没有匹配的记录并没有出现在查询结果中，如果要包含没有符合条件的记录，则必须使用外部连结的方式，
7、left outer join外部连接
select A.Seq_Id,A.u_Id,A.Email from t_email A left outer join t_user B on A.u_Id=B.Id
这个语句的查询结果将包启A 表中不符合条件的记录,这是一个等条件的pl/sql查询语句
select A.Seq_Id,A.u_Id,A.Email from t_email A ,t_user B where B.Id(+)=A.u_Id
select name,vv,count(*) from tt_job group by NAME,vv having count(*) > 1--查询重复记录
delete tt_job where name in (select name from (select name,count(*) from tt_job group by NAME having count(*) > 1))
delete tt_job a
where (a.name ,a.vv )in (select name,vv
from (select name,vv, count(*)
from tt_job
group by NAME,vv
having count(*) > 1)) --删掉所有重复记录
delete from tt_job a
where a.rowid != (select max(b.rowid)
from tt_job b
where a.name = b.name
and a.vv = b.vv) --只保留一条重复记录
select distinct table_name from USER_COL_COMMENTS --过虑字段中有重复的记录
几个统计语句
--区域统计分析
select sum(sell_count) ,sum(sell_amount) ,ROUND(sum(sell_amount)/sum(sell_count),3),round(sum(sell_count) /count(Terminal_Sell_Id),2)网点平均售额,max(sell_amount),min(sell_amount) ,term ,to_char(open_result_time,'yyyy-mm'),C.AREA_NAME,D.Playtype_Name
from terminal_sell A,touzhuzhan_info B,area C,playtype D
where B.Sell_Id(+)=A.Terminal_Sell_Id
and C.area_id(+)=B.Area_Id
and D.PLAYTYPE_ID(+)=A.Lottery_Type
group by term ,to_char(open_result_time,'yyyy-mm'),C.AREA_NAME,D.Playtype_Name
--历史高/低统计(按时段分)
select max(sell_amount) from terminal_sell --日历史最高
select max(sell_amount),qq from (select sum(sell_amount)sell_amount, to_char(open_result_time,'yyyy-q') qq from terminal_sell group by to_char(open_result_time,'yyyy-q')) group by qq --按季度
select max(sell_amount) from (select sum(sell_amount)sell_amount from terminal_sell group by to_char(open_result_time,'yyyy-mm')) --按月
select max(sell_amount) from (select sum(sell_amount)sell_amount, to_char(open_result_time,'yyyy-ww') ww from terminal_sell group by to_char(open_result_time,'yyyy-ww')) --按周统计
posted on 2006-12-08 16:40
有猫相伴的日子 阅读(1596)
评论(0) 编辑 收藏 所属分类:
pl/sql