NOT IN SQL 语句如下:
当存储过程执行not in 大量数据的时候,响应需要1分钟以上!
SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082'
AND partno NOT IN
( SELECT partno FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142')
/*******************************************简化表参数易学易懂!
SELECT *FROM table (NOLOCK) WHERE id='63514-16120082'
AND data NOT IN
( SELECT id FROM table (NOLOCK) WHERE id='63514-16120142')
*******************************************/
LEFT JOIN SQL 语句如下:
速度大大提升,用 NOT IN 响应42秒的语句而用LEFT JOIN只需要用4秒
SELECT * FROM
(SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082')AS a
LEFT JOIN
( SELECT * FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142')AS b
ON a.partno=b.partno
where b.partno IS NULL
/*******************************************简化表参数易学易懂!
SELECT * FROM
(SELECT *FROM table (NOLOCK) WHERE id='63514-16120082')AS a
LEFT JOIN
( SELECT * FROM table (NOLOCK) WHERE id='63514-16120142')AS b
ON a.data=b.data
where b.data IS NULL
*******************************************/
NOT EXISTS SQL 语句如下:
速度超级大大提升,用 NOT IN 响应42秒的语句而用LEFT JOIN只需要用1-2秒
SELECT * FROM
(SELECT *FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120082') AS a
where NOT EXISTS (
SELECT *FROM
( SELECT partno FROM mfworkdetail (NOLOCK) WHERE workorderno='63514-16120142') AS b WHERE a.partno=b.partno)
/*******************************************简化表参数易学易懂!
SELECT * FROM
(SELECT *FROM table (NOLOCK) WHERE id='63514-16120082') AS a
where NOT EXISTS (
SELECT *FROM
( SELECT data FROM table (NOLOCK) WHERE id='63514-16120142') AS b WHERE a.data=b.data)
*******************************************/