首页 文章

使用T-SQL构建去年的客户报告

提问于
浏览 次
2

我有3张 table （简化）：

-----------Orders--------------------
Id | Total_Price | Customer_Id | Date

--------Order Details---------------------
Id | Order_Id | Product Name | Qty | Value

----Customers------
Id | Name | Address

我使用此查询获取单个客户的总订单 Value ：

SELECT C.ID, C.NAME , SUM(O.TOTAL_PRICE)
FROM CUSTOMERS C
JOIN ORDERS O ON O.CUSTOMER_ID = C.ID
GROUP BY C.ID, C.NAME

现在，我想构建一个报告，其总订单 Value 按一系列日期过滤：

SELECT C.ID, C.NAME , SUM(O.TOTAL_PRICE)
FROM CUSTOMERS C
JOIN ORDERS O ON O.CUSTOMER_ID = C.ID
WHERE O.DATE BETWEEN @value1 AND @value2
GROUP BY C.ID, C.NAME

这项工作正常，但我想选择按客户分组的总订单 Value 的最后3年总和，这是我想要的结果：

1Year | 2Year | 3Year | Customer_Name
    ------------------------------------------------- 
        XXX   | YYY   | ZZZZ  | Customer1
        XYX   | YYZ   | ZZTZ  | Customer2
....

我有这个基数：带有22.000行的客户表订单表有87.000行订单详细信息有600.000

It is possible without temptable,vartable or stored procedure with long execution time?

在我的报告中，我还要计算按产品客户分组的过去3年的总数量，但这是下一步 . 有任何想法吗？谢谢

sql sql-server tsql

2 回答

  • 1

    您可以使用case语句来获得所需的结果 . 由于你的帖子中关于如何定义年份范围存在一些模糊性，我已经遗漏了任何计算以获得那些年末/开始并且只是放入变量 . 你可以根据自己的需要进行修改 .

    SELECT C.ID
    ,C.NAME
    ,SUM(CASE 
            WHEN o.DATE BETWEEN @year1start
                    AND @year1end
                THEN O.TOTAL_PRICE
            ELSE 0
            END) Year1
    ,SUM(CASE 
            WHEN o.DATE BETWEEN @year2start
                    AND @year2end
                THEN O.TOTAL_PRICE
            ELSE 0
            END) Year2
    ,SUM(CASE 
            WHEN o.DATE BETWEEN @year3start
                    AND @year3end
                THEN O.TOTAL_PRICE
            ELSE 0
            END) Year3
FROM CUSTOMERS C
INNER JOIN ORDERS O ON O.CUSTOMER_ID = C.ID
GROUP BY C.ID
    ,C.NAME
    回复于
  • 3

    另一种选择是使用pivot语句 . 我假设您的每个日期范围等于一年（例如2013年，2014年等） .
    如果这些年是强烈确定的枢轴不是很漂亮的选项（look at full sqlfiddle example, it has possible solution for your additional question）：

    select
    c.Id, c.Name, c.Address, CostByYear.[2013], CostByYear.[2014], CostByYear.[2015]
from Customers c
left join (
    select
        pt.Customer_Id, isnull(pt.[2013], 0) as [2013], 
        isnull(pt.[2014], 0) as [2014], isnull(pt.[2015], 0) as [2015]
    from (
        select
            o.Customer_Id, year(o.Date) [Year], sum(o.Total_Price) [TotalCost]
        from Orders o
        group by
            o.Customer_Id, year(o.Date)
    ) src
    pivot (
        sum(TotalCost) for [Year] in ([2013], [2014], [2015])
    ) pt
) CostByYear on
    c.Id = CostByYear.Customer_Id
order by
    c.Name

    如果年份范围未知且强烈定义，您还可以使用动态创建的查询执行两种方法（我的和普通答案） .

    回复于

相关问题