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'))
)
) 就这段代码怎么能把这几个查询条件写成视图啊,然后和表连接查询。都说那么查询速度会很快,我的这个查询是几百万的数据量。
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