我需要一个 SQL 查询来检索 DATE 范围内的 SUM。
这是我的 sudo 代码：
SELECT da.Debit,da.Credit,
(SELECT SUM(daa.Debit) FROM Acc.DocumentAccount daa
INNER JOIN Acc.Document dd ON dd.Id = daa.DocumentId
WHERE dd.Date BETWEEN '2018-03-21' AND '2018-06-21' AND daa.Id=da.id) SumDebit
FROM Acc.DocumentAccount da
INNER JOIN Acc.Document d ON d.Id = da.DocumentId
WHERE d.Date BETWEEN '2018-06-21' AND '2019-03-21'
考虑以下示例数据：
CREATE TABLE Document(
Id INT,
[Date] date
);
INSERT INTO Document VALUES (1,'2018-01-01');
INSERT INTO Document VALUES (2,'2018-01-02');
INSERT INTO Document VALUES (3,'2018-07-01');
INSERT INTO Document VALUES (4,'2018-07-02');
CREATE TABLE DocumentAccount(
DocumentId INT,
Debit INT,
Credit INT
);
INSERT INTO DocumentAccount VALUES (1,100,200);
INSERT INTO DocumentAccount VALUES (2,150,300);
INSERT INTO DocumentAccount VALUES (3,150,300);
INSERT INTO DocumentAccount VALUES (4,160,310);
INSERT INTO DocumentAccount VALUES (4,170,320);
CREATE TABLE DocumentAccountDetail
(
DocumentAccountId INT,
DetailId INT
)
我需要这个硬编码数据：
select * from
(values (250, 500,0,0,480,0,0,0,930,0)) x(RemainDebit, RemainCredit,Q1Debit,Q2Debit,Q3Debit,Q4Debit,Q1Credit,Q2Credit,Q3Credit,Q4Credit)
RemainDebit是debit“2018-03-21”和“2018-06-21”之间的总和
RemainCredit是ANDcredit之间的总和'2018-03-21''2018-06-21'
Q1Debit在这种情况下是ANDdebit之间的总和，因为我的过滤器范围是 BETWEEN AND Q1Debit 是 0'2018-01-01''2018-03-30''2018-06-21''2019-03-21'
Q2Debit在这种情况下是ANDdebit之间的总和，因为我的过滤器范围是 BETWEEN AND Q1Debit 是 0'2018-04-01''2018-06-30''2018-06-21''2019-03-21'
Q3Debit在这种情况下是ANDdebit之间的总和，因为我的过滤器范围是 BETWEEN AND并且我们在 2018-07 年有 3 个文档，那么 Q1Debit 是 150+160+170=480'2018-06-01''2018-09-30''2018-06-21''2019-03-21'
等等...
根据您的问题，您不需要子查询select子句，您可以JOIN在两个表中使用并在where子句上设置日期范围
Select
Sum(debit) as Totledebit,
Sum(credit) as Totlecredit
From DocumentAccount da
INNER JOIN Document d on da.DocumentId = d.id
WHERE d.[Date] BETWEEN '2018-03-21' AND '2019-03-21'
编辑
我看到你编辑了你的问题，觉得有些逻辑很奇怪。但我相信，您可以尝试使用CASW WHEN表达式和Sum函数来实现您的期望。
Select
Sum(CASE WHEN d.[Date] between '2018-01-01' AND '2018-06-21' THEN debit ELSE 0 END) as Totledebit,
Sum(CASE WHEN d.[Date] BETWEEN '2018-01-01' AND '2018-06-21' THEN credit ELSE 0 END) as Totlecredit,
SUM(CASE WHEN d.[Date] between '2018-01-01' AND '2018-03-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN debit ELSE 0 END) as Q1Debit,
SUM(CASE WHEN d.[Date] between '2018-04-01' AND '2018-06-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN debit ELSE 0 END) as Q2Debit,
SUM(CASE WHEN d.[Date] between '2018-07-01' AND '2018-09-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN debit ELSE 0 END) as Q3Debit,
SUM(CASE WHEN d.[Date] between '2018-10-01' AND '2018-12-31' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN debit ELSE 0 END) as Q4Debit,
SUM(CASE WHEN d.[Date] between '2018-01-01' AND '2018-03-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN Credit ELSE 0 END) as Q1Credit,
SUM(CASE WHEN d.[Date] between '2018-04-01' AND '2018-06-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN Credit ELSE 0 END) as Q2Credit,
SUM(CASE WHEN d.[Date] between '2018-07-01' AND '2018-09-30' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN Credit ELSE 0 END) as Q3Credit,
SUM(CASE WHEN d.[Date] between '2018-10-01' AND '2018-12-31' AND d.[Date] BETWEEN '2018-06-21' AND '2019-03-21' THEN Credit ELSE 0 END) as Q4Credit
From DocumentAccount da
INNER JOIN Document d on da.DocumentId = d.id
sqlfiddle：https ://dbfiddle.uk/ ? rdbms = sqlserver_2017 & fiddle = de775aaf8c5e03b25c346dca3bd80e21
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句