create table Total_Sales([name] varchar(10),Sales int)
insert into Total_Sales
select 'John',10 union all
select 'Jennifer',15 union all
select 'Stella',20 union all
select 'Sophia',40 union all
select 'Greg',50 union all
select 'Jeff',20
Total_Sales 表格
-----------------
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
------------------
执行:
SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;结果:
Name Sales Running_Total
Greg 50 50
Sophia 40 90
Stella 20 110
Jeff 20 130
Jennifer 15 145
John 10 155
insert into Total_Sales
select 'John',10 union all
select 'Jennifer',15 union all
select 'Stella',20 union all
select 'Sophia',40 union all
select 'Greg',50 union all
select 'Jeff',20
Total_Sales 表格
-----------------
Name Sales
John 10
Jennifer 15
Stella 20
Sophia 40
Greg 50
Jeff 20
------------------
执行:
SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;结果:
Name Sales Running_Total
Greg 50 50
Sophia 40 90
Stella 20 110
Jeff 20 130
Jennifer 15 145
John 10 155
SELECT a1.Name, a1.Sales, a2.Sales
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)