mysql - 确定哪个客户产生的收入最多

Question

我必须确定哪个客户产生的总收入最多。我设法创建了一个查询，其中列出了所有客户以及他们产生了多少收入。

SELECT P.FirstName, P.LastName, SUM(BookingFee) AS Revenue
FROM Person P, Customer C, Reservation R
WHERE C.Id = P.Id AND R.AccountNo = C.AccountNo
GROUP BY C.AccountNo
ORDER BY Revenue DESC

按降序排列，产生最多收入的客户排在最前面。但我必须让查询只生成包含最高创收客户的一行。我不太确定该怎么做，或者我现在所拥有的是否走在正确的轨道上。

任何收入都与预订表中的 BookingFee 属性相关联。

这是我的数据库的相关部分和一些示例数据

DROP DATABASE IF EXISTS flight_reservation_system_2;

CREATE DATABASE flight_reservation_system_2;

USE flight_reservation_system_2;



CREATE TABLE Person (
    Id INTEGER,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Phone CHAR(10) NOT NULL,
    Address VARCHAR(100) NOT NULL,

    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    ZipCode INTEGER NOT NULL,
    PRIMARY KEY (Id),
    CHECK (Id > 0),
    CHECK (Phone > 0),
    CHECK (ZipCode > 0)
);

CREATE TABLE Employee (
    Id INTEGER NOT NULL,
    SSN INTEGER,
    IsManager BOOLEAN NOT NULL,
    StartDate DATE NOT NULL,
    HourlyRate NUMERIC(10 , 2 ) NOT NULL,
    PRIMARY KEY (SSN),
    FOREIGN KEY (Id)
        REFERENCES Person (Id),
    UNIQUE (Id),
    CHECK (SSN > 0),
    CHECK (HourlyRate > 0)
);

CREATE TABLE Customer (
    Id INTEGER NOT NULL,
    AccountNo INTEGER,
    CreditCardNo CHAR(16),
    Email VARCHAR(50),
    CreationDate DATETIME NOT NULL,
    Rating INTEGER,
    PRIMARY KEY (AccountNo),
    FOREIGN KEY (Id)
        REFERENCES Person (Id),
    CHECK (Rating >= 0 AND Rating <= 10)
);

CREATE TABLE Reservation (
    ResrNo INTEGER,
    ResrDate DATETIME NOT NULL,
    BookingFee NUMERIC(10 , 2 ) NOT NULL,
    TotalFare NUMERIC(10 , 2 ) NOT NULL,
    RepSSN INTEGER,
    AccountNo INTEGER NOT NULL,
    PRIMARY KEY (ResrNo),
    FOREIGN KEY (RepSSN)
        REFERENCES Employee (SSN),
    FOREIGN KEY (AccountNo)
        REFERENCES Customer (AccountNo),
    CHECK (ResrNo > 0),
    CHECK (BookingFee >= 0),
    CHECK (TotalFare > BookingFee)
);


INSERT INTO Person(Id, FirstName, LastName, Phone, Address, City, State, ZipCode)
VALUES(1, 'John', 'Doe','1231231234', '123 N Fake Street', 'New York', 'New York', 10001),
(2, 'Jane', 'Smith','5555555555', '100 Nicolls Rd','Stony Brook', 'New York', 17790),
(3, 'Rick', 'Astley','3141592653', '1337 Internet Lane', 'Los Angeles', 'California', 90001);

INSERT INTO Customer(Id, AccountNo, CreditCardNo, Email, CreationDate, Rating)
VALUES(1, 2, NULL, 'jbdoe@woot.com', '2011-01-01 19:30:00', 8),
(2,  1, NULL, 'awesomejane@ftw.com', '2011-01-01 19:30:00', 5),
(3,3, NULL, 'rickroller@rolld.com', '2011-01-01 19:30:00', 2);

INSERT INTO Reservation(ResrNo, ResrDate, BookingFee, TotalFare, RepSSN, AccountNo)
VALUES(111 , '2010-01-03 19:30:00', 10, 1200.00, NULL, 1),
(222 , '2011-01-03 19:30:00', 30, 500.00,NULL, 2),
(333 , '2011-01-03 19:30:00', 25, 3333.33, NULL, 3);

Answer 1

使用LIMIT:

LIMIT 子句可用于限制 SELECT 语句返回的行数。 LIMIT 接受一个或两个数字参数。

SELECT P.FirstName, P.LastName, SUM(BookingFee) AS Revenue
FROM Person P, Customer C, Reservation R
WHERE C.Id = P.Id AND R.AccountNo = C.AccountNo
GROUP BY C.AccountNo
ORDER BY Revenue DESC
LIMIT 1

结果:

FIRSTNAME   LASTNAME    REVENUE
John        Doe         30

在 SQL Fiddle 中查看结果.