这个查询需要很长时间才能在sql server manegment studio 2012中执行

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) )

1 个解决方案

#1

You can try this

你可以试试这个

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')