我有两个表：
+-----------+
| Customer |
+-----------+
| ID | Name |
+----+------+
| 1 | Jack |
+----+------+
| 2 | John |
+----+------+
+----------------------------------------+
| Bill |
+----------------------------------------+
| ID | Customer_ID | date | amount |
+----+-------------+------------+--------+
| 1 | 1 | 01.01.2015 | 10$ |
+----+-------------+------------+--------+
| 2 | 1 | 01.01.2014 | 20$ |
+----+-------------+------------+--------+
| 3 | 2 | 01.01.2015 | 5$ |
+----+-------------+------------+--------+
| 4 | 2 | 01.02.2015 | 50$ |
+----+-------------+------------+--------+
| 5 | 2 | 01.01.2014 | 15$ |
+----+-------------+------------+--------+
我需要知道一位客户一年中收到的所有账单的总和。
这很简单：
SELECT
SUM(Bill.amount), Customer.Name
FROM
Customer
INNER JOIN
Bill ON Customer.ID = Bill.Customer_ID
WHERE
Bill.date BETWEEN #20150101# AND #20151231#
GROUP BY
Customer.Name
困难的部分是我需要在单个表中显示多年的查询结果，如下所示：
+-------------------------------------------+
| sales to customer |
+-------------------------------------------+
| Customer_ID | Customer_Name | 2015 | 2014 |
+-------------+---------------+------+------+
| 1 | jack | 10$ | 20$ |
+-------------+---------------+------+------+
| 2 | john | 55$ | 20$ |
+-------------+---------------+------+------+
我正在使用SQL Server 2005。
我非常感谢您的每一个回答。
真诚的安达哈里
如前所述，您需要使用PIVOT才能获得所需的结果，如下所示：
Select Customer_ID, Customer_Name, [2015], [2014]
from
(select Customer_ID, Name Customer_Name, YEAR(_date) Yr, amount
from Bill b
inner join Customer c on c.ID = b.Customer_ID
) as src
PIVOT
(SUM(amount) for Yr in ([2015],[2014])) as pvt
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句