时间戳

首页 > 解决方案 > 运行总 SQL Server 查询

sql-server - 运行总 SQL Server 查询

问题描述

到目前为止，我有以下 SQL Server 2005 查询：

WITH D AS (
SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m, CAST('2020-11-01' AS DATETIME) AS dt
UNION ALL
SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m, DATEADD(dd, 1, z.dt)
FROM D AS z
WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30')
SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x
LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m
LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m
GROUP BY x.d_y_m
ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

如何将另一列 (RunningTotal) 添加到查询中，将前一天的 (Invoiced-Paid) 结果汇总到今天的结果

例子：

d_y_m | Invoiced | Paid | RunningTotal

2020.11.01 | 24 | 5 | 19

2020.11.02 | 45 | 2 | 62

2020.11.03 | 10 | 20 | 52

2020.11.04 | 5 | 0 | 57

2020.11.05 | 0 | 10 | 47

标签： sql-serversql-server-2005cumulative-sumsql-cte

解决方案

关于您当前的解决方案的几点评论：

  • 不要使用“随机”表别名。D因为“日期”是有道理的。y对于“发票”没有。d_y_m也不符合您的日期格式。保持表和列别名有意义。
  • 不要在整个解决方案中拖动日期转换。使用日期值作为date类型，并在 final 中转换一次select值。
  • 不要在一个查询中对已开票金额和已支付金额的总和进行分组。如果您在一天内有多个发票或付款，那么总和将不正确！有关说明，请参阅底部的“额外”部分。
  • 让我们轻松为您提供帮助。下次，请提供我们可以复制粘贴的示例数据，而不必自己发明。
  • 自 2016 年 4 月 12 日起， SQL Server 2005正式不受支持。是时候寻找新版本了！

样本数据

create table Invoices
(
  InvoiceDate date,
  Total money
);

insert into Invoices (InvoiceDate, Total) values
('2020-11-01', 20),
('2020-11-01',  4),
('2020-11-02', 40),
('2020-11-02',  5),
('2020-11-03', 10),
('2020-11-04',  3),
('2020-11-04',  2);

create table Payments
(
  PaymentDate date,
  Total money
);

insert into Payments (PaymentDate, Total) values
('2020-11-01',  5),
('2020-11-02',  2),
('2020-11-03', 10),
('2020-11-03', 10),
('2020-11-05', 10);

解决方案

with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it.Invoiced as [Invoiced],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) as [CumInvoiced],
       pt.Paid as [Paid],
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [CumPaid],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) -
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [RunningTotal]
from DateRange dr
join InvoicedTotal it
  on it.DateValue = dr.DateValue
join PaidTotal pt
  on pt.DateValue = dr.DateValue
order by dr.DateValue;

结果

仅列出 11 月 30 行中的前 10 行。

YYYY.MM.DD Invoiced CumInvoiced Paid    CumPaid RunningTotal
---------- -------- ----------- ------- ------- ------------
2020.11.01  24.0000     24.0000  5.0000  5.0000      19.0000
2020.11.02  45.0000     69.0000  2.0000  7.0000      62.0000
2020.11.03  10.0000     79.0000 20.0000 27.0000      52.0000
2020.11.04   5.0000     84.0000  0.0000 27.0000      57.0000
2020.11.05   0.0000     84.0000 10.0000 37.0000      47.0000
2020.11.06   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.07   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.08   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.09   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.10   0.0000     84.0000  0.0000 37.0000      47.0000

小提琴以查看它的实际效果。

额外：为什么不在一个查询中计算两个总数。

使用相同的示例数据，您可以运行此查询以放大特定日期，此处：2020-11-01。在该日期，样本数据有 2 张发票和 1 笔付款。

with DateRange as
(
  select '2020-11-01' as DateValue -- filtering data to explain
)
select dr.DateValue,
       isnull(sum(i.Total), 0) as Invoiced,
       isnull(sum(p.Total), 0) as Paid
from DateRange dr
left join Invoices i
  on i.InvoiceDate = dr.DateValue
left join Payments p
  on p.PaymentDate = dr.DateValue
group by dr.DateValue
order by dr.DateValue;

只需执行连接就会给你下面的结果。由于合并left join，付款行列出了两次！

dr.DateValue | i.Total | p.Total
------------ | ------- | -------
2020-11-01   |      20 |       5
2020-11-01   |       4 |       5 --> payment row got joined TWICE

将这些行加起来会得出当天的付款金额无效。

group by dr.DateValue | sum(i.Total) | sum(p.Total)
--------------------- | ------------ | ------------
2020-11-01            |           24 |           10 --> last sum is WRONG !

编辑：SQL Server 2005 版本与cross apply. 但还是建议更新 SQL Server 版本！

with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it1.Invoiced as [Invoiced],
       it3.Invoiced as [CumInvoiced],
       pt1.Paid as [Paid],
       pt3.Paid as [CumPaid],
       it3.Invoiced - pt3.Paid as [RunningTotal]
from DateRange dr
join InvoicedTotal it1
  on it1.DateValue = dr.DateValue
join PaidTotal pt1
  on pt1.DateValue = dr.DateValue
cross apply ( select sum(it2.Invoiced) as Invoiced
              from InvoicedTotal it2
              where it2.DateValue <= dr.DateValue ) it3
cross apply ( select sum(pt2.Paid) as Paid
              from PaidTotal pt2
              where pt2.DateValue <= dr.DateValue ) pt3
order by dr.DateValue;

更新了小提琴。

推荐阅读