22,199
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#t') is not null
drop table #t
create table #t(ID int,time datetime ,money int)
insert into #t
select 1 ,'2009-08-01' ,1000
union all select 1 ,'2009-08-02' ,2000
union all select 2 ,'2009-08-15' ,5000
union all select 2 ,'2009-08-16' ,6000
union all select 2 ,'2009-08-17' ,7000
alter table #t add id1 int
go
declare @i int
declare @j int
set @i = 0
set @j = 0
update #t set
id1 = case
when id = @j then @i else 1
end
,@i= case
when id = @j then @i+1 else 1
end
,@j = id
select * from #t
declare @str varchar(8000)
set @str =''
select @str = @str +',max(case when id1 ='+ltrim(id1)+' then time else null end)
,sum(case when id1 ='+ltrim(id1)+' then money else null end)'
from #t
where id = (select top 1 id from #t order by id1 desc)
set @str = 'select id '+@str+' from #t group by id'
--print @str
exec(@str)
id
---- ------------------------ ----------- ------------------------ ----------- ------------------------ -----------
1 2009-08-01 00:00:00.000 1000 2009-08-02 00:00:00.000 2000 NULL NULL
2 2009-08-15 00:00:00.000 5000 2009-08-16 00:00:00.000 6000 2009-08-17 00:00:00.000 7000
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb
--添加一列
alter table tb add id int identity
go
--
declare @s varchar(8000)
select @s=isnull(@s+',','select ')+'
卡号=max(case px when '+ltrim(px)+' then ltrim(卡号) else '''' end),
时间=max(case px when '+ltrim(px)+' then convert(varchar(10),时间,23) else '''' end)'
from (select distinct px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id) from tb t) t
set @s=@s+' from (select px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id),* from tb t) t group by 卡号'
exec(@s)
/*
卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21
80030 2009-02-21 80030 2009-04-19
80049 2009-02-21
80051 2009-02-14 80051 2009-03-21
80124 2009-05-16
80180 2009-04-11 80180 2009-04-18 80180 2009-04-25
(6 行受影响)
*/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([卡号] int,[时间] datetime)
insert [tb]
select 80005,'2009-2-21' union all
select 80030,'2009-2-21' union all
select 80030,'2009-4-19' union all
select 80049,'2009-2-21' union all
select 80051,'2009-2-14' union all
select 80051,'2009-3-21' union all
select 80124,'2009-5-16' union all
select 80180,'2009-4-11' union all
select 80180,'2009-4-18' union all
select 80180,'2009-4-25'
---查询---
declare @sql varchar(8000),@i int,@maxcnt int
select @i=1,@maxcnt=max(cnt) from (select 卡号,count(1) as cnt from tb group by 卡号) t
while @i<=@maxcnt
begin
select @sql=isnull(@sql+',','')+'卡号,时间=max(case px when '+rtrim(@i)+' then convert(varchar(10),时间,120) else '''' end)',@i=@i+1
end
set @sql= 'select '
+@sql
+' from (select *,px=(select count(1)+1 from tb where 卡号=t.卡号 and 时间>t.时间) from tb t) tt group by 卡号'
exec(@sql)
---结果---
卡号 时间 卡号 时间 卡号 时间
----------- ---------- ----------- ---------- ----------- ----------
80005 2009-02-21 80005 80005
80030 2009-04-19 80030 2009-02-21 80030
80049 2009-02-21 80049 80049
80051 2009-03-21 80051 2009-02-14 80051
80124 2009-05-16 80124 80124
80180 2009-04-25 80180 2009-04-18 80180 2009-04-11
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb
-->SQL查询如下:
select id=identity(int),* into # from tb t where not exists(select 1 from tb where 卡号=t.卡号 and 时间>t.时间)
select
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from #
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-04-19 80049 2009-02-21 80051 2009-03-21
80124 2009-05-16 80180 2009-04-25
(2 行受影响)
*/
drop table #
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb
-->SQL查询如下:
alter table tb add id int identity
go
select
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from tb
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-02-21 80030 2009-04-19 80049 2009-02-21
80051 2009-02-14 80051 2009-03-21 80124 2009-05-16 80180 2009-04-11
80180 2009-04-18 80180 2009-04-25
(3 行受影响)
*/