SELECT *
FROM (
SELECT
A.* --,B.DELIVERY_NO--,CASE WHEN ISNULL(B.DELIVERY_NO,'') = '' THEN 'N'ELSE 'Y' END IS_STS
FROM
(
SELECT * FROM ASC_HU WITH(NOLOCK) WHERE RCV_DT BETWEEN '20100315' AND '20100315'
) A LEFT JOIN
(
SELECT * FROM STOCK_HIS WITH(NOLOCK) WHERE DT BETWEEN '20100315' AND '20100315' AND STOCK_FLAG IN ('01','20','29')
AND AMOUNT IN ('0.00','8.00')
) B
ON A.HU_NUMBER = B.DELIVERY_NO
AND A.MATERIAL = B.MATERIAL_ID
WHERE ISNULL(B.DELIVERY_NO,'') = ''
) C WHERE C.IS_STS = 'N'有再从个表,一个是 ASC_HU 另一个是STOCK_HIS
从中各取出3月15号的数据,然后查询出 ASC_HU中有,但STOCK_HIS中没有的数据。
所以我想到了用left join,可是如果 不带B.DELIVERY_NO和不显示B.DELIVERY_NO就查询的很快
一但显示和把B.DELIVERY_NO作为条件就很慢,真的很奇怪。下面的是很快的SELECT
A.* --,B.DELIVERY_NO--,CASE WHEN ISNULL(B.DELIVERY_NO,'') = '' THEN 'N'ELSE 'Y' END IS_STS
FROM
(
SELECT * FROM ASC_HU WITH(NOLOCK) WHERE RCV_DT BETWEEN '20100315' AND '20100315'
) A LEFT JOIN
(
SELECT * FROM STOCK_HIS WITH(NOLOCK) WHERE DT BETWEEN '20100315' AND '20100315' AND STOCK_FLAG IN ('01','20','29')
AND AMOUNT IN ('0.00','8.00')
) B
ON A.HU_NUMBER = B.DELIVERY_NO
AND A.MATERIAL = B.MATERIAL_ID
FROM (
SELECT
A.* --,B.DELIVERY_NO--,CASE WHEN ISNULL(B.DELIVERY_NO,'') = '' THEN 'N'ELSE 'Y' END IS_STS
FROM
(
SELECT * FROM ASC_HU WITH(NOLOCK) WHERE RCV_DT BETWEEN '20100315' AND '20100315'
) A LEFT JOIN
(
SELECT * FROM STOCK_HIS WITH(NOLOCK) WHERE DT BETWEEN '20100315' AND '20100315' AND STOCK_FLAG IN ('01','20','29')
AND AMOUNT IN ('0.00','8.00')
) B
ON A.HU_NUMBER = B.DELIVERY_NO
AND A.MATERIAL = B.MATERIAL_ID
WHERE ISNULL(B.DELIVERY_NO,'') = ''
) C WHERE C.IS_STS = 'N'--为什么不把这个条件写在里面
毕竟是用到了函数,你可以对B.DELIVERY_NO加索引
什么意思?
不可以吗?