mysql - 通过第一个子查询的结果过滤 UNION 中的第二个子查询

Question

我正在编写一个查询，根据某人是“高捐助者”还是“低捐助者”来提取联系人数据，我的查询工作得很好，除了它会同时包括高捐助者和低捐助者，如果他们在这两个类别中，当我们真的想将它们作为高捐助者时。

(例如:Jay Smith 在 6 月捐赠了 1000 美元，在 7 月捐赠了 50 美元。Jay 将同时列为高捐赠者和低捐赠者。)

为解决此问题，我尝试根据联系人是否已被发现为高捐助者来将他们排除在低捐助者之外。我的原始查询合并了这些子查询，但是当我尝试为各个子查询添加别名并在第二个子查询中引用 highDonors 时，我收到错误消息。

(注意:这是完整查询的简化版本，重点关注导致错误的部分。)

这是我得到的错误:#1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本对应的手册，了解在第 23 行使用 near 'highDonors.contactId) as lowDonors ) as highAndLowDonors ' 的正确语法

SELECT DISTINCT
... 
FROM
    (( 
      SELECT DISTINCT contactid, 
                      "highDonor" AS highorlowdonor 
      FROM            donation 
      WHERE           donationdate > "2015-07-03" 
      AND             donationamount BETWEEN 100 AND 9999.99)
    AS highdonors
UNION ALL 
    ( 
      SELECT DISTINCT contactid, 
                      "lowDonor" AS highorlowdonor 
      FROM            donation 
      WHERE           donationdate > "2015-07-03" 
      AND             donationamount BETWEEN 1 AND 99.99 
      WHERE           contactid NOT IN highdonors.contactid)
    AS lowdonors )
    AS highandlowdonors
    ...

对导致此错误的原因有任何想法吗？我可以像这样为两个子查询设置别名并在第二个子查询中使用第一个吗？

Answer 1

除非重复第一个查询，否则不能在第二个查询中使用第一个查询的结果。

但是您可以重写查询以按 contactid 分组并获得最大 donationamount。将您的逻辑放入 CASE 中，查看最大值。

SELECT contactid,
       CASE
         WHEN max(donationamount) BETWEEN 100 AND 9999.99
           THEN 'highDonor'
         WHEN max(donationamount) BETWEEN 1 AND 99.99
           THEN 'lowDonor'
       END highorlowdonor
       FROM donation
       WHERE donationdate > '2015-07-03'
       GROUP BY contactid
       HAVING CASE
                WHEN max(donationamount) BETWEEN 100 AND 9999.99
                  THEN 'highDonor'
                WHEN max(donationamount) BETWEEN 1 AND 99.99
                  THEN 'lowDonor'
              END IS NOT NULL;

(注意:如果没有 HAVING，这将包括捐赠金额 < 1 或 99.99 < 金额 < 100 或金额 > 9999.99 但 highorlowdonor 的捐赠者null。与其使用 HAVING，我宁愿建议调整您的逻辑以使用 <(或 >)而不是 <=(或 >=)，BETWEEN 是一个的别名。)

顺便说一句:您在应该使用单引号 (') 的地方使用双引号 (")(将字符串和日期文字括起来)。