SQL 语法不在列表中？

Question

我正在尝试开发一个 T-SQL 查询来排除另一个表“B”中的所有行。另一个表“B”有 3 列组成其 PK，总共 136 行。所以我想选择表“A”中的所有列减去表“B”中的列。我该怎么做？我认为这个查询不正确，因为我仍然收到重复记录错误：

CREATE TABLE #B (STUDENTID VARCHAR(50), MEASUREDATE SMALLDATETIME, MEASUREID VARCHAR(50))
INSERT #B 
SELECT studentid, measuredate, measureid
  from [J5C_Measures_Sys] 
GROUP BY studentid, measuredate, measureid
  HAVING COUNT(*) > 1

insert into J5C_MasterMeasures (studentid, measuredate, measureid, rit)
select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
  join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_14' 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
 where a.score_14 is not null AND B.MEASURENAME IS NOT NULL
 and (A.studentid NOT IN (SELECT studentid from #B) 
and a.measuredate NOT IN (SELECT measuredate from #B)
and a.measureid NOT IN (SELECT measureid from #B))

Answer 1

使用 NOT EXISTS...NOT IN 不会过滤掉 NULLS

insert into J5C_MasterMeasures (studentid, measuredate, measureid, rit)
select A.studentid, A.measuredate, B.measurename+' ' +B.LabelName, A.score_14
from [J5C_Measures_Sys] A
join [J5C_ListBoxMeasures_Sys] B on A.MeasureID = B.MeasureID 
  join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_14' 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
 where a.score_14 is not null AND B.MEASURENAME IS NOT NULL
 AND NOT EXISTS (select 1 from  #B where #b.studentid = A.studentid
 and a.measuredate = #B.measuredate
 and a.measureid = #B.measureid)
  and not exists (select 1 from J5C_MasterMeasures z 
                  where z.studentid = A.studentid)

只是让你知道，看看 选择一个表中不存在于另一个表中的所有行

基本上有至少有 5 种方法从 onr 表中选择不在另一个表中的所有行

• NOT IN
• NOT EXISTS
• LEFT 和 RIGHT JOIN
• OUTER APLY (2005+)
• EXCEPT (2005+)

Answer 2

以下是使用left join进行差分运算的通用解决方案：

select * from FirstTable
left join SecondTable on FirstTable.ID = SecondTable.ID
where SecondTable.ID is null

当然，您的join on子句可能会更复杂，但基本操作是相同的。

Answer 3

我认为你可以在子查询中使用“NOT IN”，但你说你有一个多字段键？

我正在考虑使用左外连接，然后在右侧测试空值...

马丁。