SELECT dbo.Map_UnitType.typename AS CompanyTypeName,
UnitType_1.typename AS CompanyParentTypeName, dbo.Map_UnitOwner.OwnerID,
dbo.Map_UnitOwner.UnitID, dbo.Map_UnitOwner.OwnerName,
dbo.Map_UnitOwner.OwnerType, dbo.Map_UnitOwner.address AS UnitOwnerAddress,
dbo.Map_UnitOwner.introduction AS UnitOwnerIntroduction,
dbo.yellow_CompanyInfo.CompanyName,
dbo.yellow_CompanyInfo.introduction AS CompanyIntroduction,
dbo.yellow_CompanyInfo.trafficinfo AS CompanyTrafficInfo,
dbo.yellow_CompanyInfo.keyword AS CompaynKeyWord,
dbo.yellow_CompanyInfo.CompanyID,
dbo.yellow_CompanyInfo.address AS CompanyAddress,
dbo.yellow_CompanyInfo.telephone AS CompanyTelephone,
dbo.yellow_CompanyInfo.isActive AS CompanyIsActive,
dbo.yellow_CompanyInfo.firstlevelcode AS CompanyType,
dbo.Map_UnitOwner.isActive AS UnitOwnerIsActive,
dbo.yellow_CompanyInfo.CompanyTypeID1, dbo.Map_CityUnit.parentUnitID,
dbo.Map_UnitType.typeid AS CompanyTypeID,
UnitType_1.typeid AS CompanyParentTypeID,
dbo.yellow_CompanyInfo.keywords AS CompanyKeyWords,
dbo.Map_UnitOwner.keywords AS UnitOwnerKeyWords,
dbo.yellow_CompanyInfo.ECode
FROM dbo.Map_UnitOwner LEFT OUTER JOIN
dbo.Map_CityUnit ON
dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
dbo.yellow_CompanyInfo LEFT OUTER JOIN
dbo.Map_UnitType UnitType_1 ON
RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
= UnitType_1.typeid LEFT OUTER JOIN
dbo.Map_UnitType ON RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
= dbo.Map_UnitType.typeid ON
dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND
(dbo.yellow_CompanyInfo.modifytype != 3 OR
dbo.yellow_CompanyInfo.modifytype = NULL) AND
(dbo.Map_UnitOwner.modifytype != 3 OR
dbo.Map_UnitOwner.modifytype = NULL))
UNION
(SELECT NULL AS CompanyTypeName,
UnitType_1.typename AS CompanyParentTypeName, dbo.Map_UnitOwner.OwnerID,
dbo.Map_UnitOwner.UnitID, dbo.Map_UnitOwner.OwnerName,
dbo.Map_UnitOwner.OwnerType, dbo.Map_UnitOwner.address AS UnitOwnerAddress,
dbo.Map_UnitOwner.introduction AS UnitOwnerIntroduction, NULL
AS CompanyName, NULL AS CompanyIntroduction, NULL
AS CompanyTrafficInfo, NULL AS CompaynKeyWord, NULL AS CompanyID, NULL
AS CompanyAddress, NULL AS CompanyTelephone, NULL
AS CompanyIsActive, NULL AS CompanyType,
dbo.Map_UnitOwner.isActive AS UnitOwnerIsActive, NULL AS CompanyTypeID1,
dbo.Map_CityUnit.parentUnitID, dbo.Map_UnitType.typeid AS CompanyTypeID,
UnitType_1.typeid AS CompanyParentTypeID, NULL AS CompanyKeyWords,
dbo.Map_UnitOwner.keywords AS UnitOwnerKeyWords, NULL AS ECode
FROM dbo.Map_UnitOwner LEFT OUTER JOIN
dbo.Map_CityUnit ON
dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
dbo.yellow_CompanyInfo LEFT OUTER JOIN
dbo.Map_UnitType UnitType_1 ON
RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
= UnitType_1.typeid LEFT OUTER JOIN
dbo.Map_UnitType ON RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
= dbo.Map_UnitType.typeid ON
dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND (dbo.Map_UnitOwner.modifytype != 3 OR
dbo.Map_UnitOwner.modifytype = NULL) AND
(dbo.Map_UnitOwner.OwnerID NOT IN
(SELECT dbo.Map_UnitOwner.OwnerID
FROM dbo.Map_UnitOwner LEFT OUTER JOIN
dbo.Map_CityUnit ON
dbo.Map_UnitOwner.UnitID = dbo.Map_CityUnit.UnitID FULL OUTER JOIN
dbo.yellow_CompanyInfo LEFT OUTER JOIN
dbo.Map_UnitType UnitType_1 ON
RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 4), 3)
= UnitType_1.typeid LEFT OUTER JOIN
dbo.Map_UnitType ON
RIGHT(LEFT(dbo.yellow_CompanyInfo.CompanyTypeID1, 8), 3)
= dbo.Map_UnitType.typeid ON
dbo.Map_UnitOwner.OwnerID = dbo.yellow_CompanyInfo.OwnerID
WHERE (dbo.Map_UnitOwner.OwnerID > 0) AND
(dbo.yellow_CompanyInfo.modifytype != 3 OR
dbo.yellow_CompanyInfo.modifytype = NULL) AND
(dbo.Map_UnitOwner.modifytype != 3 OR
dbo.Map_UnitOwner.modifytype = NULL))))
sqlServer2000 视图
最新推荐文章于 2024-03-20 14:59:36 发布