SQL：返回＆QUOT;真＆QUOT;如果不存在的记录列表？ [英] SQL: Return "true" if list of records exists?
问题描述
这是另一种标题可能是：
检查多行的存在？
An alternative title might be: Check for existence of multiple rows?
使用的SQL和C＃我想一个方法，如果在一个表中存在的所有产品中的列表返回true的组合。如果都可以在SQL来完成，这将是preferable。我写了一个返回方法是否一个的productID 存在使用的SQL语句：
Using a combination of SQL and C# I want a method to return true if all products in a list exist in a table. If it can be done all in SQL that would be preferable. I have written a method that returns whether a single productID exists using the following SQL:
SELECT productID FROM Products WHERE ProductID = @productID
如果这个返回一行，那么C＃方法返回true，否则为false。
If this returns a row, then the c# method returns true, false otherwise.
现在我想知道如果我有产品ID列表（不是一个巨大的列表提醒你，通常在20）。我怎么能写一个查询，如果存在的所有产品的ID，将返回一行，并没有行，如果一个或多个产品的ID不存在？
Now I'm wondering if I have a list of product IDs (not a huge list mind you, normally under 20). How can I write a query that will return a row if all the product id's exist and no row if one or more product id's does not exist?
(Maybe something involving "IN" like:
SELECT * FROM Products WHERE ProductID IN ('1', '10', '100', 'ABC'))
编辑：
结果如何是前pressed对我来说并不重要。查询是否返回 1 或 0 ，一个空结果还是非空单，真的还是假的没有按' Ť问题。我想preFER是1）易于阅读和理解，答案2）高性能
How the result is expressed is not important to me. Whether the query returns a 1 or 0, an empty resultset or a non-empty one, true or false doesn't matter. I'd prefer the answer that is 1) easy to read and understand and 2) performant
我预想串联产品的ID与SQL名单。显然，这将打开code到SQL注入（产品ID的实际上是 VARCHAR 。在这种情况下，机会渺茫，但仍希望避免这种可能性）。因此，如果有办法解决，这将是更好的。使用SQL Server 2005。
I was envisioning concatenating the list of product id's with the SQL. Obviously this opens the code up to SQL injection (the product id's are actually varchar. in this case the chance is slim but still want to avoid that possibility). So if there is a way around this that would be better. Using SQL Server 2005.
产品ID是 VARCHAR
推荐答案
由于更新的问题，这是最简单的形式：
Given your updated question, these are the simplest forms:
如果的ProductID 是独一无二的你想要
If ProductID is unique you want
SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)
然后核对 3 的结果，要查询（这最后一部分可以在SQL中完成产品的数量，但它可能是更容易做到这一点在C＃中，除非你正在做的SQL甚至更多）。
and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).
如果的ProductID 不是唯一的，是
SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)
当这个问题被认为需要返回行，当所有 ProductIds 是present则为none：
When the question was thought to require returning rows when all ProductIds are present and none otherwise:
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)
或
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)
如果你真的打算做的结果的东西。否则，简单的 SELECT 1 WHERE（SELECT ...）= 3 会做其他的答案都表示或暗示的保证。
if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.
这篇关于SQL：返回＆QUOT;真＆QUOT;如果不存在的记录列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
