比较sql中组内的列

Question

我对 SQL 很陌生，所以如果这个问题很难理解，我深表歉意。

假设我有一个像这样的表：

Name    Birthday
Bob     7/18
Bob     7/18
Mark    5/10
Mark    7/5  
Sue     2/1
Joe     1/14
Joe     1/14
Joe     1/2
Jeff    9/16
Jeff    3/20
Jeff    6/13
[...]

我想执行一个选择语句，为我提供具有多个不同生日的人的姓名。

因此，对于我的示例表，输出将是 Mark、Joe 和 Jeff。

感谢您的帮助。

Answer 1

根据我的理解，重新措辞您的问题陈述：

所有名称，其中：
- 该名称有多个与之关联的颜色
- 其中，至少有两种颜色具有与其关联的不同生日

我不确定的是，与同一名称关联的相同颜色是否可能有两个不同的生日？

如果否，则颜色变得无关紧要，您只需要一个具有超过 1 个不同生日的名称即可。

SELECT
  Name
FROM
  yourTable
GROUP BY
  name
HAVING
  COUNT(DISTINCT birthday) > 1

If `yes`, you need to find another record with the same name, but also a different colour AND a different birthday.

SELECT
Name
FROM
yourTable
WHERE
EXISTS (SELECT * FROM yourTable as [lookup] WHERE Name = yourTable.Name AND Birthday yourTable.Birthday AND Colour yourTable.Colour)
GROUP BY
Name

Answer 2

SELECT name FROM table_xyz 
GROUP BY Name, Birthday
HAVING count(*) = 1

Answer 3

试试这个：

select
    NAME
    FROM (select
              NAME
              FROM YourTable
              GROUP BY Name,Birthday
              HAVING Count(Name)=1
         ) dt
    GROUP BY Name
    HAVING COUNT(Name)>1

SQL Server 的工作示例代码：

DECLARE @YourTable table (name varchar(10),Birthday varchar(10), FavoriteColor varchar(10))
INSERT @YourTable VALUES ('Bob'  ,   '7/18'   ,    'Blue')
INSERT @YourTable VALUES ('Bob'  ,   '7/18'   ,    'Green')
INSERT @YourTable VALUES ('Mark' ,   '5/10'   ,    'Blue')
INSERT @YourTable VALUES ('Mark' ,   '7/5'    ,    'Green')

select
    NAME
    FROM (select
              NAME
              FROM @YourTable
              GROUP BY Name,Birthday
              HAVING Count(Name)=1
         ) dt
    GROUP BY Name
    HAVING COUNT(Name)>1

输出：

NAME
----------
Mark

Answer 4

您可以通过几种不同的方式来完成此操作。您可以JOIN 表自身，也可以使用EXISTS 子句。

SELECT DISTINCT
    T1.name
FROM
    Some_Table AS T1
WHERE
    EXISTS (SELECT *
            FROM Some_Table T2
            WHERE T2.name = T1.name AND
                  T2.birthday <> T1.birthday)

或者

SELECT DISTINCT
    T1.name
FROM
    Some_Table AS T1
INNER JOIN Some_Table AS T2 ON
    T2.name = T1.name AND
    T2.birthday <> T1.birthday

您也可以使用 HAVING 子句解决这个特定问题：

SELECT
    T1.name
FROM
    Some_Table AS T1
GROUP BY
    T1.name
HAVING
    MAX(birthday) <> MIN(birthday)