复杂的组SQL查询
问题描述:
这里是我的查询和结果;复杂的组SQL查询
select
DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0) as Date,
Stores.Name StoreName,
SUM(PosCash + PosCredit + PosBillCash + PosBillCredit) as Revenue
from StoreRevenue
inner join Stores on Stores.ID = StoreRevenue.StoreID
group by DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0), Stores.Name
我想创建一个表(结果),必须是按日期和商店名称组。
Date, Avcılar Mağaza, Ataşehir Mağaza
2013-03-04, 150, 200
2013-03-05, 200, 250
2013-03-06, 300, 150
行子组(这些的:2013年3月4日,150,200)的日期和收入我想那种结果 的每个门店 的我也曾尝试“支点”在SQL中,但它不适用于我
对不起,我的英语。 -Thanks
答
可以使用PIVOT
函数从行变换的数据列:
select *
from
(
select
DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0) as Date,
Stores.Name StoreName,
(PosCash + PosCredit + PosBillCash + PosBillCredit) as Revenue
from StoreRevenue
inner join Stores
on Stores.ID = StoreRevenue.StoreID
) d
pivot
(
sum(Revenue)
for StoreName in ([Avcılar Mağaza], [Ataşehir Mağaza]..)
) piv;
或者你可以使用聚合函数与CASE
:
select DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0) as Date,
sum(case when Stores.Name = 'Avcılar Mağaza'
then PosCash + PosCredit + PosBillCash + PosBillCredit end) as [Avcılar Mağaza],
sum(case when Stores.Name = 'Ataşehir Mağaza'
then PosCash + PosCredit + PosBillCash + PosBillCredit end) as [Ataşehir Mağaza]
from StoreRevenue
inner join Stores
on Stores.ID = StoreRevenue.StoreID
group by DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0)
如果你有一个未知的数字fo店,那么你可以SE动态SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name)
from Stores
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Date, ' + @cols + '
from
(
select
DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0) as Date,
Stores.Name StoreName,
(PosCash + PosCredit + PosBillCash + PosBillCredit) as Revenue
from StoreRevenue
inner join Stores
on Stores.ID = StoreRevenue.StoreID
) x
pivot
(
sum(Revenue)
for StoreName in (' + @cols + ')
) p '
execute(@query)
答
试试这个:
WITH CTE
AS
(
select
DATEADD(dd, DATEDIFF(dd, 0, PostDate), 0) as Date,
Stores.Name StoreName,
PosCash + PosCredit + PosBillCash + PosBillCredit as Revenue
from StoreRevenue
inner join Stores on Stores.ID = StoreRevenue.StoreID
)
SELECT *
FROM CTE AS c
PIVOT
(
SUM(Revenue)
FOR StoreName IN ([Avcılar Mağaza], [Ataşehir Mağaza], ...)
) AS p
它可以如何动态存储名称? – ertan2002 2013-04-05 13:23:49
@ user2107255是的,如果商店名称未知,则可以使用动态SQL。查看我的编辑 – Taryn 2013-04-05 13:23:50
@ user2107255不客气! – Taryn 2013-04-05 13:34:27