在SQL SERVER中使用子查询排除记录

本文介绍了在SQL SERVER中使用子查询排除记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我不是SQL的专家。我有一个表格，数据以错误的模式输入。我必须排除一些记录（我可以我要删除）我正在尝试创建一个视图。 尝试使用子查询但没有为我工作 这里是我的数据Hi,I am not an expert in SQL.I have a table where data entered in a wrong patter.I have to exclude some records( i can't delete) i am trying to create a view.Tried with sub queries but not worked for mehere is my datastidAyearAtermAwardDetails432014fall432015fall HONOR Honor List432015spring good432016spring good432016spring HONOR Honor List452014fall452015fall PRESIDENT PresidentList452015spring good452016spring good452016spring HONOR Honor List 这是我的数据。我想排除详细信息的行'好的'当同年有荣誉名单和期限 2016年春季学生45我们有良好的荣誉名单 in this我们需要删除rec ord与'good' 并保持荣誉列表 这是表结构this is my data.i want to exclude the row with details 'Good' when there is Honor list in the same year and termfor Student 45 in 2016 spring we have good and honorlistin this case we need to remove the record with 'good'and keep the honorlist as suchThis is the table structureCREATE TABLE [dbo].[testawards]([stid] [int] NULL,[Ayear] [int] NULL,[Aterm] [nchar](10) NULL,[Award] [nvarchar](50) NULL,[Details] [nvarchar](max) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 样本数据Sample datainsert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2014,'fall','','')insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2015,'fall','HONOR','Honor List')insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2015,'spring','','good')insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','','good')insert into testawards (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','HONOR','Honor List')insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2014,'fall','','')insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2015,'fall','PRESIDENT','PresidentList')insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2015,'spring','','good')insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','','good')insert into testawards (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','HONOR','Honor List')What I have tried:I tried with Subqueries but not worked .just took the count of records with same term and year then if there is Honor ,but this is not worked for me推荐答案为了能够排除某些数据，您需要使用 EXSISTS [ ^ ]或除了 [ ^ ]方法。 例如：To be able to exclude some data, you need to use EXSISTS[^] or EXCEPT[^] method.For example:SELECT *FROM @testawardsWHERE Details <> 'good'EXCEPTSELECT *FROM @testawardsWHERE Details = 'good' 另一种方法是使用 JOIN '秒。请参阅 David_Wimbley [ ^ ]。有关 JOIN 如何工作的更多详细信息，请参阅： SQL连接的可视化表示 [ ^ ]Another way is to use JOIN's. See solution 1 by David_Wimbley[^]. For further details about how the JOINs work, see: Visual Representation of SQL Joins[^] binu.emiliya [ ^ ]写道：学生43为学期2015年春天有没有荣誉所以我需要保留该记录 student 43 for term 2015 Spring there is no honor so i need to keep that record 以上回复David_Wimbley [ ^ ]的答案有点混乱G。 我使用pivot来检查：Above reply to David_Wimbley[^]'s answer is bit confusing.I checked that using pivot:SELECT stid, AYear, [spring], [fall]FROM (SELECT stid, AYear, Aterm, DetailsFROM @testawards) AS DTPIVOT (MAX(Details) FOR Aterm IN([spring], [fall])) AS PTORDER BY PT.stid, PT.Ayear 我的结果是and my result isstidAYearspringfall432014NULL432015goodHonor List432016Honor ListNULL452014NULL452015goodPresidentList452016Honor ListNULL 所以，你必须考虑ab那个。So, you have to think about that.如果我理解你的要求，你可以试试这个变种。If I understand your requirement correctly, here's one variation you can try.SELECT *FROM testawards taWHERE ta.Details = 'Good'AND NOT EXISTS ( SELECT 1 FROM testawards ta2 WHERE ta2.stid = ta.stid AND ta2.Ayear = ta.Ayear AND ta2.Aterm = ta.Aterm AND ta2.Award = 'HONOR') 编辑： 如果需要包含'Good'以外的其他记录，请尝试按以下方式更改：If records with something else than 'Good' need to be included, try varying as follows:SELECT *FROM testawards taWHERE ta.Details <> 'Good'OR NOT EXISTS ( SELECT 1 FROM testawards ta2 WHERE ta2.stid = ta.stid AND ta2.Ayear = ta.Ayear AND ta2.Aterm = ta.Aterm AND ta2.Award = 'HONOR')我认为这提供了你想要的东西。另外，感谢您提供架构和示例数据，这些数据让您更轻松。I think this provides what you are looking for. Also, thank you for providing schema and sample data, that made helping you so much easier. SELECT B.* FROM(SELECT stid, ayear, aterm FROM @testawards GROUP BY stid, ayear, aterm HAVING COUNT(*) > 1) AS A RIGHT JOIN @testawards AS B ON B.stid = A.stid AND b.Ayear = A.Ayear AND B.Aterm = A.AtermWHERE B.Details <> 'good' 这篇关于在SQL SERVER中使用子查询排除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！