如何在同一条语句中显示1月，2月3月4日的销售额？

debugcn 发表于 Dev

托尼

我正在尝试制作一份可以并排显示销售情况的报告，但是我目前只能获得每张纸一个月的销售量。

select c.[AR CUST Customer ID],c.[AR CUST Name], sum([AR SALE Subtotal]) as Jan_2013_Sales
from [AR Customer] c
 join [AR Sales] s
  on c.[AR CUST Customer ID]=s.[AR SALE Customer ID]
where [AR SALE Date] >= '1-01-2013' and [AR SALE Date] < '2-01-2013' and [AR SALE Document Type] like 'invoice'
group by c.[AR CUST Customer ID],c.[AR CUST Name]
order by [AR CUST Customer ID]

当前，这是我得到的输出：

AR Cust Customer ID   |   AR Cust Name  |   Jan_2013_Sales

040891                |    Dans         |      268.8

041473                |    Dicks        |       2846.74

很好，但我希望能够从同一查询中提取：

AR Cust Customer ID   |   AR Cust Name  |   Jan_2013_Sales |  Feb_2013_Sales

040891                |    Dans         |       268.8      |     354.54

041473                |    Dicks        |       2846.74    |     1564.53

戴夫·克

如果您确切地知道要运行该报表的月份，则可以通过这种方式进行...

select c.[AR CUST Customer ID],c.[AR CUST Name], jan.sumJan, feb.sumfeb, march.sumMarch
from [AR Customer] c
 left join (select [AR SALE Customer ID], sum([AR SALE Subtotal]) as sumJan from [AR Sales]
        where [AR SALE Date] >= '1-01-2013' and [AR SALE Date] < '2-01-2013' and [AR SALE     Document Type] like 'invoice'
        group by [AR SALE Customer ID]) jan on jan.[AR SALE Customer ID] = c.[AR CUST Customer ID]
 left join (select [AR SALE Customer ID], sum([AR SALE Subtotal]) as sumFeb from [AR Sales]
        where [AR SALE Date] >= '2-01-2013' and [AR SALE Date] < '3-01-2013' and [AR SALE     Document Type] like 'invoice'
        group by [AR SALE Customer ID]) Feb on Feb.[AR SALE Customer ID] = c.[AR CUST Customer ID]
 left join (select [AR SALE Customer ID], sum([AR SALE Subtotal]) as sumMarch from [AR Sales]
        where [AR SALE Date] >= '3-01-2013' and [AR SALE Date] < '4-01-2013' and [AR SALE     Document Type] like 'invoice'
        group by [AR SALE Customer ID]) March on March.[AR SALE Customer ID] = c.[AR CUST Customer ID]
order by [AR CUST Customer ID]

否则，您需要一个数据透视表，这是codeProject.com上的一个示例

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。