sql-server - 条件行选择

Question

我正在尝试从表格中选择最适用(最通用)的行

CREATE TABLE #T(Id int, Bid int, Did int,Pid int, Vid int, SomeData varchar(10))

INSERT INTO #T
VALUES(1,1,1,1,1,'Data1'),
      (2,1,1,NULL,5,'Data2'),
      (3,1,1,NULL,6,'Data3'),
      (4,1,1,8,NULL,'Data4'),
      (5,1,NULL,NULL,NULL,'Data5')

SELECT *
FROM #T

当我使用如下参数值时，我期望的行是 ID 1，因为它具有从参数传递的所有值。

DECLARE @Bid INT=1,
        @Did INT=1,
        @Pid INT=1,
        @Vid INT=1

当我使用以下值时，我期望的行是 ID 5。因为没有匹配 Pid 1 和 Vid Null 的行。因此，这些参数最通用的行是 Id = 5 的行。

DECLARE @Bid INT=1,@Did INT =1,@Pid INT =1,@Vid INT =NULL

或

DECLARE @Bid INT=1, @Did INT=1,@Pid INT=1,@Vid INT=6

列值NULL表示它可以适用于任何参数值。

例如，如果所有的值都为空，则表示它是一种主行。但是如果主“SomeData”需要不同，比如说 Vid 1，那么我们创建的行将所有值都为空，但 Vid 值为 1。

列的优先级从左到右。

如果值是

DECLARE @Bid INT=1,@Did INT =1,@Pid INT =10,@Vid INT =1

预期的行是 id 为 5 的行。

我试过下面的查询，但它返回多行。无论如何要实现这样的事情

SELECT *
FROM #T
WHERE ((Bid = @Bid) OR Bid IS NULL) 
    AND ((Did = @Did) OR Did IS NULL)   
    AND ((Pid = @Pid) OR Pid IS NULL)   
    AND ((Vid = @Vid) OR Vid IS NULL)   

Answer 1

如果您不需要担心重复，即不止一行具有相同数量的匹配项，那么您可以尝试以下查询:

SELECT TOP 1 t.Id
FROM
(
    SELECT Id,
           CASE WHEN Bid = @Bid OR (Bid IS NULL AND @Bid IS NOT NULL)
                THEN 1 ELSE 0 END AS BidMatch
           CASE WHEN Did = @Did OR (Did IS NULL AND @Did IS NOT NULL)
                THEN 1 ELSE 0 END AS DidMatch
           CASE WHEN Pid = @Pid OR (Pid IS NULL AND @Pid IS NOT NULL)
                THEN 1 ELSE 0 END AS PidMatch
           CASE WHEN Vid = @Vid OR (Vid IS NULL AND @Vid IS NOT NULL)
                THEN 1 ELSE 0 END AS VidMatch
    FROM #T
) t
ORDER BY t.BidMatch DESC,
         t.DidMatch DESC,
         t.PidMatch DESC,
         t.VidMatch DESC

您可以只删除查询开头的 TOP 1 子句，以查看所有 ID 按它们与输入变量的匹配程度降序排列。