这有点难以解释但假设我有这个表
DateTime ID# Type Cost
8/1 8pm 1 Activate 2
8/1 8pm 1 Add 15
8/1 10am 1 Add 30
8/1 1pm 1 Use NULL
8/5 6pm 1 Use NULL
8/5 11pm 1 Add 14
8/12 9am 1 Use NULL
8/15 11am 1 Add 100
8/15 1130am 1 Add 10
8/15 5pm 1 Use NULL
8/1 8pm 2 Activate 2
8/1 8pm 2 Add 15
8/1 10am 2 Add 30
8/1 1pm 2 Use NULL
8/3 6pm 2 Use NULL
8/3 12pm 2 Add 14
8/12 9am 2 Use NULL
8/15 11am 2 Add 90
8/18 5pm 2 Use NULL
我按CardNumber分组
1
2
Then I do:
SELECT CardNumber
,SUM(Case when Type = 'Add' then Cost ELSE 0 END) as totalMoneyAdd
FROM Table
Group by CardNumber
1 169
2 105
现在我想添加另一个列,不使用内部查询或嵌套查询,以便每个ID#它将有一个汇总和列,它将只取每个ID#每天的初始添加的总和。
例如,ID#1在8/1、8/5、8/12、8/15中处于激活状态。如果你仔细看,在8月1日,Id#1的初始"加"是15美元,8月5日的初始"加"是14美元,8月12日的初始"加"是0美元,8月15日的初始"加"是100美元。加上所有这些首字母,ID#1要129美元。同样的计算也适用于Id#2
所以总的来说我想:
1 169 129
2 115 119
我想要实现这一点,而不使用内部查询或嵌套查询,这意味着只添加一个列,如
SELECT CardNumber
,SUM(Case when Type = 'Add' then Cost ELSE 0 END) as totalMoneyAdd
--,Add calculations here to calculate this new column
FROM Table
Group by CardNumber
我正在考虑使用某种分区,但这需要我在from子句中创建一个嵌套查询或查询。我想知道这是否可以实现。
是的,你是对的,你需要使用分区by来解决这个问题。就内部连接或嵌套sql而言,您需要对这些类型的查询使用嵌套查询。我的答案不匹配,因为我认为你的计算有些错误。我为您创建了两个示例查询。第一个有内部查询,第二个没有内部查询,如下所示。我把日期和时间分成两列,因为我没有物理表。请进行必要的转换。在第二个查询的情况下,它没有按照您的要求给出结果集,但请检查您是否可以在您的情况下使用它,因为您只需要按id分组并求和其他列以获得所需的结果。
查询1:select id,sum(cost) totalmoneyadd, sum(totalmoneyinit) totalmoneyinit
from
(
select
id
,cost
, case when rank() over (partition by id,date order by id,date,time) =1 then cost else null end totalmoneyinit
from
(
select '8/1' date,20 time, 1 id,'Activate' type, 2 cost
union
select '8/1',20, 1,'Add', 15
union
select '8/1',10, 1,'Add', 30
union
select '8/1',13, 1,'Use', NULL
union
select '8/5',18, 1,'Use', NULL
union
select '8/5',23, 1,'Add', 14
union
select '8/12', 9, 1,'Use', NULL
union
select '8/15', 11, 1, 'Add', 100
union
select '8/15', 11.5, 1,'Add', 10
union
select '8/15',17, 1 , 'Use', NULL
union
select '8/1',20, 2,'Activate', 2
union
select '8/1',20, 2 ,'Add', 15
union
select '8/1', 10, 2,'Add', 30
union
select '8/1',13, 2,'Use', NULL
union
select '8/3',18, 2,'Use', NULL
union
select '8/3', 12, 2 ,'Add', 14
union
select '8/12', 9, 2 ,'Use', NULL
union
select '8/15', 11, 2 ,'Add', 90
union
select '8/18', 17, 2 ,'Use', NULL
)A
where type='Add'
) B
group by id
查询2:select
case when rank() over (partition by id,date order by id,date,time) =1 then id else null end id
,case when rank() over (partition by id,date order by id,date,time) =1 then sum(cost) over (partition by id,date order by id,date,time desc) else null end totalmoneyadd
, case when rank() over (partition by id,date order by id,date,time) =1 then cost else null end totalmoneyinit
from
(
select '8/1' date,20 time, 1 id,'Activate' type, 2 cost
union
select '8/1',20, 1,'Add', 15
union
select '8/1',10, 1,'Add', 30
union
select '8/1',13, 1,'Use', NULL
union
select '8/5',18, 1,'Use', NULL
union
select '8/5',23, 1,'Add', 14
union
select '8/12', 9, 1,'Use', NULL
union
select '8/15', 11, 1, 'Add', 100
union
select '8/15', 11.5, 1,'Add', 10
union
select '8/15',17, 1 , 'Use', NULL
union
select '8/1',20, 2,'Activate', 2
union
select '8/1',20, 2 ,'Add', 15
union
select '8/1', 10, 2,'Add', 30
union
select '8/1',13, 2,'Use', NULL
union
select '8/3',18, 2,'Use', NULL
union
select '8/3', 12, 2 ,'Add', 14
union
select '8/12', 9, 2 ,'Use', NULL
union
select '8/15', 11, 2 ,'Add', 90
union
select '8/18', 17, 2 ,'Use', NULL
)A
where type='Add'