SQL查询的简化

Question

SQL不是我的专长。我有一个有效的查询，它可以完成我需要的东西，但我知道必须有一种更干净的方法来实现我的目标。

查询应返回：

 - Name
 - Date of most recent donation (if any)
 - Political Party guess as: R, D, or U, where:
    - R = Likely Republican (more republican donations than other donations)
    - D = Likely Democrat (more democrat donations than other donations)
    - U = Undtermined (no donations on record, or R and D are tied for first place.
- Address

下面是一个SQLFiddle链接，您可以在其中找到这些表和我的解决方案：http://sqlfiddle.com/#!9/f2303f/2。或者，下面是模式构建：

CREATE TABLE People (
ID INT UNSIGNED  DEFAULT '0000' NOT NULL,
Name  CHAR(20)      DEFAULT ''     NOT NULL,
Address   CHAR(40)      DEFAULT ''     NOT NULL,
PRIMARY KEY(ID));

INSERT INTO People VALUES
(1, "Name1", "Idaho"),
(2, "Name2","UCLA"),
(3, "Name3", "Carolina"),
(4, "Name4", "Portland");

CREATE TABLE Donations (
ID INT UNSIGNED  DEFAULT '0000' NOT NULL,
People_ID  INT UNSIGNED  DEFAULT '0000' NOT NULL,
Donation_Date   DATE  NOT NULL,
Party   INT SIGNED  DEFAULT '0000' NOT NULL,
PRIMARY KEY(ID));

INSERT INTO Donations VALUES
#Name1
(1, 1, "2000-06-23", 1), (2, 1, "2000-06-24",-1),

#Name2
(3, 2, "2001-06-25", 1),(4, 2, "2001-06-26", 1),

# Name3
(5, 3, "2002-06-26", -1),(6, 3, "2002-06-27", -1);

#Name4
#None

下面是查询：

SELECT Name, 
  IFNULL(donation_date, 'None') as 'Recent Donation', 
  IFNULL(voting_guess, "U") as 'Party Guess', 
  Address
FROM people p

LEFT JOIN donations on donations.people_id = p.id AND donations.donation_date = (
  SELECT MAX(donation_date) 
  FROM donations
  WHERE donations.people_id = p.id)
  
LEFT JOIN 
  (SELECT people_id, 
    (CASE
       WHEN SUM(party) > 0 THEN "R"
       WHEN SUM(party) < 0 THEN "D"
     END
    ) AS voting_guess
    FROM donations
    GROUP BY people_id
  ) voting ON P.id = voting.people_id

具体地说，我想尝试将后两个左连接压缩为一个。有什么建议吗？

Answer 1

您可以左联接在其中完成聚合的单个派生表。

SELECT p1.name,
       coalesce(x1.donation_date, 'None') `Recent Donation`,
       coalesce(x1.voting_guess, 'U') `Party Guess`,
       p1.address
       FROM people p1
            LEFT JOIN (SELECT d1.people_id,
                              max(d1.donation_date) donation_date,
                              CASE
                                WHEN sum(d1.party) > 0 THEN
                                  'R'
                                WHEN sum(d1.party) < 0 THEN
                                  'D'
                              END voting_guess
                              FROM donations d1
                              GROUP BY d1.people_id) x1
                      ON x1.people_id = p1.id;

而且，在移植到另一个DBMS时，您还应该学会使用尽可能接近标准SQL的正确引号，以最大限度地减少兼容性问题。对于文本，仅使用单引号，并且仅对文本使用单引号。对于标识符，仅使用反刻度，并且仅对标识符使用反刻度。请不要使用双引号。在标准SQL和许多其他数据库管理系统中，双引号用于标识符，就像MySQL中的反引号一样。