SQL Server Check if multiple entries exist

y1aodyip  于 2023-03-17  发布在  其他
关注(0)|答案(2)|浏览(139)

In SQL Server 2022 (v15), I have this table:

Table file
-------------
Id    name        Md5
1     abc.txt     xxxxx1
2     bbb.doc     xxxxx2
3     ccc.bin     xxxxx3

If the client passes some md5s to query if these files in the database, I can make a query like,

SELECT Md5 
FROM file 
WHERE md5 IN ('xxx1', 'xxx2', ...)

This query returns a dataset with the existing file Md5s. The client needs to write some extra code to know whether xxx1 exists, whether xxx2 exists ...

Can I give the result for each file in the query? Like,

SELECT 
    CASE WHEN 'xxx1' IN ('xxx1', 'xxx2', ...) THEN 1 ELSE 0 AS 'xxx1',
    CASE WHEN 'xxx2' IN ('xxx1', 'xxx2', ...) THEN 1 ELSE 0 AS 'xxx2',
    ...
FROM
    ....
WHERE 
    Md5 IN ('xxx1', 'xxx2', ...)
64jmpszr

64jmpszr1#

You could do a simple PIVOT

Note: The WHERE is optional, but may be more performant

Example

Select * 
 From  (Select Md5
         From  YourTable
         Where md5 in ('xxxxx1', 'xxxxx2')  -- << Optional
       ) src
 Pivot ( count(Md5) for Md5 in ([xxxxx1], [xxxxx2]) ) Pvt

Results

xxxxx1  xxxxx2
1       1
pengsaosao

pengsaosao2#

The easiest way to do this is to start with a table of the MD5 hashes to check, and left-join the file table.

You can use a VALUES constructor

SELECT
  v.Md5,
  f.Id   -- this column is NULL if not matched
FROM (VALUES
    (0xxxx1),
    (0xxxx2),
    (0xxxx8)
) v(md5)
LEFT JOIN file f ON f.md5 = v.md5;

Or, depending on how you pass the data in, you can use a table variable, temp table or Table-Valued Parameter.

DECLARE @values TABLE (md5 binary(32) PRIMARY KEY);
INSERT @values (md5)
VALUES
    (0xxxx1),
    (0xxxx2),
    (0xxxx8);

SELECT
  v.Md5,
  f.Id   -- this column is NULL if not matched
FROM @values v
LEFT JOIN file f ON f.md5 = v.md5;

The results would be something like this

Md5Id
0xxxx11
0xxxx22
0xxxx8NULL

相关问题