表1员工姓名 销售金额 销售时间
员工A 10 2011-8-3 00:56:01
员工C 15 2011-9-1 11:56:01
员工D 20 2011-9-2 23:56:01
员工B 10 2011-9-3 11:56:01
员工C 30 2011-9-4 08:56:01
员工D 10 2011-7-6 05:56:01怎么得出9月份内每个员工销售总额,并对每个员工的销售总额排名!mssql 2000结果员工姓名 销售金额 名次
员工C 35 1
员工D 20 2
员工B 10 3
员工A 10 2011-8-3 00:56:01
员工C 15 2011-9-1 11:56:01
员工D 20 2011-9-2 23:56:01
员工B 10 2011-9-3 11:56:01
员工C 30 2011-9-4 08:56:01
员工D 10 2011-7-6 05:56:01怎么得出9月份内每个员工销售总额,并对每个员工的销售总额排名!mssql 2000结果员工姓名 销售金额 名次
员工C 35 1
员工D 20 2
员工B 10 3
解决方案 »
- 找SQL共同学习的朋友
- 在客户端复制SQLServer中除表数据外的所有定义信息,如表结构、外键,存存储过程,视图等。
- 使用sql查询链接服务器所有的数据库、用户和表
- 如何用存储过程在数据中添加用户,并赋于登陆数据库的权限?
- RecordSet的机制怎样的,能不能将他的缓冲区容量设大些
- sql2005 查询分析器里面直接读取excel,怎么读??
- 有点难度的数据转换问题!!!!
- 事件老是运行 SELECT N'Testing Connection...'
- 进度条
- 简单SQL语句 秒结贴
- 本地机器可以用IP连接远程数据库,web服务器却连接不上远程数据库
- [Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序.急急急!!!!!!!!!!!!!1
SELECT
员工姓名,
销售金额 = SUM(销售金额),
identity(INT) 名次
INTO #表1
FROM 表1
GROUP BY 员工姓名
ORDER BY 销售金额 DESC
--sql 2005
SELECT
员工姓名,
销售金额 = SUM(销售金额),
名次=ROW_NUMBER() OVER (ORDER BY SUM(销售金额) DESC)
FROM 表1
GROUP BY 员工姓名
select * from #tabtmp
员工C 35 1
员工D 20 2
员工E 20 2
员工B 10 3
员工F 5 4
员工G 5 4
where 销售时间 between '1997-01-01'and'1997-02-01'
group by 员工姓名
order by 销售总额 desc
insert into tb values('员工A', 10 ,'2011-8-3 00:56:01')
insert into tb values('员工C', 15 ,'2011-9-1 11:56:01')
insert into tb values('员工D', 20 ,'2011-9-2 23:56:01')
insert into tb values('员工B', 10 ,'2011-9-3 11:56:01')
insert into tb values('员工C', 30 ,'2011-9-4 08:56:01')
insert into tb values('员工D', 10 ,'2011-7-6 05:56:01')
go--sql 2000
select m.* , 名次 = (select count(1) from
(
select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) n where n.销售总额 > m.销售总额) + 1 from
(
select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) m
order by 名次
--sql 2005
select m.* , 名次 = row_number() over(order by 销售总额 desc) from
(
select 员工姓名 , sum(销售金额) 销售总额 from tb where convert(varchar(7),销售时间,120) = '2011-09' group by 员工姓名
) m
order by 名次drop table tb/*
员工姓名 销售总额 名次
---------- ----------- -----------
员工C 45 1
员工D 20 2
员工B 10 3(所影响的行数为 3 行)
*/
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
--*/--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
--*/
select
员工姓名 ,
sum(销售金额) as 销售总额
into
#t
from
tb
where
convert(varchar(7),销售时间,120) = '2011-09'
group by
员工姓名select
员工姓名 ,销售金额,
名次=(select count(distinct 销售金额) from #t where 销售金额>=销售金额)
from
#t t
order by
名次
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (员工姓名 varchar(5),销售金额 int,销售时间 datetime)
insert into [tb]
select '员工A',10,'2011-8-3 00:56:01' union all
select '员工C',15,'2011-9-1 11:56:01' union all
select '员工D',20,'2011-9-2 23:56:01' union all
select '员工B',10,'2011-9-3 11:56:01' union all
select '员工C',30,'2011-9-4 08:56:01' union all
select '员工D',10,'2011-7-6 05:56:01' union all
select '员工E',20,'2011-9-4 08:56:01' union all
select '员工F',20,'2011-9-4 08:56:01'--开始查询
;with cte as(
select 员工姓名,SUM(销售金额) 销售金额 from [tb]
where 销售时间>='2011-9-1' and 销售时间<'2011-10-1'
group by 员工姓名
)
select *,名次=DENSE_RANK() over (order by 销售金额 desc) from cte--结束查询
drop table [tb]
/*
员工姓名 销售金额 名次
----- ----------- --------------------
员工C 45 1
员工D 20 2
员工E 20 2
员工F 20 2
员工B 10 3(5 行受影响)