如何获取在特定时间段内付款的客户或客户数量

Question

我有两个表学生表和付款 我想获取特定班级中在特定学期内付款的学生人数 这是我的代码

SELECT classform, student_id, COUNT(student_id) as countreport_class
FROM studentstable s JOIN
(SELECT studentid
FROM payment
WHERE term= ? AND acayear=?
) AS pa
ON pa.studentid = s.student_id AND s.classform=? 
GROUP BY s.student_id 

但结果是错误的。它反而显示学生付款的次数...... 我需要帮助。提前谢谢。

Answer 1

我期待这样的事情：

SELECT COUNT(*) as countreport_class
FROM studentstable s JOIN
payment p
ON p.studentid = s.student_id AND
p.term = ? AND p.acayear = ?
WHERE s.classform = ? ;

如果您希望查询返回单个数字，为什么要在select中返回这么多列？

以上计算了付款次数。 如果您想要付款的学生人数，请使用exists：

SELECT COUNT(*) as countreport_class
FROM studentstable s 
WHERE EXISTS (SELECT 1
FROM payment p
WHERE p.studentid = s.student_id AND
p.term = ? AND p.acayear = ?
) AND
s.classform = ? ;

Answer 2

此查询

SELECT distinct studentid
FROM payment
WHERE term = ? AND acayear = ?

返回在此学期至少支付了 1 笔款项的所有学生。
因此，如果您将学生表联接到此查询并应用特定类的条件，您将得到您需要的内容：

SELECT COUNT(*) as countreport_class
FROM studentstable s INNER JOIN (
SELECT distinct studentid
FROM payment
WHERE term = ? AND acayear = ?
) AS pa ON pa.studentid = s.studentid 
WHERE s.classform = ? 

观看演示