--感觉LZ说的结果和描述的逻辑不一样,所以随便猜猜,试试看结果吧 select T1.F_UserID,T1.F_UserName,T1.F_RoleName ,F_GSID=1 ,T1.F_FileIndex ,T1.F_hit,T1.F_CreateTime From T_Role T1 inner join ( Select A.[F_UserID], A.[F_UserName] ,A.F_FileIndex, A.F_Hit, Min(A.F_CreateTime) as F_CreateTime From [T_Role] A inner join ( select [F_UserID], [F_UserName] ,F_FileIndex ,Max( F_Hit) as F_Hit from [T_Role] group by [F_UserID], [F_UserName] ,F_FileIndex ) B on A.F_UserID=B.F_UserID and A.F_UserName=B.F_UserName and A.F_FileIndex=B.F_FileIndex And A.F_Hit=B.F_Hit ) T2 on T1.F_UserID=T2.F_UserID and T1.F_UserName=T2.F_UserName and T1.F_FileIndex=T2.F_FileIndex and T1.F_Hit=T2.F_Hit and T1.F_CreateTime=T2.F_CreateTime
create table t_role (F_UserID int,F_UserName varchar(50), F_RoleName varchar(50), F_GSID int, F_FileIndex int, F_hit int,F_CreateTime datetime ) insert t_role values(1518, 'ccdccd', 'cccca' , 1, 0, 150, '2008-09-20 21:36:00') insert t_role values(1518, 'ccdccd', '小i111', 5, 0, 150, '2009-02-22 19:59:00') insert t_role values(1518, 'ccdccd', '中国' , 1, 1, 1 ,'2008-12-01 16:07:00') insert t_role values(1518, 'ccdccd', 'ccadd' , 5, 1, 7 ,'2009-03-12 10:22:00') insert t_role values(1518, 'ccdccd', '111221', 1, 2, 1 ,'2008-12-11 15:02:00') insert t_role values(1518, 'ccdccd', 'test13', 1, 3, 1 ,'2008-10-06 10:02:00') ------------------ select [F_UserID], F_UserName , [F_RoleName] = case when count(F_FileIndex) > 1 then (select top 1 [F_RoleName] from t_role where [F_UserName] = a.[F_UserName] and F_FileIndex = a.F_FileIndex order by F_hit desc,F_CreateTime ) else max([F_RoleName]) end , [F_GSID] = case when count(F_FileIndex) > 1 then (select max(f_GSID) from t_role where [F_UserName] = a.[F_UserName] and F_FileIndex = a.F_FileIndex ) else max(a.[F_GSID]) end ,F_FileIndex ,max(F_hit) F_hit ,max(F_CreateTime) F_CreateTime from t_role a group by [F_UserID],F_UserName,F_FileIndex ------------ /* F_UserID F_UserName F_RoleName F_GSID F_FileIndex F_hit F_CreateTime ----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- ----------------------- 1518 ccdccd cccca 5 0 150 2009-02-22 19:59:00.000 1518 ccdccd ccadd 5 1 7 2009-03-12 10:22:00.000 1518 ccdccd 111221 1 2 1 2008-12-11 15:02:00.000 1518 ccdccd test13 1 3 1 2008-10-06 10:02:00.000(4 row(s) affected)*/
drop table t_role create table t_role (F_UserID int,F_UserName varchar(50), F_RoleName varchar(50), F_GSID int, F_FileIndex int, F_hit int,F_CreateTime datetime ) insert t_role values(1518, 'ccdccd', 'cccca' , 1, 0, 150, '2008-09-20 21:36:00') insert t_role values(1518, 'ccdccd', '小i111', 5, 0, 150, '2009-02-22 19:59:00') insert t_role values(1518, 'ccdccd', '中国' , 1, 1, 1 ,'2008-12-01 16:07:00') insert t_role values(1518, 'ccdccd', 'ccadd' , 5, 1, 7 ,'2009-03-12 10:22:00') insert t_role values(1518, 'ccdccd', '111221', 1, 2, 1 ,'2008-12-11 15:02:00') insert t_role values(1518, 'ccdccd', 'test13', 1, 3, 1 ,'2008-10-06 10:02:00') declare @F_UserName varchar(50) declare @F_FileIndex int declare taoistong CURSOR FOR SELECT F_UserName, F_FileIndex FROM t_role group by F_UserName, F_FileIndex having count(1)>1select OPEN taoistong FETCH taoistong INTO @F_UserName, @F_FileIndex; while (@@fetch_status=0) begin if (select COUNT( distinct F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex ) >1 begin print 1 update t_role set F_GSID=1 where F_hit=(select MAX(F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex) and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex print 3 delete t_role where F_hit<(select MAX(F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex) and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex end else begin print 2 delete t_role where F_CreateTime>(select Min(F_CreateTime) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex) and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex end FETCH NEXT FROM taoistong INTO @F_UserName, @F_FileIndex end close taoistong deallocate taoistong /*1518 ccdccd cccca 1 0 150 2008-09-20 21:36:00.000 1518 ccdccd ccadd 1 1 7 2009-03-12 10:22:00.000 */
--感觉LZ说的结果和描述的逻辑不一样,所以随便猜猜,试试看结果吧
select T1.F_UserID,T1.F_UserName,T1.F_RoleName ,F_GSID=1 ,T1.F_FileIndex ,T1.F_hit,T1.F_CreateTime
From T_Role T1
inner join
(
Select A.[F_UserID], A.[F_UserName] ,A.F_FileIndex, A.F_Hit, Min(A.F_CreateTime) as F_CreateTime
From [T_Role] A
inner join
(
select [F_UserID], [F_UserName] ,F_FileIndex ,Max( F_Hit) as F_Hit
from [T_Role]
group by [F_UserID], [F_UserName] ,F_FileIndex
) B
on A.F_UserID=B.F_UserID and A.F_UserName=B.F_UserName and A.F_FileIndex=B.F_FileIndex And A.F_Hit=B.F_Hit
) T2
on T1.F_UserID=T2.F_UserID
and T1.F_UserName=T2.F_UserName
and T1.F_FileIndex=T2.F_FileIndex
and T1.F_Hit=T2.F_Hit
and T1.F_CreateTime=T2.F_CreateTime
create table t_role (F_UserID int,F_UserName varchar(50), F_RoleName varchar(50), F_GSID int, F_FileIndex int, F_hit int,F_CreateTime datetime )
insert t_role values(1518, 'ccdccd', 'cccca' , 1, 0, 150, '2008-09-20 21:36:00')
insert t_role values(1518, 'ccdccd', '小i111', 5, 0, 150, '2009-02-22 19:59:00')
insert t_role values(1518, 'ccdccd', '中国' , 1, 1, 1 ,'2008-12-01 16:07:00')
insert t_role values(1518, 'ccdccd', 'ccadd' , 5, 1, 7 ,'2009-03-12 10:22:00')
insert t_role values(1518, 'ccdccd', '111221', 1, 2, 1 ,'2008-12-11 15:02:00')
insert t_role values(1518, 'ccdccd', 'test13', 1, 3, 1 ,'2008-10-06 10:02:00')
------------------
select [F_UserID], F_UserName
, [F_RoleName] = case when count(F_FileIndex) > 1 then (select top 1 [F_RoleName] from t_role where [F_UserName] = a.[F_UserName] and F_FileIndex = a.F_FileIndex order by F_hit desc,F_CreateTime ) else max([F_RoleName]) end
, [F_GSID] = case when count(F_FileIndex) > 1 then (select max(f_GSID) from t_role where [F_UserName] = a.[F_UserName] and F_FileIndex = a.F_FileIndex ) else max(a.[F_GSID]) end
,F_FileIndex
,max(F_hit) F_hit
,max(F_CreateTime) F_CreateTime
from t_role a
group by [F_UserID],F_UserName,F_FileIndex
------------
/*
F_UserID F_UserName F_RoleName F_GSID F_FileIndex F_hit F_CreateTime
----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- -----------------------
1518 ccdccd cccca 5 0 150 2009-02-22 19:59:00.000
1518 ccdccd ccadd 5 1 7 2009-03-12 10:22:00.000
1518 ccdccd 111221 1 2 1 2008-12-11 15:02:00.000
1518 ccdccd test13 1 3 1 2008-10-06 10:02:00.000(4 row(s) affected)*/
create table t_role (F_UserID int,F_UserName varchar(50), F_RoleName varchar(50), F_GSID int, F_FileIndex int, F_hit int,F_CreateTime datetime )
insert t_role values(1518, 'ccdccd', 'cccca' , 1, 0, 150, '2008-09-20 21:36:00')
insert t_role values(1518, 'ccdccd', '小i111', 5, 0, 150, '2009-02-22 19:59:00')
insert t_role values(1518, 'ccdccd', '中国' , 1, 1, 1 ,'2008-12-01 16:07:00')
insert t_role values(1518, 'ccdccd', 'ccadd' , 5, 1, 7 ,'2009-03-12 10:22:00')
insert t_role values(1518, 'ccdccd', '111221', 1, 2, 1 ,'2008-12-11 15:02:00')
insert t_role values(1518, 'ccdccd', 'test13', 1, 3, 1 ,'2008-10-06 10:02:00') declare @F_UserName varchar(50)
declare @F_FileIndex int
declare taoistong CURSOR FOR SELECT F_UserName, F_FileIndex
FROM t_role
group by F_UserName, F_FileIndex
having count(1)>1select OPEN taoistong
FETCH taoistong INTO @F_UserName, @F_FileIndex;
while (@@fetch_status=0)
begin
if (select COUNT( distinct F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex ) >1
begin
print 1
update t_role
set F_GSID=1
where F_hit=(select MAX(F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex)
and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex print 3
delete t_role
where F_hit<(select MAX(F_hit) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex)
and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex end
else
begin
print 2
delete t_role
where F_CreateTime>(select Min(F_CreateTime) from t_role where F_UserName=@F_UserName and F_FileIndex=@F_FileIndex)
and F_UserName=@F_UserName and F_FileIndex=@F_FileIndex end
FETCH NEXT FROM taoistong INTO @F_UserName, @F_FileIndex
end
close taoistong
deallocate taoistong
/*1518 ccdccd cccca 1 0 150 2008-09-20 21:36:00.000
1518 ccdccd ccadd 1 1 7 2009-03-12 10:22:00.000
*/