首先,我们需要使用SUM()函数获取每个客户的总购买价值和总折扣金额,并使用GROUP BY子句将查询结果按照年份进行分组。 可以使用如下SQL查询语句:
SELECT YEAR(OrderDate) AS Order_Year, Customers.CustomerName, SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) AS Total_Purchase_Value, SUM(OrderDetails.Discount) AS Total_Discounts FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID GROUP BY YEAR(OrderDate), Customers.CustomerName
这将返回每个客户在每个年份中的总购买价值和总折扣金额。
为了筛选出每个客户在每年的总购买价值和总折扣金额,我们可以使用如下SQL查询语句:
SELECT YEAR(OrderDate) AS Order_Year, Customers.CustomerName, SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) AS Total_Purchase_Value, SUM(OrderDetails.Discount) AS Total_Discounts FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID GROUP BY YEAR(OrderDate), Customers.CustomerName
这将返回按年份分类的客户、客户购买总价值和客户获得的折扣总额。