当前位置： 文江博客话题详情

SQL：返回“true”记录列表是否存在？

发布于 2024-09-02 12:41:56 字数 857 浏览 6 评论 0原文

另一个标题可能是： 检查是否存在多行？

使用 SQL 和 C# 的组合，如果列表中的所有产品都存在于表中，我希望有一个方法返回 true。如果能全部用 SQL 来完成那就更好了。我编写了一个方法，使用以下 SQL 返回单个 productID 是否存在：

SELECT productID FROM Products WHERE ProductID = @productID

如果返回一行，则 c# 方法返回 true，否则返回 false。

现在我想知道我是否有一个产品 ID 列表（请注意，列表不是很大，通常在 20 个以下）。如何编写一个查询，如果所有产品 id 都存在，则返回一行，如果一个或多个产品 id 不存在，则不返回任何行？

(Maybe something involving "IN" like:
SELECT * FROM Products WHERE ProductID IN ('1', '10', '100', 'ABC'))

编辑：

如何表达结果对我来说并不重要。无论查询返回 1 还是 0、空结果集还是非空结果集、true 或 false，都无关紧要。我更喜欢这样的答案：1）易于阅读和理解，2）高性能

我设想将产品 id 列表与 SQL 连接起来。显然，这使代码容易遭受 SQL 注入（产品 ID 实际上是 varchar。在这种情况下，机会很小，但仍然希望避免这种可能性）。所以如果有办法解决这个问题那就更好了。使用 SQL Server 2005。

产品 ID 为 varchar

原文

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

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

If this returns a row, then the c# method returns true, false otherwise.

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

EDIT:

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

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.

Product ID's are varchar

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

落花浅忆 2024-09-09 12:41:56

我通常是这样做的：

只需用此语句替换您的查询SELECT * FROM table WHERE 1

SELECT
    CASE WHEN EXISTS 
    (
        SELECT * FROM table WHERE 1
    )
    THEN 'TRUE'
    ELSE 'FALSE'
END

Here's how I usually do it:

Just replace your query with this statement SELECT * FROM table WHERE 1

SELECT
    CASE WHEN EXISTS 
    (
        SELECT * FROM table WHERE 1
    )
    THEN 'TRUE'
    ELSE 'FALSE'
END
回复 原文
眼角的笑意。 2024-09-09 12:41:56

鉴于您更新的问题，这些是最简单的形式：

如果 ProductID 是您想要的唯一的

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

，然后根据 3 检查该结果，您正在查询的产品数量（最后一个）部分可以在 SQL 中完成，但在 C# 中可能更容易完成，除非您在 SQL 中完成更多操作）。

如果 ProductID 不是唯一的，则

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

当问题被认为需要在所有 ProductIds 都存在且没有其他情况时返回行时：

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 将按照其他答案所述或暗示的那样进行。

Given your updated question, these are the simplest forms:

If ProductID is unique you want

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

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

If ProductID is not unique it is

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

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)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=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.

回复 原文
暖阳 2024-09-09 12:41:56

@Mark Hurd，感谢您指出错误。

这会起作用（如果您使用的是 Postgresql、Sql Server 2008）：

create table products
(
product_id int not null
);



insert into products values(1),(2),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM (values(1),(10),(100)) as x(id) 
             WHERE x.id NOT IN (select product_id from products))
        THEN 0 --'NOT ALL'

        ELSE 1 -- 'ALL'
    END

如果您使用的是 MySQL，请创建一个临时内存表（然后在那里填充 1,10,100）：

create table product_memory(product_id int) engine=MEMORY;

insert into product_memory values(1),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products))
        THEN 0 -- 'NOT ALL'

        ELSE 1 -- 'ALL'
    END

在您的 C# 代码上：

bool isAllExist = (int)(new SqlCommand(queryHere).ExecuteScalar()) == 1;

[编辑]

我怎样才能编写一个查询
如果所有产品 id 都返回一行
如果存在一个或多个则不存在行
产品 ID 不存在？

关于，如果所有行都存在，则返回一行（单数），如果一个或多个产品 id 不存在，则不返回行：

MySql：

SELECT 1
WHERE 
    NOT EXISTS(
        SELECT 1
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products) )

Posgresql，Sql Server 2008：

SELECT 1
WHERE 
    NOT EXISTS(            
        SELECT 1 
        FROM (values(1),(10),(100)) as x(id) 
        WHERE x.id NOT IN (select product_id from products) )

然后在您的 C# 代码上：

var da = new SqlDataAdapter(queryhere, connectionhere);
var dt = new DataTable();
da.Fill(dt);

if (dt.Rows.Count > 0) 
    return true; 
