此查询需要很长时间才能在 SQL Server Management Studio 2012 中执行

Question

SELECT s.option_code,s.seasonal_template_id,s.T900,s.T901,s.creator_id,s.creator_date,s.revisor_id,s.revisor_date,s.costing_sr_no,s.[T902],s.[T903]
FROM   [dbo].[tna870_seasonal_bulk_option_code_details_vendor_temp] s
INNER JOIN mst703_master_field_values S5 
ON S5.field_value_id = s.T900
AND S5.field_id = (select s1.field_id from mst703_master_field s1 
where s1.field_code='t900')
WHERE s.seasonal_template_id = 35
AND ( s.option_code+CAST(s.seasonal_template_id AS VARCHAR(20))+s.costing_sr_no 
NOT IN (SELECT DISTINCT s2.option_code+CAST(s2.seasonal_template_id AS VARCHAR(20))+s2.costing_sr_no 
FROM tna870_seasonal_bulk_option_code_details_vendor s2
WHERE s2.seasonal_template_id = 35) )

Answer 1

你可以试试这个

SELECT
s.option_code,
s.seasonal_template_id,
s.T900,
s.T901,
s.creator_id,
s.creator_date,
s.revisor_id,
s.revisor_date,
s.costing_sr_no,
s.[T902],
s.[T903]
FROM
(
SELECT
*
FROM [dbo].[tna870_seasonal_bulk_option_code_details_vendor_temp] s
WHERE s.seasonal_template_id = 35
AND NOT EXISTS(
SELECT 1 
FROM  tna870_seasonal_bulk_option_code_details_vendor s2 
WHERE s2.seasonal_template_id = s.seasonal_template_id
AND s2.option_code+CAST(s2.seasonal_template_id AS VARCHAR(20))+s2.costing_sr_no = s.option_code+CAST(s.seasonal_template_id AS VARCHAR(20))+s.costing_sr_no
)
) s
INNER JOIN mst703_master_field_values S5 
ON S5.field_value_id = s.T900
AND S5.field_id = (select s1.field_id from mst703_master_field s1 
where s1.field_code='t900')