一個分頁內嵌套的查詢腳本
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
有貓相伴的日子 閱讀(1623)
評論(0) 編輯 收藏 所屬分類:
pl/sql