各位大侠,有以下的难题请帮忙解决一下:现在有四张表 BTSPF_FACIINF F, 字段(BTS_GENID COL_056 COL_057 COL_058 COL_059 COL_060 COL_061)
MMS_ESTINF M, 字段(MMS_GENID)
RNC_ESTINF R, 字段(RNC_GENID MMS_GENID)
BTS_ESTINF B 字段(BTS_GENID RNC_GENID VENDERID MACHINTYPE) 满足以下条件的记录的MMS_GENID RNC_GENID BTS_GENID列筛选出来:
(1)
M.MMS_GENID = R.MMS_GENID AND
R.RNC_GENID = B.RNC_GENID AND
B.BTS_GENID = F.BTS_GENID AND
(2)
并且满足以下条件之一:
当B.VENDERID = 2 AND B.MACHINTYPE = 11
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,4)之间。并且都不等于-1
当B.MACHINTYPE = 21
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,16)之间。并且都不等于-1 当B.MACHINTYPE = 21
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,8)之间。并且都不等于-1请问where条件该如何写,才能正确并效率高呢?
MMS_ESTINF M, 字段(MMS_GENID)
RNC_ESTINF R, 字段(RNC_GENID MMS_GENID)
BTS_ESTINF B 字段(BTS_GENID RNC_GENID VENDERID MACHINTYPE) 满足以下条件的记录的MMS_GENID RNC_GENID BTS_GENID列筛选出来:
(1)
M.MMS_GENID = R.MMS_GENID AND
R.RNC_GENID = B.RNC_GENID AND
B.BTS_GENID = F.BTS_GENID AND
(2)
并且满足以下条件之一:
当B.VENDERID = 2 AND B.MACHINTYPE = 11
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,4)之间。并且都不等于-1
当B.MACHINTYPE = 21
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,16)之间。并且都不等于-1 当B.MACHINTYPE = 21
并且F.COL_056 COL_057 COL_058 COL_059 COL_060 COL_061 不在(1,8)之间。并且都不等于-1请问where条件该如何写,才能正确并效率高呢?
SELECT DISTINCT
M.MMSNO,
R.RNCNO,
B.BTSNO FROM
BTSPF_FACIINF F,
MMS_ESTINF M,
RNC_ESTINF R,
BTS_ESTINF B WHERE
M.MMS_GENID = R.MMS_GENID AND
R.RNC_GENID = B.RNC_GENID AND
B.BTS_GENID = F.BTS_GENID AND
(
(
(
((F.COL_056 != -1 AND F.COL_056 < 1) OR F.COL_056 > 4) OR
((F.COL_057 != -1 AND F.COL_057 < 1) OR F.COL_057 > 4) OR
((F.COL_058 != -1 AND F.COL_058 < 1) OR F.COL_058 > 4) OR
((F.COL_059 != -1 AND F.COL_059 < 1) OR F.COL_059 > 4) OR
((F.COL_060 != -1 AND F.COL_060 < 1) OR F.COL_060 > 4) OR
((F.COL_061 != -1 AND F.COL_061 < 1) OR F.COL_061 > 4)
) AND
B.VENDERID = 2 AND
B.MACHINTYPE = 11) OR
(
(
((F.COL_056 != -1 AND F.COL_056 < 0.2) OR F.COL_056 > 8) OR
((F.COL_057 != -1 AND F.COL_057 < 0.2) OR F.COL_057 > 8) OR
((F.COL_058 != -1 AND F.COL_058 < 0.2) OR F.COL_058 > 8) OR
((F.COL_059 != -1 AND F.COL_059 < 0.2) OR F.COL_059 > 8) OR
((F.COL_060 != -1 AND F.COL_060 < 0.2) OR F.COL_060 > 8) OR
((F.COL_061 != -1 AND F.COL_061 < 0.2) OR F.COL_061 > 8)
) AND
B.MACHINTYPE = 21) OR
(
(
(F.COL_056 != -1 AND F.COL_056 != 0.1) OR
(F.COL_057 != -1 AND F.COL_057 != 0.1) OR
(F.COL_058 != -1 AND F.COL_058 != 0.1) OR
(F.COL_059 != -1 AND F.COL_059 != 0.1) OR
(F.COL_060 != -1 AND F.COL_060 != 0.1) OR
(F.COL_061 != -1 AND F.COL_061 != 0.1)
) AND
B.MACHINTYPE = 23)
) ORDER BY
M.MMSNO,
R.RNCNO,
B.BTSNO