56,803
社区成员




SELECT COUNT(*) FROM SN_VIPLOADBALANCE A
LEFT JOIN SN_VIPLOADINFO_SERVER B ON B.VIPSVR_ROWID=A.ROW_ID AND B.VIPSVR_STATUS != 'DEL'
LEFT JOIN SN_VIPLOADINFO_HGP H ON A.ROW_ID = H.VIPHGP_ROWID AND B.VIPSVR_NUM = H.VIPHGP_NUM AND H.VIPHGP_STATUS != 'DEL'
LEFT JOIN SN_HOSTGROUP J ON H.VIPHGP_HGPNUM = J.HOSTGROUP_NUM AND J.HOSTGROUP_STATUS != 'DEL'
LEFT JOIN SN_SERVER C ON C.SERVER_NUM = B.VIPSVR_SVRNUM AND C.SERVER_STATUS != 'DEL'
LEFT JOIN SN_SERVER_HISTORY I ON I.SVRNUM = B.VIPSVR_SVRNUM AND I.OPERATETYPE = 'DEL'
LEFT JOIN SN_SYSTEM K ON I.SYSCNAME = K.SYSTEM_FCNAME AND K.SYSTEM_STATUS != 'DEL'
LEFT JOIN SN_RACKDEV RD ON C.SERVER_SVRNUM = RD.RACKDEV_DEVNUM
LEFT JOIN SN_RACK R ON RD.RACKDEV_RACKNUM = R.RACK_NUM AND R.RACK_STATUS != 'DEL'
LEFT JOIN SN_LOGICROOM L ON R.RACK_LOGICROOMNUM = L.LOGICROOM_NUM AND L.LOGICROOM_STATUS != 'DEL'
LEFT JOIN SN_INTRANETIP E ON E.INTRANETIP_DEVNUM = A.VIP_NLBNUM AND E.INTRANETIP_STATUS != 'DEL'
LEFT JOIN SN_INTRANETIP NW ON NW.INTRANETIP_DEVNUM = A.VIP_VIPNUM AND NW.INTRANETIP_STATUS != 'DEL'
LEFT JOIN SN_INTERNETIP GW ON GW.INTERNETIP_BUSINESSNUM = A.VIP_VIPNUM AND GW.INTERNETIP_STATUS != 'DEL'
WHERE 1=1
AND A.VIP_NLBNUM != ''
AND A.VIP_NLBNUM IS NOT NULL
AND A.VIP_STATUS != 'DEL'
AND B.VIPSVR_SVRNUM IS NOT NULL
SELECT COUNT(*) FROM
(Select ROW_ID, VIP_NLBNUM, VIP_VIPNUM From SN_VIPLOADBALANCE Where A.VIP_NLBNUM != '' AND A.VIP_NLBNUM IS NOT NULL AND A.VIP_STATUS != 'DEL') A
LEFT JOIN (Select VIPSVR_ROWID, VIPSVR_NUM, VIPSVR_SVRNUM From SN_VIPLOADINFO_SERVER Where VIPSVR_SVRNUM IS NOT NULL And VIPSVR_STATUS != 'DEL')B ON B.VIPSVR_ROWID=A.ROW_ID
LEFT JOIN SN_VIPLOADINFO_HGP H ON H.VIPHGP_STATUS != 'DEL' And A.ROW_ID = H.VIPHGP_ROWID AND B.VIPSVR_NUM = H.VIPHGP_NUM
LEFT JOIN SN_HOSTGROUP J ON J.HOSTGROUP_STATUS != 'DEL' And H.VIPHGP_HGPNUM = J.HOSTGROUP_NUM
LEFT JOIN SN_SERVER C ON C.SERVER_STATUS != 'DEL' And C.SERVER_NUM = B.VIPSVR_SVRNUM
LEFT JOIN SN_SERVER_HISTORY I I.OPERATETYPE = 'DEL' And ON I.SVRNUM = B.VIPSVR_SVRNUM
LEFT JOIN SN_SYSTEM K ON K.SYSTEM_STATUS != 'DEL' And I.SYSCNAME = K.SYSTEM_FCNAME
LEFT JOIN SN_RACKDEV RD ON C.SERVER_SVRNUM = RD.RACKDEV_DEVNUM
LEFT JOIN SN_RACK R ON R.RACK_STATUS != 'DEL' And RD.RACKDEV_RACKNUM = R.RACK_NUM
LEFT JOIN SN_LOGICROOM L ON L.LOGICROOM_STATUS != 'DEL' And R.RACK_LOGICROOMNUM = L.LOGICROOM_NUM
LEFT JOIN SN_INTRANETIP E ON E.INTRANETIP_STATUS != 'DEL' And E.INTRANETIP_DEVNUM = A.VIP_NLBNUM
LEFT JOIN SN_INTRANETIP NW ON NW.INTRANETIP_STATUS != 'DEL' And NW.INTRANETIP_DEVNUM = A.VIP_VIPNUM
LEFT JOIN SN_INTERNETIP GW ON GW.INTERNETIP_STATUS != 'DEL' And GW.INTERNETIP_BUSINESSNUM = A.VIP_VIPNUM
3、或者再试试把各个表单独的条件放到表中,作为一个子查询,on 后面只放与连接有关的条件
SELECT COUNT(*)
FROM
(Select ROW_ID, VIP_NLBNUM, VIP_VIPNUM From SN_VIPLOADBALANCE Where VIP_NLBNUM != '' AND VIP_NLBNUM IS NOT NULL AND VIP_STATUS != 'DEL') A
LEFT JOIN (
Select VIPSVR_ROWID, VIPSVR_NUM, VIPSVR_SVRNUM From SN_VIPLOADINFO_SERVER Where B.VIPSVR_STATUS != 'DEL' And VIPSVR_SVRNUM IS NOT NULL
)B ON B.VIPSVR_ROWID = A.ROW_ID
LEFT JOIN (
Select VIPHGP_ROWID, VIPHGP_NUM, VIPHGP_HGPNUM From SN_VIPLOADINFO_HGP Where VIPHGP_STATUS != 'DEL'
)H ON A.ROW_ID = H.VIPHGP_ROWID AND B.VIPSVR_NUM = H.VIPHGP_NUM
LEFT JOIN (
Select HOSTGROUP_NUM From SN_HOSTGROUP Where HOSTGROUP_STATUS != 'DEL'
)J ON H.VIPHGP_HGPNUM = J.HOSTGROUP_NUM
LEFT JOIN (
Select SERVER_NUM From SN_SERVER Where SERVER_STATUS != 'DEL'
) C ON C.SERVER_NUM = B.VIPSVR_SVRNUM
LEFT JOIN (
Select SVRNUM, SYSCNAME From SN_SERVER_HISTORY Where OPERATETYPE = 'DEL'
)I ON I.SVRNUM = B.VIPSVR_SVRNUM
LEFT JOIN (
Select SYSTEM_FCNAME From SN_SYSTEM Where SYSTEM_STATUS != 'DEL'
)K ON I.SYSCNAME = K.SYSTEM_FCNAME
LEFT JOIN SN_RACKDEV RD ON C.SERVER_SVRNUM = RD.RACKDEV_DEVNUM
LEFT JOIN (
Select RACK_NUM, RACK_LOGICROOMNUM From SN_RACK Where RACK_STATUS != 'DEL'
) R ON RD.RACKDEV_RACKNUM = R.RACK_NUM
LEFT JOIN (
Select LOGICROOM_NUM From SN_LOGICROOM Where LOGICROOM_STATUS != 'DEL'
) L ON R.RACK_LOGICROOMNUM = L.LOGICROOM_NUM
LEFT JOIN (
Select INTRANETIP_DEVNUM From SN_INTRANETIP Where INTRANETIP_STATUS != 'DEL'
) E ON E.INTRANETIP_DEVNUM = A.VIP_NLBNUM
LEFT JOIN (
Select INTRANETIP_DEVNUM From SN_INTRANETIP Where INTRANETIP_STATUS != 'DEL'
) NW ON NW.INTRANETIP_DEVNUM = A.VIP_VIPNUM
LEFT JOIN (
Select INTERNETIP_BUSINESSNUM From SN_INTERNETIP Where INTERNETIP_STATUS != 'DEL'
) GW ON GW.INTERNETIP_BUSINESSNUM = A.VIP_VIPNUM