使用子查询提高查询效率

故障现象：多查询一个字段时效率很低，少一个字段时效率快10倍

解决方案：使用子查询提高查询效率

 

实例：

SELECT

BTS.EMS_NE_NAME AS DEVICE_NAME,'3G' AS DEVICE_CATEGORY

,'光缆' AS COMPARE_TYP,SEG_ROAD.

OP_CABLE_SEG_NAME AS OBJ_NAME ,

--ROAD.OP_ROAD_NAME AS OBJ_OPTICAL,

CARR.CARRIER_OBJ_NAME, CARR.OBJ_NAME,CARR.OBJ_ID,

BTS.DATA_DOMAIN_ID,SEG_ROAD.

OP_CABLE_SEG_CODE,

(

SELECT ROAD.OP_ROAD_NAME FROM T_OP_ROAD ROAD WHERE (CARR.OBJ_ID = ROAD.OP_ROAD_ID)

) AS OBJ_OPTICAL,

 

CASE BTS.MARRY_STATE

WHEN 1

THEN '网管与资源匹配'

ELSE '资源缺失'

END

AS RES_STATUS,

CASE

WHEN (BTS.EMS_NE_NAME IS NULL OR

SEG_ROAD.OP_CABLE_SEG_ID IS NULL)

THEN '关联失败'

ELSE '关联成功'

END

AS "COMPARE_RESULT",'' AS TOWER_NAME,'' AS TOWER_CODE

FROM

T_WX_BTS BTS

LEFT JOIN T_WX_CARRIER CARR

ON (CARR.CARRIER_OBJ_ID=BTS.RES_NE_ID AND

OBJ_TYPE=8)

--LEFT JOIN T_OP_ROAD ROAD ON (CARR.OBJ_ID = ROAD.OP_ROAD_ID)

LEFT JOIN T_CABLESEG_RE_ROAD SEG_ROAD

ON(SEG_ROAD.OP_ROAD_ID=CARR.OBJ_ID)

WHERE

BTS.MARRY_STATE IN(1,2)

AND BTS.DATA_DOMAIN_ID IN (1030005,1070001,1030001,1150001,1010003,1060001,1050001,1090001,1140001,1080001,1020001,1040001,1100001,1020003,1010004)

 

更多精彩技术分享请浏览本人博客：https://blog.csdn.net/wohiusdashi