else 
    return false;

或者只是缩短条件：

return dt.Rows.Count > 0;

@Mark Hurd, thanks for pointing out the error.

this will work (if you are using Postgresql, Sql Server 2008):

create table products
(
product_id int not null
);



insert into products values(1),(2),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM (values(1),(10),(100)) as x(id) 
             WHERE x.id NOT IN (select product_id from products))
        THEN 0 --'NOT ALL'

        ELSE 1 -- 'ALL'
    END

If you are using MySQL, make a temporary memory table(then populate 1,10,100 there):

create table product_memory(product_id int) engine=MEMORY;

insert into product_memory values(1),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products))
        THEN 0 -- 'NOT ALL'

        ELSE 1 -- 'ALL'
    END

On your C# code:

bool isAllExist = (int)(new SqlCommand(queryHere).ExecuteScalar()) == 1;

[EDIT]

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?

Regarding, returning a row(singular) if all rows exists, and no row to be returned if one or more product id does not exists:

MySql:

SELECT 1
WHERE 
    NOT EXISTS(
        SELECT 1
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products) )

Posgresql, Sql Server 2008:

SELECT 1
WHERE 
    NOT EXISTS(            
        SELECT 1 
        FROM (values(1),(10),(100)) as x(id) 
        WHERE x.id NOT IN (select product_id from products) )

Then on your C# code:

var da = new SqlDataAdapter(queryhere, connectionhere);
var dt = new DataTable();
da.Fill(dt);

if (dt.Rows.Count > 0) 
    return true; 
else 
    return false;

Or just make the condition shorter:

return dt.Rows.Count > 0;
回复 原文
坚持沉默 2024-09-09 12:41:56

您可以使用 SELECT CASE 语句，例如so：

select case when EXISTS (
 select 1 
 from <table>
 where <condition>
 ) then TRUE else FALSE end

当父级中的查询存在时，它返回TRUE。

You can use a SELECT CASE statement like so:

select case when EXISTS (
 select 1 
 from <table>
 where <condition>
 ) then TRUE else FALSE end

It returns TRUE when your query in the parents exists.

回复 原文
清旖 2024-09-09 12:41:56

假设您使用的是 SQL Server，布尔类型不存在，但位类型存在，它只能保存 0 或 1，其中 0 代表 False，1 代表 True。

我会这样：

select 1
    from Products
    where ProductId IN (1, 10, 100)

这里，将返回空行或无行（如果不存在行）。

甚至：

select case when EXISTS (
    select 1
        from Products
        where ProductId IN (1, 10, 100)
    ) then 1 else 0 end as [ProductExists]

这里，将始终返回标量值 1 或 0 （如果不存在行）。

Assuming you're using SQL Server, the boolean type doesn't exist, but the bit type does, which can hold only 0 or 1 where 0 represents False, and 1 represents True.

I would go this way:

select 1
    from Products
    where ProductId IN (1, 10, 100)

Here, a null or no row will be returned (if no row exists).

Or even:

select case when EXISTS (
    select 1
        from Products
        where ProductId IN (1, 10, 100)
    ) then 1 else 0 end as [ProductExists]

Here, either of the scalar values 1 or 0 will always be returned (if no row exists).

回复 原文
爱的故事 2024-09-09 12:41:56
DECLARE @values TABLE (ProductId int)
INSERT @values (1)
INSERT @values (10)
INSERT @values (100)

SELECT CASE WHEN (SELECT COUNT(*) FROM @values v) = 
                 (SELECT COUNT(*) FROM Products p WHERE p.ProductId IN
                       (SELECT v.ProductId FROM @values v))
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END [AreAllFound]

DECLARE @values TABLE (ProductId int)
INSERT @values (1)
INSERT @values (10)
INSERT @values (100)

SELECT CASE WHEN (SELECT COUNT(*) FROM @values v) = 
                 (SELECT COUNT(*) FROM Products p WHERE p.ProductId IN
                       (SELECT v.ProductId FROM @values v))
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END [AreAllFound]
回复 原文
眼泪也成诗 2024-09-09 12:41:56

您试图确定其存在的产品列表在哪里？如果该列表存在于另一个表中，您可以执行此

declare @are_equal bit
declare @products int

SELECT @products = 
     count(pl.id)
FROM ProductList pl
JOIN Products p
ON pl.productId = p.productId

select @are_equal = @products == select count(id) from ProductList

编辑：

然后在 C# 中完成所有工作。将实际的产品列表缓存在应用程序中的某处，并执行 LINQ 查询。

var compareProducts = new List<Product>(){p1,p2,p3,p4,p5};
var found = From p in GetAllProducts()
            Join cp in compareProducts on cp.Id equals p.Id
            select p;

