select distinct mbrid,dos from fi05 where
(
dx1 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
or dx2 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
......
or dx9 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
)
and
(
(
((cpt between '99217' and '99220') or (cpt between '99234' and '99236'))
or ((cpt1 between '99217' and '99220') or (cpt1 between '99234' and '99236'))
..........
or ((cpt9 between '99217' and '99220') or (cpt9 between '99234' and '99236'))
)
or
(
((rev between '0500' and '0529') or (rev between '0570' and '0599') or (rev between '0770' and '0779') or (rev between '0820' and '0859' )
or (rev between '0982' and '0983') or (rev = '0882'))
or((rev1 between '0500' and '0529') or (rev1 between '0570' and '0599') or (rev1 between '0770' and '0779') or (rev1 between '0820' and '0859')
or (rev1 between '0982' and '0983') or (rev1 = '0882'))
...............
or ((rev9 between '0500' and '0529') or (rev9 between '0570' and '0599') or (rev9 between '0770' and '0779') or (rev9 between '0820' and '0859')
or (rev9 between '0982' and '0983') or (rev9 = '0882'))
)
) 就这段代码怎么能把这几个查询条件写成视图啊,然后和表连接查询。都说那么查询速度会很快,我的这个查询是几百万的数据量。
解决方案 »
- 各位侠客,我请问个问题,刚oracle9i里我建错了一些表和试图,索引什么的
- 存储参数(storage子句)含义
- avg 疑问
- 初学者问题:要在自己的机上安装pl/sql developer连服务器的oracle9i,要先装什么?oracle9i客户端吗?
- XP下Oracle数据库不会自动启动怎么办啊?
- 怎样修改ORACLE的internal的密码为其它或者失效?
- 如何在删除主键纪录的时候删除其他表中对应的外键??
- 新手赐教,请问这个存储过程怎么有错?
- 怎样让数据库返回查询只是固定行数,譬如10行
- 这样的sql语句怎么处理比较好呢??
- oracle中出现了换行怎么办?/r/n
- 求CNOUG邀请码
create view test_dx as select distinct mbrid,dos from fi05 where
(
dx1 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
........
or dx9 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
) create view test_cpt as select distinct mbrid,dos from fi05 where
(
((cpt between '99217' and '99220') or (cpt between '99234' and '99236'))
....
or ((cpt9 between '99217' and '99220') or (cpt9 between '99234' and '99236'))
)
create view test_rev as select distinct mbrid,dos from fi05 where
((rev between '0500' and '0529') or (rev between '0570' and '0599') or (rev between '0770' and '0779') or (rev between '0820' and '0859' )
or (rev between '0982' and '0983') or (rev = '0882'))
........
or ((rev9 between '0500' and '0529') or (rev9 between '0570' and '0599') or (rev9 between '0770' and '0779') or (rev9 between '0820' and '0859')
or (rev9 between '0982' and '0983') or (rev9 = '0882'))
select b.mbrid from fi05 a,test_dx b,test_cpt c,test_rev d where a.mbrid = b.mbrid and (a.mbrid = c.mbrid or a.mbrid = d.mbrid)
可以写成这样嘛? 是这么写嘛 ? 高手帮帮我吧
有一个表fi 表中有好多的字段。主要有dx1..dx9,cpt1..cpt9,rev1..rev9,还有就是mbrid 也就是id
求出满足dx1..dx9中任意一个字段等于402.01, 402.11, 402.91, 404.01, 404.11, 404.91, 428.0, 428.1, 428.20-428.23, 428.40-428.43 其中的任意一个。
cpt1..cpt9 中任意一个字段等于99201-99205, 99211-99215, 99241-99245, 99271-99275, 99301-99313, 99315-99316, 99318-99337, 99341-99350, 99354-99355, 99381-99387, 99391-99397,99401-99429,99450, 99455-99456 中的任意一个。
rev1..rev9 中的任意一个字段等于 0500-0529, 0570-0599, 0770-0779, 0820-0859, 0882, 0982-0983 中任意一个
dx和 cpt 还有 rev的关系是 dx and (cpt or rev)
最后是求满足这些条件的人的 mbrid