如题,各位大侠帮写个sql语句,要求是:逐日累加,然后排名。
如下表:
表1
员工姓名 销售金额 销售时间
员工A 10 2011-9-21 00:56:01
员工A 15 2011-9-11 11:56:01
员工A 30 2011-9-12 23:56:01
员工A 10 2011-9-4 11:56:01
员工B 10 2011-9-11 05:56:01
员工B 10 2011-9-12 05:56:01
员工B 30 2011-9-4 08:56:01
员工B 10 2011-9-21 05:56:01结果
销售时间 员工姓名 销售金额 名次
2011-9-4 11:56:01 员工A 10 22011-9-11 11:56:01 员工A 25 22011-9-12 11:56:01 员工A 55 12011-9-21 11:56:01 员工A 65 1
如下表:
表1
员工姓名 销售金额 销售时间
员工A 10 2011-9-21 00:56:01
员工A 15 2011-9-11 11:56:01
员工A 30 2011-9-12 23:56:01
员工A 10 2011-9-4 11:56:01
员工B 10 2011-9-11 05:56:01
员工B 10 2011-9-12 05:56:01
员工B 30 2011-9-4 08:56:01
员工B 10 2011-9-21 05:56:01结果
销售时间 员工姓名 销售金额 名次
2011-9-4 11:56:01 员工A 10 22011-9-11 11:56:01 员工A 25 22011-9-12 11:56:01 员工A 55 12011-9-21 11:56:01 员工A 65 1
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([员工姓名] VARCHAR(5),[销售金额] INT,[销售时间] DATETIME)
INSERT [tb]
SELECT '员工A',10,'2011-9-21 00:56:01' UNION ALL
SELECT '员工A',15,'2011-9-11 11:56:01' UNION ALL
SELECT '员工A',30,'2011-9-12 23:56:01' UNION ALL
SELECT '员工A',10,'2011-9-4 11:56:01' UNION ALL
SELECT '员工B',10,'2011-9-11 05:56:01' UNION ALL
SELECT '员工B',10,'2011-9-12 05:56:01' UNION ALL
SELECT '员工B',30,'2011-9-4 08:56:01' UNION ALL
SELECT '员工B',10,'2011-9-21 05:56:01'
--------------开始查询--------------------------SELECT [员工姓名] , [销售时间] , [销售金额] = (
SELECT SUM ([销售金额]) FROM [tb] WHERE [员工姓名]= t.[员工姓名]
AND [销售时间]<= t.[销售时间]
)
FROM [tb] AS t
ORDER BY [员工姓名] , [销售时间]
----------------结果----------------------------
/*员工姓名 销售时间 销售金额
员工A 2011-09-04 11:56:01.000 10
员工A 2011-09-11 11:56:01.000 25
员工A 2011-09-12 23:56:01.000 55
员工A 2011-09-21 00:56:01.000 65
员工B 2011-09-04 08:56:01.000 30
员工B 2011-09-11 05:56:01.000 40
员工B 2011-09-12 05:56:01.000 50
员工B 2011-09-21 05:56:01.000 60
*/
insert into tb
select '员工A', 10, '2011-9-21 00:56:01' union
select '员工A', 15, '2011-9-11 11:56:01' union
select '员工A', 30, '2011-9-12 23:56:01' union
select '员工A', 10, '2011-9-4 11:56:01' union
select '员工B', 10, '2011-9-11 05:56:01' union
select '员工B', 10, '2011-9-12 05:56:01' union
select '员工B', 30, '2011-9-4 08:56:01' union
select '员工B', 10, '2011-9-21 05:56:01' ;with cte
as
(
select *,cnt=(select sum(销售金额) from tb where 员工姓名=t.员工姓名 and 销售时间<=t.销售时间)
from tb t
),
cte2 as
(select *,pi=row_number() over (partition by convert(char(10),销售时间,120) order by cnt)
from cte)
select * from cte2
go
create table [tb]([员工姓名] varchar(5),[销售金额] int,[销售时间] datetime)
insert [tb]
select '员工A',10,'2011-9-21 00:56:01' union all
select '员工A',15,'2011-9-11 11:56:01' union all
select '员工A',30,'2011-9-12 23:56:01' union all
select '员工A',10,'2011-9-4 11:56:01' union all
select '员工B',10,'2011-9-11 05:56:01' union all
select '员工B',10,'2011-9-12 05:56:01' union all
select '员工B',30,'2011-9-4 08:56:01' union all
select '员工B',10,'2011-9-21 05:56:01'
goselect *
from (
select *,名次=row_number() over(partition by convert(varchar(10),销售时间,120) order by 销售金额 desc)
from
(
select 销售时间,员工姓名,
销售金额=(select sum(销售金额) from tb where 员工姓名=a.员工姓名 and 销售时间<=a.销售时间)
from tb a
) b
) c
where 员工姓名='员工A'/**
销售时间 员工姓名 销售金额 名次
----------------------- ----- ----------- --------------------
2011-09-04 11:56:01.000 员工A 10 2
2011-09-11 11:56:01.000 员工A 25 2
2011-09-12 23:56:01.000 员工A 55 1
2011-09-21 00:56:01.000 员工A 65 1(4 行受影响)
**/
你好,Josy, 我的sql是2000的,没有row_number() 这个函数
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([员工姓名] VARCHAR(5),[销售金额] INT,[销售时间] DATETIME)
INSERT [tb]
SELECT '员工A',10,'2011-9-21 00:56:01' UNION ALL
SELECT '员工A',15,'2011-9-11 11:56:01' UNION ALL
SELECT '员工A',30,'2011-9-12 23:56:01' UNION ALL
SELECT '员工A',10,'2011-9-4 11:56:01' UNION ALL
SELECT '员工B',10,'2011-9-11 05:56:01' UNION ALL
SELECT '员工B',10,'2011-9-12 05:56:01' UNION ALL
SELECT '员工B',30,'2011-9-4 08:56:01' UNION ALL
SELECT '员工B',10,'2011-9-21 05:56:01'
--------------开始查询--------------------------SELECT * , [名次]=(
SELECT COUNT(*)+1
FROM (
SELECT [员工姓名] , [销售时间] , [销售金额] = (SELECT SUM ([销售金额]) FROM [tb] WHERE [员工姓名]= b.[员工姓名] AND [销售时间]<= b.[销售时间])
FROM [tb] AS b
) a
WHERE CONVERT(VARCHAR(10) , 销售时间 , 120) = CONVERT(VARCHAR(10) , b.销售时间 , 120)
AND [销售金额] > b.[销售金额]
)
FROM (
SELECT [员工姓名] ,
[销售时间] ,
[销售金额] = (SELECT SUM ([销售金额]) FROM [tb] WHERE [员工姓名]= t.[员工姓名] AND [销售时间]<= t.[销售时间])
FROM [tb] AS t
) b
ORDER BY [员工姓名] , [销售时间]
/*
员工姓名 销售时间 销售金额 名次
员工A 2011-09-04 11:56:01.000 10 2
员工A 2011-09-11 11:56:01.000 25 2
员工A 2011-09-12 23:56:01.000 55 1
员工A 2011-09-21 00:56:01.000 65 1
员工B 2011-09-04 08:56:01.000 30 1
员工B 2011-09-11 05:56:01.000 40 1
员工B 2011-09-12 05:56:01.000 50 2
员工B 2011-09-21 05:56:01.000 60 2
*/