T-SQL - 值与列表中的至少一个项不匹配

I have an MS SQL table with several columns, each for different employee roles, and a corresponding column for each indicating whether the current employee in that role is active, on leave, terminated, etc. Due to dependency on an external data source, the fields aren't really normalized.

我有一个MS SQL表，其中包含多个列，每个列用于不同的员工角色，每个列都有一个相应的列，用于指示该角色中的当前员工是处于活动状态，休假，终止等。由于依赖于外部数据源，字段没有真正规范化。

I'm looking to run a query to return all of the rows where at least one of the 'is active' columns is not equal to 'ACTIVE'. There are several possible values other than Active.

我希望运行一个查询来返回所有行中至少有一个'is active'列不等于'ACTIVE'。除Active之外还有几个可能的值。

I know one of the longer ways to do this would be

我知道有一个更长的方法可以做到这一点

SELECT * FROM MYTABLE
WHERE IsActive1 <> 'ACTIVE' OR
      IsActive2 <> 'ACTIVE' OR
      IsActive3 <> 'ACTIVE' OR
      IsActive4 <> 'ACTIVE' OR
... etc

Just wondering if there is a shorter way, possibly more efficient way to do this. I've seen plenty of solutions for finding a match across multiple columns, but not one for looking for non-matches.

只是想知道是否有更短的方式，可能更有效的方式来做到这一点。我已经看到了很多解决方案，可以在多个列中查找匹配项，但不是一个用于查找非匹配项的解决方案。

2 个解决方案

#1

I don't know that this is more elegant, but you could do something like this:

我不知道这更优雅，但你可以这样做：

where replace(IsActive1 + IsActive2 + IsActive3 + . . .,
              'ACTIVE', '') <> ''

Note that if the values could be NULL, then you would need to replace them with something else. Also, this assumes that the strings themselves are not empty.

请注意，如果值可能为NULL，那么您需要将其替换为其他值。此外，这假设字符串本身不为空。

EDIT:

编辑：

If you want to do this efficiently and want the code to look good, add a computed column and build an index on that column:

如果您希望有效地执行此操作并希望代码看起来不错，请添加计算列并在该列上构建索引：

alter table mytable
    add IsAllActive as (case when IsActive1 = 'ACTIVE' and
                                  IsActive2 = 'ACTIVE' and
                                  . . .
                             then 'ACTIVE'
                             else 'INACTIVE'
                        end);

create index mytable_IsAllActive on mytable(IsAllActive);

You might want to add other relevant columns to the index as well.

您可能还想将其他相关列添加到索引中。

#2

Try using a filtered index, which is quite limited in what you can include in the WHERE clause:

尝试使用过滤索引，这在WHERE子句中可以包含的内容非常有限：

--simulate your existing table
create table Test (RowID int identity(1,1) primary key
                  ,c1 varchar(10), c2 varchar(10),c3 varchar(10),c4 varchar(10)
                  )
go
--simulate your existing data
INSERT INTO Test VALUES ('ACTIVE','ACTIVE','ACTIVE','ACTIVE')        --1
                      , ('ACTIVE','ACTIVE','ACTIVE','ACTIVE')        --2  
                      , ('ACTIVE','ACTIVE','ACTIVE','ACTIVE')        --3
                      , ('INACTIVE','ACTIVE','ACTIVE','ACTIVE')      --4 <Inactive
                      , ('ACTIVE','INACTIVE','ACTIVE','ACTIVE')      --5 <Inactive
                      , ('ACTIVE','ACTIVE','INACTIVE','ACTIVE')      --6 <Inactive
                      , ('ACTIVE','ACTIVE','ACTIVE','INACTIVE')      --7 <Inactive
                      , ('INACTIVE','ACTIVE','ACTIVE','INACTIVE')    --8 <Inactive
                      , ('INACTIVE','ACTIVE','INACTIVE','INACTIVE')  --9 <Inactive
                      , ('INACTIVE','INACTIVE','INACTIVE','INACTIVE')--10<Inactive
go

--what you need to add to your existing table
CREATE NONCLUSTERED INDEX fIX_Test_c1 ON Test(c1) WHERE C1 !='ACTIVE'
CREATE NONCLUSTERED INDEX fIX_Test_c2 ON Test(c2) WHERE C2 !='ACTIVE'
CREATE NONCLUSTERED INDEX fIX_Test_c3 ON Test(c3) WHERE C3 !='ACTIVE'
CREATE NONCLUSTERED INDEX fIX_Test_c4 ON Test(c4) WHERE C4 !='ACTIVE'

--your query o get the data
      SELECT * FROM Test WHERE c1 !='ACTIVE'
UNION SELECT * FROM Test WHERE c2 !='ACTIVE'
UNION SELECT * FROM Test WHERE c3 !='ACTIVE'
UNION SELECT * FROM Test WHERE c4 !='ACTIVE'

you could try a persisted computed column, with an index:

你可以尝试一个带有索引的持久计算列：

ALTER TABLE Test ADD C_Summary AS (LEFT(ISNULL(c1,'Z'),1)+LEFT(ISNULL(c1,'Z'),1)+LEFT(ISNULL(c1,'Z'),1)+LEFT(ISNULL(c1,'Z'),1))
go
CREATE NONCLUSTERED INDEX fIX_Test_C_Summary ON Test(C_Summary) 
go

select * from test WHERE C_Summary !='AAAA'

You'll have to try these (and other variations) out on your data, which can skew the index usage. Also, you might want to try changing the table design, where you split all the C1, C2, C3, C4,... columns into different rows in a child table. You could then do a single filtered index on that table.

您必须在数据上尝试这些（和其他变体），这可能会扭曲索引的使用。此外，您可能希望尝试更改表设计，将所有C1，C2，C3，C4，...列拆分为子表中的不同行。然后，您可以在该表上执行单个筛选索引。

#1