SQL:返回“真"；如果记录列表存在? [英] SQL: Return "true" if list of records exists?

问题描述

另一个标题可能是:检查是否存在多行?

An alternative title might be: Check for existence of multiple rows?

结合使用 SQL 和 C#，如果列表中的所有产品都存在于表中，我希望方法返回 true.如果它可以在 SQL 中完成，那将是可取的.我编写了一个方法，使用以下 SQL 返回单个 productID 是否存在:

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'))

结果如何表达对我来说并不重要.查询返回 1 还是 0、空结果集或非空结果集、true 或 false 都无关紧要.我更喜欢 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 连接起来.显然，这会将代码打开到 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 不是唯一的，那么它就是

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

<小时>

当问题被认为需要在所有 ProductIds 都存在且不存在时返回行时:

---

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:返回“真"；如果记录列表存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！