刚刚毕业到公司,看这么长的SQL有点困难。例如:
select * from
( select s.*,row_number() over(ORDER BY rownum) id_tj, COUNT(1) over() ID_TJ_ALL from
(SELECT DISTINCT * FROM
(select sl.yxtywlsh, sl.ywlsh, sl.xmmc,t.blzt,sl.sjbbh, sl.spsxbh, sl.spsxzxbh, sl.spsxbh || '-' || sl.spsxzxbh as SPSXZX, sl.slsj, sl.sqdwhsqrxm, sl.status,'sl' as type from t_jc_xzxk_busiindex t, t_jc_xzxk_shouli sl where sl.ywlsh=t.ywlsh(+) and sjbbh =
(select max(sjbbh) from t_jc_xzxk_shouli where ywlsh = sl.ywlsh) and sl.spsxbh||'-'||sl.spsxzxbh in (SELECT B.ID||'-'||B.ITEMS FROM T_JC_XZXK_PERMISSIONOFGROUP A, T_JC_XZXK_PERMISSIONITEMSYS B, T_SYS_USEROFUSERGROUP C WHERE A.XKSXBH = B.ID AND A.XKSXZX = B.ITEMS AND C.USERGROUPID = A.USERGROUPID AND A.STATUS = 1 AND B.STATUS = 1 AND C.STATUS = 1 AND C.USERID = '999999999999999' and b.version =
(select max(version) from t_jc_xzxk_permissionitemsys where id=b.id and items=b.items) ) UNION ALL select wssl.yxtywlsh, wssl.ywlsh,wssl.byzda as xmmc,v.blzt,wssl.sjbbh, wssl.spsxbh, wssl.spsxzxbh, wssl.spsxbh || '-' || wssl.spsxzxbh as SPSXZX, wssl.wstjsj AS slsj, wssl.sqrhsqdw as sqdwhsqrxm, '-1' as status,'wssl' as type from t_jc_xzxk_busiindex v,t_jc_xzxk_wangshangshouli wssl where wssl.ywlsh = v.ywlsh(+) and wssl.spsxbh||'-'||wssl.spsxzxbh in (SELECT B.ID||'-'||B.ITEMS FROM T_JC_XZXK_PERMISSIONOFGROUP A, T_JC_XZXK_PERMISSIONITEMSYS B, T_SYS_USEROFUSERGROUP C WHERE A.XKSXBH = B.ID AND A.XKSXZX = B.ITEMS AND C.USERGROUPID = A.USERGROUPID AND A.STATUS = 1 AND B.STATUS = 1 AND C.STATUS = 1 AND C.USERID = '999999999999999' and b.version =
(select max(version) from t_jc_xzxk_permissionitemsys where id=b.id and items=b.items) ) and sjbbh =
(select max(sjbbh) from t_jc_xzxk_wangshangshouli where ywlsh = wssl.ywlsh) and not exists
(select 1 from t_jc_xzxk_shouli where ywlsh = wssl.ywlsh)
and wssl.status in ('1', '2')) wssl WHERE 1=1 and (wssl.blzt < 9
or wssl.blzt is null) ORDER BY wssl.slsj desc,wssl.ywlsh,wssl.sjbbh desc) s )
where id_tj between 1 and 10
select * from
( select s.*,row_number() over(ORDER BY rownum) id_tj, COUNT(1) over() ID_TJ_ALL from
(SELECT DISTINCT * FROM
(select sl.yxtywlsh, sl.ywlsh, sl.xmmc,t.blzt,sl.sjbbh, sl.spsxbh, sl.spsxzxbh, sl.spsxbh || '-' || sl.spsxzxbh as SPSXZX, sl.slsj, sl.sqdwhsqrxm, sl.status,'sl' as type from t_jc_xzxk_busiindex t, t_jc_xzxk_shouli sl where sl.ywlsh=t.ywlsh(+) and sjbbh =
(select max(sjbbh) from t_jc_xzxk_shouli where ywlsh = sl.ywlsh) and sl.spsxbh||'-'||sl.spsxzxbh in (SELECT B.ID||'-'||B.ITEMS FROM T_JC_XZXK_PERMISSIONOFGROUP A, T_JC_XZXK_PERMISSIONITEMSYS B, T_SYS_USEROFUSERGROUP C WHERE A.XKSXBH = B.ID AND A.XKSXZX = B.ITEMS AND C.USERGROUPID = A.USERGROUPID AND A.STATUS = 1 AND B.STATUS = 1 AND C.STATUS = 1 AND C.USERID = '999999999999999' and b.version =
(select max(version) from t_jc_xzxk_permissionitemsys where id=b.id and items=b.items) ) UNION ALL select wssl.yxtywlsh, wssl.ywlsh,wssl.byzda as xmmc,v.blzt,wssl.sjbbh, wssl.spsxbh, wssl.spsxzxbh, wssl.spsxbh || '-' || wssl.spsxzxbh as SPSXZX, wssl.wstjsj AS slsj, wssl.sqrhsqdw as sqdwhsqrxm, '-1' as status,'wssl' as type from t_jc_xzxk_busiindex v,t_jc_xzxk_wangshangshouli wssl where wssl.ywlsh = v.ywlsh(+) and wssl.spsxbh||'-'||wssl.spsxzxbh in (SELECT B.ID||'-'||B.ITEMS FROM T_JC_XZXK_PERMISSIONOFGROUP A, T_JC_XZXK_PERMISSIONITEMSYS B, T_SYS_USEROFUSERGROUP C WHERE A.XKSXBH = B.ID AND A.XKSXZX = B.ITEMS AND C.USERGROUPID = A.USERGROUPID AND A.STATUS = 1 AND B.STATUS = 1 AND C.STATUS = 1 AND C.USERID = '999999999999999' and b.version =
(select max(version) from t_jc_xzxk_permissionitemsys where id=b.id and items=b.items) ) and sjbbh =
(select max(sjbbh) from t_jc_xzxk_wangshangshouli where ywlsh = wssl.ywlsh) and not exists
(select 1 from t_jc_xzxk_shouli where ywlsh = wssl.ywlsh)
and wssl.status in ('1', '2')) wssl WHERE 1=1 and (wssl.blzt < 9
or wssl.blzt is null) ORDER BY wssl.slsj desc,wssl.ywlsh,wssl.sjbbh desc) s )
where id_tj between 1 and 10
解决方案 »
- 如何利用程序的方法查询一个数据库中的所有表和这些表的属性?急!!
- 请问EXP/IMP 的日志写在那里了,谢谢
- C#与Oracle的连接问题Data Source
- 断电后 orcacle 的奇怪问题 请大家帮帮忙
- 在linux下,用oracle10g怎么设置字符集显示中文
- commit?
- 高手请进---对表查询如何得到这样的结果
- 安装9i客户端,在asp中调用数据访问组件时提示找不到客户端
- 64bit Sqlserver2012 链接服务器连接Oracle10g 32bit已被玩疯
- oracle11g无监听,协议适配器错误,
- 请问,PLSQL连接时用管理员登陆显示TNS无法解析,是什么原因
- 这样写有什么问题?
2. 要知道所查询的字段的数据类型
3. 不要着急,一段一段地把SQL语句拆开来理解。我想这肯定是已经完成的项目中的SQL语句,可以结合这个项目的界面的的操作来更快的理解(主要是看一下这个界面都涉及到了哪些数据)。
然后逻辑就比较清晰了,可以看要查询的目标字段是哪个,从哪个表取。
例如这语句本来是procedure里面,
有多个子查询,是不是应该从最下层的子查询逐步想上层延伸了?
可能是经验问题吧。