“7个字段中有3个不是空白的行”条件的选项

I've got a requirement to include rows when any 3 out of 7 available columns contain data for that row. Is there a best practice for doing something like that? The solution I came up with seems fine, just wondering if there is a more accepted pattern? See here for exact details. Below is the gist of what I am trying to accomplish did.

当7个可用列中的3个包含该行的数据时，我需要包含行。做这样的事情有什么最佳实践吗?我提出的解决方案似乎还不错，只是想知道是否存在一种更被接受的模式?具体细节请看这里。下面是我要完成的目标的要点。

CREATE TABLE Customer(
    FirstName varchar(50) NULL,
    LastName varchar(50) NULL,
    AddressLine1 varchar(100) NULL,
    AddressLine2 varchar(100) NULL,
    City varchar(50) NULL,
    State char(3) NULL,
    Country char(2) NULL)

Insert Into Customer Values('Bob', null, '', 'addr2', 'city', '', null)
Insert Into Customer Values('', '', '', '', '', '', '')
Insert Into Customer Values(null, null, null, null, null, null, null)

SELECT *
    FROM Customer
    WHERE (CASE WHEN IsNull(FirstName, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(LastName, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(AddressLine1, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(AddressLine2, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(City, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(State, '') = '' THEN 0 ELSE 1 END
           + CASE WHEN IsNull(Country, '') = '' THEN 0 ELSE 1 END) 
            >= 3

1 个解决方案

#1

An alternative.

另一种选择。

SELECT *
FROM   Customer
WHERE  3 <= ((SELECT Count(NULLIF(C, ''))
              FROM   (VALUES (FirstName),
                             (LastName),
                             (AddressLine1),
                             (AddressLine2),
                             (City),
                             (State),
                             (Country)) V(C)))