--查询处理
select nid=0,id,dt,dis=cast(dis as varchar)
into #t
from 表
order by iddeclare @id int,@nid int,@s varchar(8000)
update #t set @nid=case @id when id then @nid+1 else 1 end
,nid=@nid,@id=id
select @nid=max(nid),@s='' from #t
while @nid>0
select @s=',时间点'+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dt end),距离'
+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dis end)'
+@s
,@nid=@nid-1
exec('select id'+@s+' from #t group by id')
select nid=0,id,dt,dis=cast(dis as varchar)
into #t
from 表
order by iddeclare @id int,@nid int,@s varchar(8000)
update #t set @nid=case @id when id then @nid+1 else 1 end
,nid=@nid,@id=id
select @nid=max(nid),@s='' from #t
while @nid>0
select @s=',时间点'+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dt end),距离'
+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dis end)'
+@s
,@nid=@nid-1
exec('select id'+@s+' from #t group by id')
min(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=1 then bt else '9999-12-31' end) as 时间点1,
sum(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=1 then dis else 0 end) as 距离1,
min(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=2 then bt else '9999-12-31' end) as 时间点2,
sum(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=2 then dis else 0 end) as 距离2,
min(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=3 then bt else '9999-12-31' end) as 时间点3,
sum(case when (select count(*) from tablename where id=a.id and bt<=a.bt)=3 then dis else 0 end) as 距离3
from tablename a
group by id
麻烦你再详细点说明,谢谢!
select @nid=max(nid),@s='' from(
select nid=count(*) from 表
group by id)a
while @nid>0
select @s=',时间点'+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dt end),距离'
+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dis end)'
+@s
,@nid=@nid-1
exec('select id'+@s+' from(
select nid=(select count(*) from 表 where id=a.id orderid<=a.orderid),*
from 表 a
)agroup by id')
select @nid=max(nid),@s='' from(
select nid=count(*) from 表
group by id)a
while @nid>0
select @s=',时间点'+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dt end),距离'
+cast(@nid as varchar)+'=max(case nid when '
+cast(@nid as varchar)+' then dis end)'
+@s
,@nid=@nid-1
exec('select id'+@s+' from(
select nid=(select count(*) from 表 where id=a.id orderid<=a.orderid),*
from 表 a
)a group by id')