带有两个条件内联选择的MySQL查询

Question

目标：

2003-10-10.

• Result有两张表格，一张校友表和一张捐款表，每一份贡献都与校友ID

• 相关，列出了在2002-10-10年的日期范围内贡献了2-3000英镑的校友名单，而

• 需要显示校友的姓名、ID、所有时间的校友总数，以及每个校友在指定日期范围

的捐款总额。

的尝试：

SELECT alumni.alumni_id, alumni.alumni_name,
(SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id) AS contrib_alltime_total,
(SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id AND contribution.date >= 2002-10-10 AND contribution.date <= 2003-10-10) AS contrib_range_total

FROM alumni
WHERE hold_code IS NULL    
ORDER BY lname ASC    
HAVING contrib_range_total >= 2000
HAVING contrib_range_total <= 3000

我得到了一个语法错误，很可能是因为我试图使用两个

我还意识到，您想要使用有与组，但我不需要做任何分组

我也许可以这样做，但我认为它效率低下，可能需要很长时间。

SELECT alumni.alumni_id, alumni.alumni_name,
(SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id) AS contrib_alltime_total,
(SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id AND contribution.date >= 2002-10-10 AND contribution.date <= 2003-10-10) AS contrib_range_total

FROM alumni

WHERE hold_code IS NULL
AND (SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id AND contribution.date >= 2002-10-10 AND contribution.date <= 2003-10-10) >= 2000
AND (SELECT SUM(contributed_amt) FROM contribution WHERE contribution.alumni_id = alumni.alumni_id AND contribution.date >= 2002-10-10 AND contribution.date <= 2003-10-10) <= 3000

ORDER BY lname ASC

实现这一目标的最快和最有效的方法是什么？欢迎任何建议，谢谢您的时间。

Answer 1

SELECT a.alumni_id,
sum(c.contributed_amt) AS contrib_alltime_total,
sum(case when c.contribution_date BETWEEN '2002-10-10' AND '2003-10-10' then c.contributed_amt else 0 end) AS contrib_range_total
FROM alumni a
INNER JOIN contribution c ON c.alumni_id = a.alumni_id
WHERE a.hold_code IS NULL
GROUP BY a.alumni_id
HAVING contrib_range_total BETWEEN 2000 AND 3000

Answer 2

SELECT a.alumni_id, a.alumni_name, 
SUM(c.contributed_amt) AS contrib_alltime_total, 
SUM(c.contributed_amt) AS contrib_range_total

FROM alumni a, contribution c

WHERE a.hold_code IS NULL
AND c.alumni_id = a.alumni_id AND c.date BETWEEN '2002-10-10' AND '2003-10-10'
AND contrib_range_total BETWEEN 2000 AND 3000
ORDER BY lname ASC

Answer 3

我认为没有HAVING就不能使用GROUP BY，也可以使用AND连接多个HAVING案例.

GROUP BY contrib_range_total HAVING contrib_range_total >= 2000 AND contrib_range_total <= 3000