求一句分组排序的SQL-单表

[kalakala]

table name Sales

员工 所属小组 业绩
张三 红旗 10
张三 红旗 20
李四 红旗 60
王五 朝阳 10
王五 朝阳 40
赵六 朝阳 30

求实现SQL如下

进行小组内加总排序，例如

员工 所属小组 业绩 名次
张三 红旗 30 2
李四 红旗 60 1
王五 朝阳 50 1
赵六 朝阳 30 2

例如：张三是红旗小组第二名，王五是朝阳小组第一名。

谢谢

因使用条件受限，最好是SQL语句而不是存储过程，谢谢。

[毛师兄]

學習了。

[kalakala]

select *，DENSE_RANK() OVER(PARTITION BY GROUP_NAME ORDER BY 组内排序依据字段-例如销售额 DESC) AS RANK
from table

多谢楼上几位，我自己又琢磨了一下，OK了。

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([员工] [nvarchar](10),[所属小组] [nvarchar](10),[业绩] [int])
INSERT INTO [tb]
SELECT '张三','红旗','10' UNION ALL
SELECT '张三','红旗','20' UNION ALL
SELECT '李四','红旗','60' UNION ALL
SELECT '王五','朝阳','10' UNION ALL
SELECT '王五','朝阳','40' UNION ALL
SELECT '赵六','朝阳','30'

--SELECT * FROM [tb]

-->SQL查询如下:
SELECT *,名次=dense_rank()over(order by 业绩 desc)
FROM (
	SELECT DISTINCT 员工,所属小组,sum(业绩)over(partition by 员工,所属小组) 业绩
	FROM tb
) AS t 
ORDER BY 所属小组,名次
/*
员工         所属小组       业绩          名次
---------- ---------- ----------- --------------------
王五         朝阳         50          2
赵六         朝阳         30          3
李四         红旗         60          1
张三         红旗         30          3

(4 行受影响)
*/

[bluesmiler]

SELECT 员工,所属小组,sum(业绩) 业绩,rank() over (partition by 所属小组 order by sum(业绩) desc ) from Sales
group by 员工,所属小组


王五 朝阳 50 1
赵六 朝阳 30 2
李四 红旗 60 1
张三 红旗 30 2

[bluesmiler]

或者
SELECT 员工,所属小组,sum(业绩) 业绩,dense_rank() over (partition by 所属小组 order by sum(业绩) desc ) from Sales
group by 员工,所属小组

[心灵的深处总是孤单的]

--部门表
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[Departmentinf]') AND type in (N'U'))
DROP TABLE [Departmentinf]
go
CREATE TABLE [Departmentinf]
(
	DID bigint identity(1,1) PriMary Key,
	DName varchar(50) null,                             --部门名称
)
go

insert into [Departmentinf] values('红旗')
insert into [Departmentinf] values('朝阳')

go
--业务表
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[Resultinf]') AND type in (N'U'))
DROP TABLE [Resultinf]
go
CREATE TABLE [Resultinf]
(
	DID		bigint identity(1,1) PriMary Key,
	names	varchar(50) null,                            --部员工
	Groups	int,							 --所属小组
	Results	decimal(18, 0)								 --业绩 
	
)
go
insert into [Resultinf] values('张三',1,'10')
insert into [Resultinf] values('张三',1,'20')
insert into [Resultinf] values('李四',1,'60')
insert into [Resultinf] values('王五',2,'10')
insert into [Resultinf] values('王五',2,'40')
insert into [Resultinf] values('赵六',2,'30')

select names,DName,sum(Results) as Results
from [Resultinf] a,[Departmentinf] b where a.groups=b.did group by DName,names order by DName desc


--员工 所属小组 业绩  
李四	红旗	60
张三	红旗	30
王五	朝阳	50
赵六	朝阳	30 

[new_fengzi]

declare @table table(员工 nvarchar(20),所属小组 nvarchar(20),业绩 int)
insert into @table
select '张三','红旗',10 union all
select '张三','红旗',20 union all
select '李四','红旗',60 union all
select '王五','朝阳',10 union all
select '王五','朝阳',40 union all
select '赵六','朝阳',30

--select  * from @table
select 员工,所属小组,sum(业绩) as 业绩,rank() over (partition by 所属小组 order by sum(业绩) desc ) as 名次
 from @table
group by 员工,所属小组

[Tosp2012]

IF OBJECT_ID('[Ta]') IS NOT NULL
    DROP TABLE [Ta]
GO
CREATE TABLE [Ta] 
(
Names Char(20),
Groups char(10),
Score int
)

INSERT INTO Ta
SELECT '張三','紅旗','10' UNION ALL
SELECT '張三','紅旗','20' UNION ALL
SELECT '李四','紅旗','60' UNION ALL
SELECT '王五','朝陽','10' UNION ALL
SELECT '王五','朝陽','40' UNION ALL
SELECT '趙六','朝陽','30'
select * from Ta
select Names,Groups,Sum(Score) as Scores From Ta  Group by Names,Groups order by  Scores desc

[心灵的深处总是孤单的]

[Quote=引用 1 楼 htl258 的回复:]
SQL code
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([员工] [nvarchar](10),[所属小组] [nvarchar](10),[业绩] [int])
INSERT INTO [tb]
SELECT '张三','红旗','10……
[/Quote]
人家好像要按组分的吧

[kalakala]

楼上高人还是不少啊，我看看研究一下谢谢各位。

[andy_liucj]

SELECT 员工,所属小组,sum(业绩) as 业绩,rank() over (partition by 所属小组 order by sum(业绩) desc) from Sales
group by 员工,所属小组

[liang145]

create table #Sales(员工 nvarchar(10),所属小组 nvarchar(10), 业绩  int)
insert #Sales select '张三', '红旗', 10 union all
select '张三', '红旗', 20  union all
select '李四', '红旗', 60 union all
select '王五', '朝阳', 10 union all
select '王五', '朝阳', 40 union all
select '赵六', '朝阳', 30 

select outs.员工,所属小组,sum(业绩) as 总业绩 ,
(select num from
(select ROW_NUMBER() over(order by sum(ins.业绩) desc)as num,ins.员工
 from #Sales as ins where ins.所属小组=outs.所属小组 group by ins.员工 )
as t where t.员工=outs.员工) as 名次 
from #Sales as outs group by 所属小组,员工

[javatemptation]

use tempdb;
/*
create table Sales
(
	员工 nvarchar(10) not null,
	所属小组 nvarchar(10) not null,
	业绩 int not null
);
insert into Sales(员工,所属小组,业绩)
values
('张三','红旗',10),
('张三','红旗',20),
('李四','红旗',60),
('王五','朝阳',10),
('王五','朝阳',40),
('赵六','朝阳',30);
*/
select t.员工,t.所属小组,SUM(t.业绩) as [业绩],
ROW_NUMBER() over(partition by t.所属小组 order by SUM(t.业绩) desc) as [名次]
from Sales as t
group by t.员工,t.所属小组;