return compareProducts.Count == found.Count;

这可以防止手动构建 SQL 查询，并将所有应用程序逻辑保留在应用程序中。

Where is this list of products that you're trying to determine the existence of? If that list exists within another table you could do this

declare @are_equal bit
declare @products int

SELECT @products = 
     count(pl.id)
FROM ProductList pl
JOIN Products p
ON pl.productId = p.productId

select @are_equal = @products == select count(id) from ProductList

Edit:

Then do ALL the work in C#. Cache the actual list of products in your application somewhere, and do a LINQ query.

var compareProducts = new List<Product>(){p1,p2,p3,p4,p5};
var found = From p in GetAllProducts()
            Join cp in compareProducts on cp.Id equals p.Id
            select p;

return compareProducts.Count == found.Count;

This prevents constructing SQL queries by hand, and keeps all your application logic in the application.

回复 原文
调妓 2024-09-09 12:41:56

我知道这已经很旧了，但我认为这会对其他来找的人有所帮助...

SELECT CAST(COUNT(ProductID) AS bit) AS [EXISTS] FROM Products WHERE(ProductID = @ProductID)

如果存在，则始终返回 TRUE，如果不存在，则始终返回 FALSE（而不是没有行）。

I know this is old but I think this will help anyone else who comes looking...

SELECT CAST(COUNT(ProductID) AS bit) AS [EXISTS] FROM Products WHERE(ProductID = @ProductID)

This will ALWAYS return TRUE if exists and FALSE if it doesn't (as opposed to no row).

回复 原文
冷血 2024-09-09 12:41:56

对于 PostgreSQL：

SELECT COUNT(*) = 1 FROM (
  SELECT 1 FROM $table WHERE $condition LIMIT 1
) AS t

For PostgreSQL:

SELECT COUNT(*) = 1 FROM (
  SELECT 1 FROM $table WHERE $condition LIMIT 1
) AS t
回复 原文
喜爱纠缠 2024-09-09 12:41:56
// not familiar with C#, but C#'s equivalent of PHP's:
$count = count($productIds); // where $productIds is the array you also use in IN (...)

SELECT IF ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)) = $count, 1, 0)
// not familiar with C#, but C#'s equivalent of PHP's:
$count = count($productIds); // where $productIds is the array you also use in IN (...)

SELECT IF ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)) = $count, 1, 0)
回复 原文
成熟的代价 2024-09-09 12:41:56

如果 IN 子句是一个参数（无论是 SP 还是热构建 SQL），则始终可以这样做：

SELECT (SELECT COUNT(1)
          FROM product_a
         WHERE product_id IN (1, 8, 100)
       ) = (number of commas in product_id as constant)

如果 IN 子句是一个表，则始终可以这样做：

