T-SQL 2008 R2，其中有几个没有项目的语句

Question

在T-SQL 2008 R2中，我试图确定如何设置SQL以完成以下内容目标：

SELECT table1.customer_id, 
       type, 
       start_date, 
       end_date, 
       program_id 
FROM   table1 
       JOIN table2 
         ON table1.customer_id = table2.customer_id 

1. type not =（'aa'，'cc'）和type not ='g2'其中code = 3在表1中，每个custerution_id有很多记录类型的许多各种值。我只想要不包含的customer_ids上面列出的值。和
2. table2只有一个custeruty_id。Customer_ID是Table2的关键。我希望在3列之一中没有价值的客户：start_date，end_date和program_id。

上面列出的第1部分和第2部分都必须正确，才能选择customer_id。因此，您能告诉我如何设置该SQL吗？

Answer 1

我无法确切告诉你，因为我看不到您的数据库结构，但我相信这是这样的：

   SELECT table1.customer_id,type,start_date,end_date,Program_id
    FROM table1 JOIN table2 ON table1.customer_id = table2.customer_id
    WHERE (table1.type NOT IN('aa', 'cc', 'g2') AND table1.code = 3)
    AND (table2.start_data IS NULL OR table2.end_date IS NULL OR table2.program_id IS NULL)

Answer 2

尝试使用内部加入：

SELECT table1.customer_id,type,start_date,end_date,Program_id
  FROM table1 
INNER JOIN table2 ON table1.customer_id = table2.customer_id
  WHERE table1.type != 'aa' 
    AND  table1.type != 'cc' 
    AND table1.type != 'g2'
    AND table1.code = 3
   AND (table2.start_data IS NULL OR table2.end_date IS NULL OR table2.program_id IS NULL)

让我知道它是否正常！

regrads，Andoura

Answer 3

将支票交给Jason（ 1），但我会将条件拉入联接
有时，这有助于查询优化器

SELECT table1.customer_id,type,start_date,end_date,Program_id
  FROM table1 
  JOIN table2 
    ON table1.customer_id = table2.customer_id
   AND table1.type NOT IN ('aa', 'cc') 
   AND (table1.code = 3 and table1.type <> 'g2')
   AND (table2.start_data IS NULL OR table2.end_date IS NULL OR table2.program_id IS NULL)

我明白这是您从字面上要求的，但这不是您认为的

AND table1.type NOT IN ('aa', 'cc') 
AND (table1.code = 3 and table1.type <> 'g2')

与

相同

AND table1.type NOT IN ('aa', 'cc')
AND table1.type <> 'g2' 
AND table1.code = 3 

与

相同

AND table1.type NOT IN ('aa', 'cc', 'g2')
AND table1.code = 3

我认为您要要求的是

AND (    table1.type NOT IN ('aa', 'cc') 
      or (table1.code = 3 and table1.type <> 'g2') 
    )