从单列中根据多个条件选择多列

Question

我在数据库中有一个表，其中存储了金额，它们各自的'发票日期'，以及这些发票日期属于合同的哪一年。

客户有年度、双年度、季度和月度合同。因此，一个年度期间可以有多个发票-因此该列定义了发票属于哪一年。

它看起来像这样:

Amounttbody> <<tr>111222

contracactid	InvoiceNumber	InvoiceDate	YearIndex
1	1	01/01/2019	100	1
	2	01/06/2019	100	1
	3	01/01/2020	100	2
	4	01/06/2020	100	2
	1	01/03/2020	200	1
	2	01/03/2020	200	2
	3	01/03/2021	200	3
3	1	01/01/2020	300	1
3	2	01/04/2020	300	1
3	3	01/07/2020	300	1
3	4	01/10/2020	300	1

这是一般模式。您可以根据需要切换Access和Null与0的语法。

SELECT ContractID,
SUM(CASE WHEN YearIndex = 1 THEN Amount ELSE 0 END) AS Year1Amount,
SUM(CASE WHEN YearIndex = 2 THEN Amount ELSE 0 END) AS Year2Amount,
...
SUM(CASE WHEN YearIndex = N THEN Amount ELSE 0 END) AS YearNAmount
FROM SOME_TABLE
GROUP
BY ContactID

数据库不希望以并列的形式显示总和…为了达到这个目的，你会制造很多复杂的东西

现成的是:

groupID groupYear sumAmount

特别使用Access one使用了一个称为聚合查询的常见功能，您可以在网上找到该功能的教程

我只是想补充一下，Microsoft access不支持CASE语句，只支持IIF语句，然而，IIF语句不允许在其中包含聚合函数。因此，对于访问，最终的答案是:

SELECT 
DISTINCT (ContractInvoiceInfo.ContractID), 
Y1.Y1Amount, 
Y2.Y2Amount, 
Y3.Y3Amount, 
Y4.Y4Amount, 
Y5.Y5Amount
FROM 
ContractInvoiceInfo INNER JOIN (
(   
SELECT ContractID, SUM(Amount) AS Y1Amount 
FROM ContractInvoiceInfo 
WHERE YearIndex = 1 
GROUP BY ContractID
)  AS Y1 LEFT JOIN (
(   
SELECT ContractID, SUM(Amount) AS Y2Amount 
FROM ContractInvoiceInfo 
WHERE YearIndex = 2 
GROUP BY ContractID
)  AS Y2 LEFT JOIN (
(
SELECT ContractID, SUM(Amount) AS Y3Amount 
FROM ContractInvoiceInfo 
WHERE YearIndex = 3 
GROUP BY ContractID
)  AS Y3 LEFT JOIN (
(
SELECT ContractID, SUM(Amount) AS Y4Amount 
FROM ContractInvoiceInfo 
WHERE YearIndex = 4 
GROUP BY ContractID
)  AS Y4 LEFT JOIN (
SELECT ContractID, SUM(Amount) AS Y5Amount 
FROM ContractInvoiceInfo 
WHERE YearIndex = 5 
GROUP BY ContractID
)  AS Y5 
ON Y5.ContractID = Y4.ContractID)
ON Y4.ContractID = Y3.ContractID)
ON Y3.ContractID = Y2.ContractID)
ON Y2.ContractID = Y1.ContractID)
ON Y1.ContractID = ContractInvoiceInfo.ContractID
ORDER BY 
ContractInvoiceInfo.ContractID;