SELECT (SELECT COUNT(*)
          FROM product_a
         WHERE product_id IN (SELECT Products
                                FROM #WorkTable)
       ) = (SELECT COUNT(*)
              FROM #WorkTable)

如果 IN 子句很复杂，则可以将其假脱机放入表中或写入两次。

If the IN clause is a parameter (either to SP or hot-built SQL), then this can always be done:

SELECT (SELECT COUNT(1)
          FROM product_a
         WHERE product_id IN (1, 8, 100)
       ) = (number of commas in product_id as constant)

If the IN clause is a table, then this can always be done:

SELECT (SELECT COUNT(*)
          FROM product_a
         WHERE product_id IN (SELECT Products
                                FROM #WorkTable)
       ) = (SELECT COUNT(*)
              FROM #WorkTable)

If the IN clause is complex then either spool it into a table or write it twice.

回复 原文
冷夜 2024-09-09 12:41:56

如果您将 ID 存储在临时表中（可以通过某些 C# 函数或简单的 SQL 来完成），那么问题在 SQL 中就变得简单且可行。

select "all exist"
where (select case  when count(distinct t.id) = (select count(distinct id) from #products) then "true" else "false" end
    from ProductTable t, #products p
    where t.id = p.id) = "true"

当 #products 中的所有产品都存在于目标表 (ProductTable) 中时，这将返回“全部存在”，如果上述情况不成立，则不会返回行。

如果您不愿意写入临时表，那么您需要输入一些参数来表示您尝试查找的产品数量，并将临时表替换为“in”；子查询如下所示：

SELECT "All Exist"
WHERE(
        SELECT case when count(distinct t.id) = @ProductCount then "true" else "false" 
        FROM ProductTable t 
        WHERE t.id in (1,100,10,20) -- example IDs
) = "true"

If you have the IDs stored in a temp table (which can be done by some C# function or simple SQL) then the problem becomes easy and doable in SQL.

select "all exist"
where (select case  when count(distinct t.id) = (select count(distinct id) from #products) then "true" else "false" end
    from ProductTable t, #products p
    where t.id = p.id) = "true"

This will return "all exists" when all the products in #products exist in the target table (ProductTable) and will not return a row if the above is not true.

If you are not willing to write to a temp table, then you need to feed in some parameter for the number of products you are attempting to find, and replace the temp table with an 'in'; clause so the subquery looks like this:

SELECT "All Exist"
WHERE(
        SELECT case when count(distinct t.id) = @ProductCount then "true" else "false" 
        FROM ProductTable t 
        WHERE t.id in (1,100,10,20) -- example IDs
) = "true"
回复 原文
以歌曲疗慰 2024-09-09 12:41:56

如果您使用的是 SQL Server 2008，我将创建一个存储过程，该过程采用 一个表- 值参数。然后，查询应该是一种特别简单的形式：

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE (SELECT COUNT(DISTINCT ProductID) FROM @param)
        = (SELECT COUNT(DISTINCT p.ProductID) FROM @param AS p
           INNER JOIN Products 
               ON p.ProductID = Products.ProductID)
END

我将其更改为返回一行，正如您似乎需要的那样。还有其他方法可以使用 WHERE NOT EXISTS 来执行此操作（此处的 LEFT JOIN WHERE rhs IS NULL）：

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE NOT EXISTS (
        SELECT * FROM @param AS p
        LEFT JOIN Products 
            ON p.ProductID = Products.ProductID
        WHERE Products.ProductID IS NULL
    )
END

If you are using SQL Server 2008, I would create a stored procedure which takes a table-valued parameter. The query should then be of a particularly simple form:

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE (SELECT COUNT(DISTINCT ProductID) FROM @param)
        = (SELECT COUNT(DISTINCT p.ProductID) FROM @param AS p
           INNER JOIN Products 
               ON p.ProductID = Products.ProductID)
END

I changed this to return a row, as you seem to require. There are other ways to do this with a WHERE NOT EXISTS (LEFT JOIN in here WHERE rhs IS NULL):

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE NOT EXISTS (
        SELECT * FROM @param AS p
        LEFT JOIN Products 
            ON p.ProductID = Products.ProductID
        WHERE Products.ProductID IS NULL
    )
END
回复 原文
彼岸花ソ最美的依靠 2024-09-09 12:41:56

你的 c# 只需要做一些工作（计算传入的 ID 数量），但试试这个：

select (select count(*) from players where productid in (1, 10, 100, 1000)) = 4

编辑：

4 绝对可以参数化，整数列表也可以。

如果您不根据用户输入的字符串生成 SQL，则无需担心攻击。如果是，您只需确保只获得整数。例如，如果您输入字符串“1,2,3,4”，您会执行类似的操作，

String.Join(",", input.Split(",").Select(s => Int32.Parse(s).ToString()))

如果您得到错误的结果，则会抛出异常。然后只需将其设置为参数即可。

另外，请务必确保 items.Count == 0 的特殊情况，因为如果您将其发送到 where ParameterID in ()，您的数据库将会阻塞。

Your c# will have to do just a bit of work (counting the number of IDs passed in), but try this:

select (select count(*) from players where productid in (1, 10, 100, 1000)) = 4

Edit:

4 can definitely be parameterized, as can the list of integers.

If you're not generating the SQL from string input by the user, you don't need to worry about attacks. If you are, you just have to make sure you only get integers. For example, if you were taking in the string "1, 2, 3, 4", you'd do something like

String.Join(",", input.Split(",").Select(s => Int32.Parse(s).ToString()))

That will throw if you get the wrong thing. Then just set that as a parameter.

Also, be sure be sure to special case if items.Count == 0, since your DB will choke if you send it where ParameterID in ().

回复 原文
情徒 2024-09-09 12:41:56

这可能太简单了，但我总是使用：

SELECT COUNT(*)>0 FROM `table` WHERE condition;

This may be too simple, but I always use:

SELECT COUNT(*)>0 FROM `table` WHERE condition;
回复 原文
娇纵 2024-09-09 12:41:56

例子：

SELECT iif(count(id)=0,'false','true') FROM table WHERE id = 19

Example:

SELECT iif(count(id)=0,'false','true') FROM table WHERE id = 19
回复 原文
~没有更多了~

关于作者

我爱人

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

书间行客

文章 0 评论 0

我ぃ本無心為│何有愛

文章 0 评论 0

神妖

文章 0 评论 0

undefined

文章 0 评论 0

38169838

文章 0 评论 0

彡翼

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文