如何在SQL中进行减号运算后获得前1个元素 [英] how to get top 1 element after MINUS operation in SQL

问题描述

我有一个数据库.在SQL中进行减号运算后，我得到了一定的结果，但是我无法从中获得前1个元素.任何人都可以帮忙....

这是我的查询

i have a database. after MINUS operation in SQL i get certain result but i am unable to get top 1 element from it. can anyone pls help....

here is my query

select top 1 Book_Stock.StockId from Book_Stock,Issue_Book where  Book_Stock.BookId=''99''
except
select Book_Stock.StockId from Book_Stock,Issue_Book  where Book_Stock.StockId = Issue_Book.StockId

该查询将获取不在库存和发行书籍中的stockid.

this query gets stockid which is not in bookstock and issue book.

result is like<br />
-----------<br />
stock id<br />
173<br />
174<br />
176<br />
177<br />
186<br />
187

现在如何在上述单个查询中从上述结果中获取前1个stockid?

now how can i get the top 1 stockid from the above result, in the single above query?

推荐答案

在这里很难为您提供帮助，而又看不到您的实际查询，以及您正在处理的两个数据集的相关子集.
但是，最常见的错误是以错误的方式获得两个语句的顺序:由于MINUS仅返回第一个集合中的记录，而不是第二个集合中的记录(忽略所有第二个记录中的记录，而不是第二个记录中的记录第一个)如果您交换订单，则很可能会返回一个空的数据集.

首先，首先要将第一条语句运行到DataTable中，然后将第二条语句运行到另一个语句中.比较两个表(如有必要，将它们转储到DataGridViews中或使用DataTable.WriteXML方法让您使用文本编辑器或类似工具脱机分析它们)

如果看起来在MINUS之后应该有记录，我们需要查看查询和相关的数据子集.

OP添加了SQL语句:

It is difficult to help you here, without seeing your actual query, and a relevant subset of the two data sets you are operating on.
However, the most common mistake is to get the order of the two statements the wrong way round: Since MINUS returns only those records which ARE in the first set and NOT in the second, (ignoring all those that ARE in the second and NOT in the first) if you swap the order, it is likely to return an empty dataset.

First things first: run your first statement alone into a DataTable, then your second statement alone into another. Compare the two tables (if necessary, dump them into DataGridViews or use DataTable.WriteXML method to let you analyse them offline with a text editor or similar)

If it looks like there should be records after the MINUS, we need to see the queries and a relevant data subset.

OP Added SQL statement:

select top 1 Book_Stock.StockId from Book_Stock,Issue_Book where Book_Stock.BookId=''99''
except
select Book_Stock.StockId from Book_Stock,Issue_Book where Book_Stock.StockId = Issue_Book.StockId

那将行不通-第一个SELECT只会返回表中的第一行，然后根据排除列表进行检查.如果要返回组合排除的最高结果，则需要嵌套查询:

That won''t work - the first SELECT will only return the top row in the table, which is then checked against the exclusion list. If you want to return the top result of the combined exclusion, you need to nest the queries:

SELECT TOP 1 * FROM myTable WHERE iD IN 
  (
   SELECT iD FROM myTable WHERE iD > 5 
   EXCEPT
   SELECT iD FROM myTable WHERE iD < 10
  )

您必须根据实际需要微调它，但这就是原理.

You will have to jiggle it to match what you want to do exactly, but that''s the principle.

尝试一下.

Try this.

Select Top 1 Book_Stock.StockId from (select Book_Stock.StockId from Book_Stock,Issue_Book where  Book_Stock.BookId=''99''
except
select Book_Stock.StockId from Book_Stock,Issue_Book  where Book_Stock.StockId = Issue_Book.StockId) AS A

这篇关于如何在SQL中进行减号运算后获得前1个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！