如何在SQLServer中根据日期计算行数？

0kjbasz6 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(346)

我有日期表：

我想通过计算表上2019-06-30到2020-03-30之间的行来显示多少行？输出应该是10行。我试过了

SELECT ln.datestart,
       ln.loanid,
       col.IDNo,
       ls.LoanID,
       max(CAST(col.DateOR AS date)) AS lastpayment,
       COUNT(ls.loanid) AS rowcount
FROM Collections AS col
INNER JOIN LoanSchedules AS ls ON ls.LoanID = col.IDNo
INNER JOIN Loans AS ln ON ln.LoanID = ls.LoanID
WHERE ls.DatePayment BETWEEN ln.DateStart AND max(col.DateOR)
GROUP BY ln.loanid,
         col.IDNo,
         ls.LoanID,
         ln.datestart

但它会返回一个错误：

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

附言： ln.datestart 来自记录每个客户的第一次付款计划的另一个表。我用 max(dateor) 从另一个表中知道最后的付款日期。

sql sql-server

来源：https://stackoverflow.com/questions/62257896/how-to-count-rows-based-on-date-in-sql-server

3条答案

按热度按时间

8wtpewkr1#

你必须使用 having 子句进行筛选时，

select ls.DatePayment, ln.datestart,ln.loanid,col.IDNo, ls.LoanID, max(CAST(col.DateOR as date)) as 
lastpayment,
COUNT(ls.loanid) as rowcount

from Collections as col
inner join LoanSchedules as ls 
on ls.LoanID = col.IDNo
inner join Loans as ln
on ln.LoanID = ls.LoanID
group by ls.DatePayment, ln.loanid,col.IDNo,ls.LoanID,ln.datestart
having ls.DatePayment between ln.DateStart and max(col.DateOR)

赞(0）回复(0）举报 2021-07-26

628mspwn2#

我不太清楚你想要得到什么结果，但我想你需要使用apply操作符。我试着复制你的案例，结果是：

create table #LOANS(
             LoanId           char(11) not null
            ,DatePayment      date not null
            ,PrincipalPayment numeric(11,2) not null
            ,InterestPayment  numeric(11,2) not null
            ,TotalPayment     numeric(11,2) not null
)

insert into #LOANS (LoanId, DatePayment, PrincipalPayment, InterestPayment, TotalPayment)
            values ('B 1905.0005', '2019-06-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-07-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-08-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-09-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-10-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-11-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2019-12-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2020-01-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2020-02-29', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2020-03-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2020-04-30', '5833.33', '2106.67', '7490.00')
                  ,('B 1905.0005', '2020-05-30', '5833.33', '2106.67', '7490.00')

select *
from   #LOANS l
       outer apply(select   COUNT(*) as rowNr
                   from     #LOANS ll
                   where    ll.LoanId = l.LoanId
                        and ll.DatePayment between '2019-06-30' and '2020-03-30'
                   group by LoanId) c

https://i.stack.imgur.com/9hxpc.png

赞(0）回复(0）举报 2021-07-26

5sxhfpxr3#

你的逻辑很难理解。显示一个数据表，但查询引用三个表。不过，我很肯定，你可以用一个窗口函数来做你想做的事情。只是不清楚你想要哪个窗口功能。我想：

SELECT datestart, loanid, cIDNo, LoanID,
       COUNT(*) AS rowcount
FROM (SELECT ln.datestart, ln.loanid, col.IDNo, ls.LoanID, ls.DatePayment,
             MAX(CAST(col.DateOR AS date)) OVER (PARTITION BY ln.datestart, ln.loanid, col.IDNo, ls.LoanID) as max_DateOR
      FROM Collections col JOIN
           LoanSchedules ls
           ON ls.LoanID = col.IDNo JOIN
           Loans ln
           ON ln.LoanID = ls.LoanID
     ) x
WHERE DatePayment BETWEEN ln.DateStart AND max_DateOR
GROUP BY loanid, IDNo, LoanID, datestart;

这假设您正在寻找基于四个参数的最大值 group by 钥匙。

赞(0）回复(0）举报 2021-07-26

我来回答

如何在SQLServer中根据日期计算行数？

3条答案

相关问题

热门标签

最新问答