在同一行的不同列中查找重复值

Before someone goes on a rant that this table should be normalized, best practices, etc. I am going to admit that this is an old table we have in SQL Server 2008 R2 and I can't do anything about changing it. Having said that, this table has the following columns:

在有人说这个表应该规范化，最佳实践等之前我会承认这是我们在SQL Server 2008 R2中的旧表，我无法做任何改变它。话虽如此，这个表有以下几列：

"PreparedBy", "PrelimApprovalBy", "Approval1Signer", "Approval2Signer"

All these fields have either usernames or NULL or ''. I want to get all the rows where the same username appears in 2 OR MORE of the fields mentioned above. If 2 fields are NULL they are NOT a match and they are NOT a match if they are both ''. So both NULL and '' need to be excluded as they don't signify anything.

所有这些字段都有用户名或NULL或''。我想获得相同用户名出现在上述2个或更多字段中的所有行。如果2个字段为NULL，则它们不匹配，如果它们都是''，则它们不匹配。所以NULL和''都需要被排除，因为它们不代表任何东西。

HERE'S WHAT I THOUGHT OF SO FAR BUT AM NOT LIKING IT:
I am thinking of checking all permutations in the WHERE clause (checking for NULL and '') by doing something along the lines of

这里我想到了什么，但我不喜欢它：我正在考虑检查WHERE子句中的所有排列（检查NULL和''）做一些事情的行

WHERE PreparedBy =  PrelimApprovalBy OR PreparedBy = Approval1Signer OR ...

There has got to be a better way to do it.

必须有一个更好的方法来做到这一点。

2 个解决方案

#1

Here's one:

这是一个：

SELECT * FROM T
WHERE EXISTS 
     (SELECT 1 
      FROM (VALUES 
                   (PreparedBy)
                  ,(PrelimApprovalBy)
                  ,(Approval1Signer)
                  ,(Approval2Signer)) AS X (n)
      WHERE NULLIF(n, '') IS NOT NULL
      GROUP BY n
      HAVING COUNT(*)>1
     )

Basically, for each row, we're constructing a mini-table with the column values in different rows, and doing a GROUP BY and HAVING to check for groups of matching values. The NULLIF is helping us ignore '' values (making them NULL and then excluding all NULLs).

基本上，对于每一行，我们构建一个包含不同行中列值的小型表，并执行GROUP BY和HAVING以检查匹配值组。 NULLIF帮助我们忽略''值（使它们为NULL然后排除所有NULL）。

#2

Try this query:

试试这个查询：

SELECT PreparedBy, PrelimApprovalBy, Approval1Signer, Approval2Signer
WHERE 
((PreparedBy = PrelimApprovalBy AND NULLIF(PreparedBy, '') IS NOT NULL) 
OR
(PreparedBy = Approval1Signer AND NULLIF(PreparedBy, '') IS NOT NULL) 
OR
(PreparedBy = Approval2Signer AND NULLIF(PreparedBy, '') IS NOT NULL) 
OR
(PrelimApprovalBy = Approval1Signer  AND NULLIF(PrelimApprovalBy, '') IS NOT NULL) 
OR 
(PrelimApprovalBy = Approval2Signer AND NULLIF(PrelimApprovalBy, '') IS NOT NULL) 
OR
(Approval1Signer = Approval2Signer AND NULLIF(Approval1Signer, '') IS NOT NULL))

I can't think of anything simplier to achieve what you seek for.

我想不出任何更简单的东西来实现你所追求的目标。

#1