如果其中一行包含特定值，则返回列中具有重复值的行

I'm attempting to query against a table to determine where a value is assigned multiple times but one of assignees must equal a determined value.

我正在尝试查询表以确定多次分配值的位置，但其中一个受理人必须等于确定的值。

For example :

例如：

EmployeeName  |EmployeeNumber|EmployeeDept|EmployeeBadgeCode|
--------------+--------------+------------+-----------------+
Dante Hicks   |1             |200         |1                  pair A
Randall Graves|5             |201         |2                            no pair
Brody Bruce   |1             |555         |3                  pair A
Banky Edwards |20            |004         |4                  pair B
Gwen Turner   |7             |200         |5
Holden McNeil |20            |450         |1                  pair B  
TS Quint      |5             |105         |10                           no pair

I want to return where all rows where there is a duplicate EmployeeNumber but only if one of the duplicate's value is 1 for EmployeeBadgeCode.

我想返回存在重复EmployeeNumber的所有行的位置，但仅当EmployeeBadgeCode的副本值为1时才返回。

So I would want to return 'Dante Hicks' and 'Brody Bruce' as one pair of duplicates and 'Banky Edwards' and 'Holden McNeil' as another pair of duplicates.

因此，我希望将“Dante Hicks”和“Brody Bruce”作为一对副本返回，将“Banky Edwards”和“Holden McNeil”作为另一对副本。

'Randall Graves' and 'TS Quint' would not be returned because they have duplicate EmployeeNumber but neither of EmployeeBadeCode values are equal to 1.

不会返回'Randall Graves'和'TS Quint'，因为它们具有重复的EmployeeNumber，但EmployeeBadeCode值都不等于1。

Any help would be appreciated.

任何帮助，将不胜感激。

Thanks.

谢谢。

2 个解决方案

#1

SQL Fiddle Demo

SQL小提琴演示

e1.[EmployeeName] < e2.[EmployeeName]

This is to make sure you dont try to match with yourself and also avoid reverse pair.

这是为了确保你不要试图与自己匹配，也避免反向对。

SELECT e1.*, e2.*
FROM employee e1
JOIN employee e2
  ON e1.[EmployeeNumber] = e2.[EmployeeNumber]
 AND e1.[EmployeeName] < e2.[EmployeeName]
WHERE 
    e1.EmployeeBadgeCode = 1
or  e2.EmployeeBadgeCode = 1

In this solution both employess can have 1. You need add one aditional validation if want only one of the employee be 1

在这个解决方案中，两个雇员都可以拥有1.如果只需要一个雇员中的一个，您需要添加一个附加验证

OUTPUT

OUTPUT

|  EmployeeName | EmployeeNumber | EmployeeDept | EmployeeBadgeCode |  EmployeeName | EmployeeNumber | EmployeeDept | EmployeeBadgeCode |
|---------------|----------------|--------------|-------------------|---------------|----------------|--------------|-------------------|
|   Brody Bruce |              1 |          555 |                 3 |   Dante Hicks |              1 |          200 |                 1 |
| Banky Edwards |             20 |            4 |                 4 | Holden McNeil |             20 |          450 |                 1 |

#2

This will return both those rows. If there was only one EmployeeNumber 1 then it would not return anything.

这将返回这两行。如果只有一个EmployeeNumber 1那么它将不会返回任何内容。

SELECT *
FROM YourTable 
WHERE EmployeeNumber =
    (SELECT EmployeeNumber
    FROM YourTable
    WHERE EmployeeNumber = 1
    GROUP BY EmployeeNumber 
    HAVING COUNT(1) > 1)

#1