在SQL SERVER中使用子查询排除记录 [英] Exclude records using a subquery in SQL SERVER

问题描述

我不是SQL的专家。我有一个表格，数据以错误的模式输入。我必须排除一些记录（我可以我要删除）我正在尝试创建一个视图。

尝试使用子查询但没有为我工作



这里是我的数据< br $>

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 me

here is my data

stid	Ayear	Aterm	Award	Details
43	2014	fall      		
43	2015	fall      HONOR	   Honor List
43	2015	spring    	      good
43	2016	spring    	      good
43	2016	spring    HONOR	   Honor List
45	2014	fall      		
45	2015	fall      PRESIDENT PresidentList
45	2015	spring    	      good
45	2016	spring    	      good
45	2016	spring   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 term

for Student 45 in 2016 spring we have good and honorlist
in this case we need to remove the record with 'good'
and keep the honorlist as such



This is the table structure

CREATE 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 data

insert 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 @testawards
WHERE Details <> 'good'
EXCEPT
SELECT *
FROM @testawards 
WHERE 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, Details 
	FROM @testawards
	) AS DT
PIVOT (MAX(Details) FOR Aterm IN([spring], [fall])) AS PT
ORDER BY PT.stid, PT.Ayear 

我的结果是

and my result is

stid	AYear	spring		fall
43		2014	NULL	
43		2015	good		Honor List
43		2016	Honor List	NULL
45		2014	NULL	
45		2015	good		PresidentList
45		2016	Honor List	NULL

所以，你必须考虑ab那个。

So, you have to think about that.

如果我理解你的要求，你可以试试这个变种。

If I understand your requirement correctly, here's one variation you can try.

SELECT *
FROM  testawards ta
WHERE 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 ta
WHERE 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.Aterm
WHERE B.Details <> 'good'

这篇关于在SQL SERVER中使用子查询排除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！