我想知道在下面的示例中是否有更好的方法来创建我的左联接:
SELECT TOP 10 COALESCE(A.COD_PRODUCT, B.COD_PRODUCT),
COALESCE(A.COD_FAMILY, B.COD_FAMILY),
COALESCE(A.DATE_EXTRACT, B.DATE_EXTRACT),
A.MASS
B.VOLUME
C.PRICE
FROM FIRSTTABLE A FULL JOIN SECONDTABLE B ON B.COD_PRODUCT = A.COD_PRODUCT
AND B.COD_FAMILY = A.COD_FAMILY
AND B.DATE_EXTRACT = A.DATE_EXTRACT
LEFT JOIN THIRDTABLE C ON C.COD_PRODUCT = COALESCE(A.COD_PRODUCT,B.COD_PRODUCT)
AND C.COD_FAMILY = COALESCE(A.COD_FAMILY, B.COD_FAMILY)
AND C.DATE_EXTRACT = COALESCE(A.DATE_EXTRACT, B.DATE_EXTRACT)
这种接合需要很长时间,我怀疑它是昂贵且可改善的编辑:我想在 View 中改进此
SELECT FROM JOIN
语句。 最佳答案
您可以将查询分为两部分:收集所有与FIRSTTABLE匹配的数据。然后将其与FIRSTTABLE中没有的所有匹配SECONDTABLE的数据合并。
这样可以使SQL Server更好地使用这些表上的索引。
SELECT A.COD_PRODUCT,
A.COD_FAMILY,
A.DATE_EXTRACT,
A.MASS,
B.VOLUME,
C.PRICE
FROM FIRSTTABLE A
LEFT OUTER JOIN SECONDTABLE B
ON B.COD_PRODUCT = A.COD_PRODUCT
AND B.COD_FAMILY = A.COD_FAMILY
AND B.DATE_EXTRACT = A.DATE_EXTRACT
LEFT OUTER JOIN THIRDTABLE C
ON C.COD_PRODUCT = A.COD_PRODUCT
AND C.COD_FAMILY = A.COD_FAMILY
AND C.DATE_EXTRACT = A.DATE_EXTRACT
UNION ALL
SELECT B.COD_PRODUCT,
B.COD_FAMILY,
B.DATE_EXTRACT,
NULL AS MASS,
B.VOLUME,
C.PRICE
FROM SECONDTABLE B
LEFT OUTER JOIN THIRDTABLE C
ON C.COD_PRODUCT = B.COD_PRODUCT
AND C.COD_FAMILY = B.COD_FAMILY
AND C.DATE_EXTRACT = B.DATE_EXTRACT
WHERE NOT EXISTS (SELECT 1
FROM FIRSTTABLE A
WHERE A.COD_PRODUCT = B.COD_PRODUCT
AND A.COD_FAMILY = B.COD_FAMILY
AND A.DATE_EXTRACT = B.DATE_EXTRACT)