mysql - 来自SQL中多个列的DISTINCT值

我有以下作业：


  编写SELECT语句，为每个姓氏和帐单地址与另一位顾客相同的顾客返回一行。将结果集按姓氏和名字排序。


我尝试使用DISTINCT关键字来执行此操作，但这不符合我的目的。

出于某种原因，每次我使用GROUP BY时，都会出现以下错误：

SELECT FirstName, LastName, BillingAddressID
    FROM Customers
    GROUP BY LastName;

  选择列表中的“ Customers.FirstName”列无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。


我尝试了UNION并出现以下错误：

SELECT LastName FROM Customers

UNION

SELECT BillingAddressID FROM Customers

  将varchar值“ Sherwood”转换为数据类型int时，转换失败。


有人可以指出我正确的方向吗？

这是我正在处理的数据集的示例

firstname   lastname    billingaddressid
Allan       Sherwood        2
Barry       Zimmer          3
Christine   Brown           4
David       Goldstein       6
Erin        Sherwood        7
Frank Lee   Wilson          8
Gary        Brown           4
Heather     Esway           12

因此，查询应删除姓氏的重复条目。

最佳答案

尝试-

SELECT FirstName,
       Customers.LastName,
       Customers.billingAddressID
FROM Customers INNER JOIN
     ( SELECT LastName,
              billingAddressID
       FROM Customers
       GROUP BY LastName,
                billingAddressID
       HAVING COUNT( LastName ) >= 2 ) lastNameQuery
  ON Customers.LastName = lastNameQuery.LastName AND
     Customers.billingAddressID = lastNameQuery.billingAddressID
ORDER BY Customers.LastName,
         FirstName;

我针对使用以下脚本创建的数据库进行了测试...

CREATE DATABASE Cust;

USE Cust;

CREATE TABLE Customers
(
    fldID               INT              NOT NULL    AUTO_INCREMENT,
    firstName           VARCHAR( 50 )    NOT NULL,
    lastName            VARCHAR( 50 ),
    billingAddressID    INT              NOT NULL,
    PRIMARY KEY ( fldID )
);

我使用-输入了发问者的样本数据

INSERT INTO Customers
SET firstName        = "Allan",
    lastName         = "Sherwood",
    billingAddressID = 2;

INSERT INTO Customers
SET firstName        = "Barry",
    lastName         = "Zimmer",
    billingAddressID = 3;

INSERT INTO Customers
SET firstName        = "Christine",
    lastName         = "Brown",
    billingAddressID = 4;

INSERT INTO Customers
SET firstName        = "David",
    lastName         = "Goldstein",
    billingAddressID = 6;

INSERT INTO Customers
SET firstName        = "Erin",
    lastName         = "Sherwood",
    billingAddressID = 7;

INSERT INTO Customers
SET firstName        = "Frank Lee",
    lastName         = "Wilson",
    billingAddressID = 8;

INSERT INTO Customers
SET firstName        = "Gary",
    lastName         = "Brown",
    billingAddressID = 10;

INSERT INTO Customers
SET firstName        = "Heather",
    lastName         = "Esway",
    billingAddressID = 12;

我还添加了以下内容，以确保重复使用lastName和billingAddressID ...

INSERT INTO Customers
SET firstName        = "Don",
    lastName         = "Sherwood",
    billingAddressID = 22;

INSERT INTO Customers
SET firstName        = "Timmy",
    lastName         = "Sherwood",
    billingAddressID = 22;

INSERT INTO Customers
SET firstName        = "James",
    lastName         = "Brown",
    billingAddressID = 22;

INSERT INTO Customers
SET firstName        = "James",
    lastName         = "Esway",
    billingAddressID = 22;

我们的发问者要问的问题，我认为发问者正在寻求帮助的问题是-

Write a SELECT statement that returns one row for each customer that has the same last name and billing address as another customer. Sort the result set by last name then first name.

我对此的解释是，我们应该为每个客户返回符合以下条件的记录：具有姓氏和与AT LEAST至少一个其他客户共享的BillingAddressID的特定组合，并且返回记录的列表应按姓氏排序并再分类在名字上。

我的回答的核心是细分-

SELECT LastName,
       billingAddressID
FROM Customers

从“客户”中仅选择两个条件字段。

为此，我添加了-

GROUP BY LastName,
         billingAddressID

这会将核心细分结果细化为两个条件字段的每个唯一组合的列表。

然后，我通过添加-将此列表限制为至少出现两次的唯一组合。

HAVING COUNT( LastName ) >= 2

然后，我给结果查询一个lastNameQuery的别名。

然后，我在两个条件字段上都与“客户”一起加入了lastNameQuery，以将我们的“客户”列表限制为使用-具有一对条件值共享的用户

     Customers INNER JOIN
     ( SELECT LastName,
              billingAddressID
       FROM Customers
       GROUP BY LastName,
                billingAddressID
       HAVING COUNT( LastName ) >= 2 ) lastNameQuery
  ON Customers.LastName = lastNameQuery.LastName AND
     Customers.billingAddressID = lastNameQuery.billingAddressID

从中，我使用-选择了所需的字段。

SELECT FirstName,
       Customers.LastName,
       Customers.billingAddressID
FROM

需要澄清对在Customers.和Customers中都出现的字段名称的引用，因此需要使用lastNameQuery。没有这种澄清，MySQL无法确定应使用哪些字段。

通过添加-将此列表按指定顺序排序-

ORDER BY Customers.LastName,
         FirstName;

我测试完整陈述后得到的结果是-

+-----------+----------+------------------+
| FirstName | LastName | billingAddressID |
+-----------+----------+------------------+
| Gary      | Brown    |               10 |
| Tom       | Brown    |               10 |
| Don       | Sherwood |               22 |
| Timmy     | Sherwood |               22 |
+-----------+----------+------------------+

这些是我的扩展样本数据集中唯一在条件字段中具有值的共享组合的客户。