create table test
(
Uid int identity(1,1),
UserName varchar(20),
DateFlag varchar(10),
Part varchar(10),
LoginSum int
)
insert into test
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31 union all
select 't','200910','ag',11select * from test的结果是
UID UserName DateFlag Part LoginSum
1 admin 200910 as 15
2 admin 200911 ad 25
3 admin 200909 ae 20
4 test 200909 af 31
5 test 200910 ag 31
6 t 200910 ag 11
目标结果:
UserName DateFlag Part LoginSum DateFlag Part LoginSum
admin 200911 ad 25 200909 ae 20
test 200909 ag 31 200910 ag 31
t 200910 ag 11 null null null
(
Uid int identity(1,1),
UserName varchar(20),
DateFlag varchar(10),
Part varchar(10),
LoginSum int
)
insert into test
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31 union all
select 't','200910','ag',11select * from test的结果是
UID UserName DateFlag Part LoginSum
1 admin 200910 as 15
2 admin 200911 ad 25
3 admin 200909 ae 20
4 test 200909 af 31
5 test 200910 ag 31
6 t 200910 ag 11
目标结果:
UserName DateFlag Part LoginSum DateFlag Part LoginSum
admin 200911 ad 25 200909 ae 20
test 200909 ag 31 200910 ag 31
t 200910 ag 11 null null null
4 test 200909 af 31
5 test 200910 ag 31
(
Uid int identity(1,1),
UserName varchar(20),
DateFlag varchar(10),
Part varchar(10),
LoginSum int
)
insert into test
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31 godeclare @sql varchar(8000)
set @sql = 'select UserName '
select @sql = @sql + ' , max(case DateFlag when ''' + DateFlag + ''' then DateFlag else null end) [DateFlag_' + DateFlag + ']'
+ ' , max(case DateFlag when ''' + DateFlag + ''' then Part else null end) [Part_' + DateFlag + ']'
+ ' , max(case DateFlag when ''' + DateFlag + ''' then LoginSum else null end) [LoginSum_' + DateFlag + ']'
from (select top 2 DateFlag from test order by LoginSum desc) as a
set @sql = @sql + ' from test group by UserName'
exec(@sql) drop table test/*
UserName DateFlag_200909 Part_200909 LoginSum_200909 DateFlag_200910 Part_200910 LoginSum_200910
-------------------- --------------- ----------- --------------- --------------- ----------- ---------------
admin 200909 ae 20 200910 as 15
test 200909 af 31 200910 ag 31*/
(
Uid int identity(1,1),
UserName varchar(20),
DateFlag varchar(10),
Part varchar(10),
LoginSum int
)
insert into test
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31
--静态的
select username,
max(case when DateFlag='200910' then DateFlag else null end) 'DateFlag',
max(case when DateFlag='200910' then Part else null end) 'Part',
max(case when DateFlag='200910' then LoginSum else null end) 'LoginSum',
max(case when DateFlag='200911' then username else null end) 'UserName',
max(case when DateFlag='200911' then DateFlag else null end) 'DateFlag',
max(case when DateFlag='200911' then Part else null end) 'Part'
from test group by username--动态的
declare @sql nvarchar(4000)
set @sql='select username'
select @sql=@sql+','+'max(case when DateFlag='''+DateFlag+''' then DateFlag else null end) DateFlag
,max(case when DateFlag='''+DateFlag+'''then Part else null end) part,
max(case when DateFlag='''+DateFlag+'''then LoginSum else null end) LoginSum'
from (select distinct DateFlag from test) U
--print @sql
select @sql=@sql+(' from (select * from test where DateFlag=''200910'' or DateFlag=''200911'') u group by username')
print @sql
exec (@sql)
as
(select *,Row=Row_number()over(partition by UserName order by LoginSum desc) from Test)
select *
from Cte a
left join
Cte b on a.UserName=b.UserName and a.Row=b.Row-1取顯示的列
select 'admin','200910','as',15 union all
select 'admin','200911','ad',25 union all
select 'admin','200909','ae',20 union all
select 'test','200909','af',31 union all
select 'test','200910','ag',31 union all
select 't','200910','ag',11毓华的结果并不是我想要的
test 200909 ag 31 200910 ag 31
t 200910 ag 11 null null null admin这条记录,LoginSum最大的在第一个位置,然后是LoginSum第二大的
admin 200909 ae 20 200910 as 15
t NULL NULL NULL 200910 ag 11
test 200909 af 31 200910 ag 31
select top 2 DateFlag from test order by LoginSum desc
这个返回的是最大的前两个LoginSum的DateFlag,是200909和200910目标结果:
admin 200911 ad 25 200909 ae 20
test 200909 ag 31 200910 ag 31
t 200910 ag 11 null null null
现在DateFlag不做为条件了,
我使用
select * from test A where UID in (select top 2 UID from test
where Username=A.userName order by LoginSum desc)
将LoginSum最大的两条记录查出来,可是不知道如何合并到一行上