试图在截止日期前吸引逾期会员

Question

这是实际的查询

Select members.member_Id, membertomships.memberToMship_DueDay, sum(memberToMship_ChargePerPeriod)-sum(memberAccTran_Value) as StillDue 
from membertomships 
left join mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipOption_Id
left join members on membertomships.member_Id = members.member_Id
left join memberacctrans on memberacctrans.member_Id = members.member_Id
WHERE sum(if(memberToMship_DueDay<CURDATE(),0,memberToMship_ChargePerPeriod))>sum(memberAccTran_Value);

修改后的查询：

Select 
    members.member_Id, 
    membertomships.memberToMship_DueDay, 
    sum(sum(memberToMship_InductionFee+memberToMship_JoinFee + (IF(mshipOption_Period='year', TIMESTAMPDIFF (YEAR ,memberToMship_StartDate, memberToMship_EndDate), TIMESTAMPDIFF (MONTH ,memberToMship_StartDate, memberToMship_EndDate)) * memberToMship_ChargePerPeriod))) - sum(memberAccTran_Value) as StillDue 
from membertomships 
left join mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipOption_Id
left join members on membertomships.member_Id = members.member_Id
left join memberacctrans on memberacctrans.member_Id = members.member_Id
group by member_Id
having sum(if(memberToMship_DueDay<today(),0,memberToMship_ChargePerPeriod))>sum(memberAccTran_Value);

注意： paymenttable = memberacctrans 表

得到了这样的错误，

                          Error Code: 1111
                         Invalid use of group function

任何人都可以帮助我解决这个查询

Answer 1

如果您愿意存储，您将使一切变得更加容易
每笔付款还涉及哪条会员记录！

尝试以下 SQL 查询来显示所有未结费用：

Select member_id, max(membertomship_dueday), sum(membertomship_Totalfee)-sum(payment_Money) as StillDue
from membertomship 
left join member_table on membertomship.memberID = member_table.member_id
left join payments on payment_member_id = member_table.member_id
group by member_id
having sum(membertomship_Totalfee)>sum(payment_Money)

从那里您可以改进它以仅计算已到期的费用：
（我不知道你使用哪个数据库服务器，所以 IF 的语法可能是
不正确！）

Select member_id, (membertomship_dueday), sum(membertomship_Totalfee)-sum(payment_Money) as StillDue 
from membertomship 
left join member_table on membertomship.memberID = member_table.member_id
left join payments on payment_member_id = member_table.member_id
group by member_id
having sum(if(membertomship_dueday<today(),0,membertomship_Totalfee))>sum(payment_Money)

Answer 2

不知道表中的数据类型并假设您对日期字段使用简单的“日期”类型，您可以简单地执行以下操作：（注意您在哪里看到这是一个创建的字段，您可以重命名它们）

SELECT
member_overdue.member_id,
member_overdue.member_firstname,
member_overdue.member_lastname,
DATEDIFF(CURDATE(),membertomship.membertomship_dueday) AS days_diff
FROM
membertomship
Inner Join member_table AS member_overdue ON member_overdue.member_id = membertomship.member_Id
WHERE
membertomship.membertomship_dueday <  CURDATE()

现在使用此数据在member_table中设置：

member_id    member_firstname    member_lastname
1            brandon             s
2            sally               s
3            gregg               s

并且membertomship的这个数据集：

membertomship_id    membertomship_StartDate membertomship_enddate   membertomship_Totalfee  membertomship_dueday    membertomship_paymethod member_Id
1                   6/1/2011                7/1/2011                45                  7/1/2011                cash                      1
2                   7/1/2010                8/3/2011                45                  8/3/2011                cc                        2
3                   1/1/2009                5/1/2011                45                  5/1/2011                cc                        3

给了我这个结果集：

member_id    member_firstname    member_lastname    days_diff
1            brandon             s                  10
3            gregg               s                  71

记住select语句member_overdue是我在内连接中设置的别名，这里不需要左和右，并且只要满足以下条件，您的其他表就无关紧要付款也更新成员资格表。如果这是真的，那么这个简单的查询就是您所需要的。