从一个表中选择给定年份中所有月份的记录，从第二个表中选择记录
我的数据库中有两个表：
MembershipInstallments （ProjectId，AllotmentId，InstallmentId，EntryDate，AmountPaid） Memberships （ProjectId，AllotmentId，ClientId，ClientName，RegistrationNo） 我想选择客户在一年内按月付款的方式。还有那年开始之前支付的金额。我想要的结果集是
假设在2019年：
ClientName, RegistrationNumbers, PreviouslyPaidAmount (i.e. all amounts paid in 2018), AmountPaidInJuly 2019, AmountPaidInAugust2019. ..... AmountPaidIn Jun2020 其中一个月是EntryDate从MembershipInstallments表。
我像这样尝试过，但是对于大型数据集，它花费的时间太长：
SELECT ClientName, RegistrationNo, (SELECT ISNULL(SUM(AmountPaid),0) FROM MembershipInstallments WHERE ProjectId = Membership.ProjectId AND AllotmentId = Membership.AllotmentId AND EntryDate < @fromDate) ASPreviouslyPaid, (SELECT ISNULL(SUM(AmountPaid), 0) FROM MembershipInstallments WHERE ProjectId = Membership.ProjectId AND AllotmentId = Memberhsip.AllotmentId AND (MONTH(EntryDate) = 7) AND (YEAR(EntryDate) = YEAR(@fromDate)) AS JulyPayment FROM Memberships WHERE ProjectId = @projectId 任何帮助将不胜感激。
问题来源于stack overflow
写回答
-
标量子查询往往无法优化。在您的情况下（相同的加入条件），您可以简单地利用条件聚合：
SELECT ms.ClientName,ms.RegistrationNo, Sum(CASE WHEN mi.EntryDate<@fromDate THEN mi.AmountPaid ELSE 0 end) AS PreviouslyPaid, Sum(CASE WHEN (Month(mi.EntryDate) = 7) AND (Year(mi.EntryDate) = Year(@fromDate)) THEN mi.AmountPaid ELSE 0) FROM Memberships AS ms LEFT JOIN MembershipInstallments AS mi -- don't know if Outer join is really needed ON mi.ProjectId=ms.ProjectId AND mi.AllotmentId=ms.AllotmentId WHERE ms.ProjectId=@projectId 您可能能够在加入之前进行聚合，即在CTE中进行聚合以进一步提高性能：
WITH cte AS ( SELECT
ProjectId, AllotmentId, Sum(CASE WHEN EntryDate<@fromDate THEN AmountPaid ELSE 0 END) AS PreviouslyPaid, Sum(CASE WHEN (Month(EntryDate) = 7) AND (Year(EntryDate) = Year(@fromDate)) THEN AmountPaid ELSE 0 END) AS JulyPayment FROM MembershipInstallments WHERE ProjectId=@projectId GROUP BY ProjectId, AllotmentId ) SELECT ms.ClientName, ms.RegistrationNo, cte.PreviouslyPaid, cte.JulyPayment FROM Memberships AS ms JOIN cte ON cte.ProjectId=ms.ProjectId AND cte.AllotmentId=ms.AllotmentId WHERE ms.ProjectId=@projectId 在这两种情况下，都要仔细检查结果是否正确（取决于这些表之间的实际关系）2019-11-18 11:44:19赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
