有一个表
P_Year P_Name P_Value
2006 小王 85
2006 小李 86
2006 小赵 87
2007 小王 98
2007 小刘 98
2007 小张 78我想按照年份来统计每年的最大数值,同时希望能够知道是由哪些人是具有改数值的。
想要的结果如下P_Year MaxValue Owner
2006 87 小赵
2007 98 小王,小刘
P_Year P_Name P_Value
2006 小王 85
2006 小李 86
2006 小赵 87
2007 小王 98
2007 小刘 98
2007 小张 78我想按照年份来统计每年的最大数值,同时希望能够知道是由哪些人是具有改数值的。
想要的结果如下P_Year MaxValue Owner
2006 87 小赵
2007 98 小王,小刘
if object_id('tb') is not null
drop table tb
go
create table tb(P_Year int,P_Name varchar(20),P_Value int)
insert into tb select 2006,'小王',85
insert into tb select 2006,'小李',86
insert into tb select 2006,'小赵',87insert into tb select 2007,'小王',98
insert into tb select 2007,'小刘',98
insert into tb select 2007,'小张',78drop function f_str
CREATE FUNCTION dbo.f_str(@P_Value varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(P_Name as varchar)
FROM tb
WHERE P_Value=@P_Value
RETURN(STUFF(@re,1,1,''))
ENDselect P_Year,max(P_Value) as P_Value,
dbo.f_str(max(P_Value)) as owner from tb
group by P_YearP_Year P_Value owner
2006 87 小赵
2007 98 小王,小刘
SQLSERVER2005的可以使用CTE
if object_id('tb') is not null
drop table tb
go
create table tb(P_Year int,P_Name nvarchar(20),P_Value int)
insert into tb select 2006,N'小王',85
insert into tb select 2006,N'小李',86
insert into tb select 2006,N'小赵',87insert into tb select 2007,N'小王',98
insert into tb select 2007,N'小刘',98
insert into tb select 2007,N'小张',78
select
a.P_Year,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,''),a.P_Value
from
(select P_Year,max(P_Value)P_Value from tb group by P_Year) a
Cross apply
(select COl2=(select N','+P_Name from tb where P_Year=a.P_Year and P_Value=a.P_Value For XML PATH(''), ROOT('R'), TYPE))b
/*
2006 小赵 87
2007 小王,小刘 98
*/
as
begin
declare @currYear int,@maxValue int,@name nvarchar(1000)--年份,对应的最大值
if object_id('tempdb..#') is not null
drop table #
select top 0 * into # from tb --存入结果的中间表
declare cur_open cursor --定义一个游标
for
select p_year,max(p_value) from tb group by p_year
open cur_open--打开游标
fetch next from cur_open into @currYear,@maxValue--下一条记录
while(@@fetch_status=0)
begin
set @name=''--每一次循环置为初值
select @name=@name+p_name+',' from tb where p_value=@maxValue and p_year=@currYear
set @name=stuff(@name,len(@name),1,'')--截取最后一个逗号
print @name
insert into # select @currYear,@name,@maxValue-- 插入中间表
fetch next from cur_open into @currYear,@maxValue
end
--关闭游标
close cur_open
--删除游标
deallocate cur_open
--返回数据
select * from #
endP_Year P_Name P_Value
----------- -------------------- -----------
2006 小赵 87
2007 小王,小刘 98(所影响的行数为 2 行)