sql2K 查询，加分，在线等

[xikboy]

http://topic.csdn.net/u/20100519/19/b009edaf-c5ea-4a35-b929-d1fb606ffd22.html?seed=159542023&r=65562100#replyachor

总分100分了，全给.

[xikboy]

顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起顶起

[-狙击手-]

我也顶

[xikboy]

[Quote=引用 2 楼 happyflystone 的回复:]
我也顶
[/Quote]

你不给我写个？？？？？？？？？？？

[FlySQL]

--sql2000
select card,
max(case when rq='01' then sktime else '' end) as [01],
max(case when rq='02' then sktime else '' end) as [02],
max(case when rq='03' then sktime else '' end) as [03],
max(case when rq='04' then sktime else '' end) as [04]
from (
select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t
) t
group by card,rn

/**
card       01    02    03    04
---------- ----- ----- ----- -----
123456     00:01 01:01       00:01
234567     00:01             
123456     00:02 02:01       
234567     00:02             
123456     00:03             

(5 行受影响)

**/

[xikboy]

好些了，但还要在改一下，跟我要的还有点差。

[xikboy]

card '01' '02' '03' '04'
123456 00:01 01:01 00:01
123456 00:02 02:01
123456 00:03
234567 00:01
234567 00:02

[FlySQL]

[Quote=引用 5 楼 xikboy 的回复:]
好些了，但还要在改一下，跟我要的还有点差。
[/Quote]

差在什么地方？

[xikboy]

应该是对的。呵呵~~~

[FlySQL]

--sql2000
select card,
max(case when rn=1 then sktime else '' end) as [01],
max(case when rn=2 then sktime else '' end) as [02],
max(case when rn=3 then sktime else '' end) as [03],
max(case when rn=4 then sktime else '' end) as [04]
from (
select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t
) t
group by card,rq

/**
card       01    02    03    04
---------- ----- ----- ----- -----
123456     00:01 00:02 00:03 
234567     00:01 00:02       
123456     01:01 02:01       
123456     00:01             

(4 行受影响)

**/

[FlySQL]

不知道你要哪一种，自己看吧

[htl258_Tony]

早说吗，有点诱惑

[htl258_Tony]

IF OBJECT_ID('ta') IS NOT NULL
    DROP TABLE [ta]
GO
create table ta(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ta(card,rq,sktime)
select '123456','01','00:01'
union
select '123456','01','00:02'
union
select '123456','01','00:03'
union
select '123456','02','01:01'
union
select '123456','02','02:01'
union
select '123456','04','00:01'
union
select '234567','01','00:01'
union
select '234567','01','00:02'

go
declare @s varchar(8000)
select @s = isnull(@s,'select card')
	+',max( case rq when '''+ltrim(rq)+''' then sktime else '''' end ) '+quotename(rq)
from (select rq = right(101+number,2) from master..spt_values where type='p' and number<=(select max(cast(rq as int))-min(cast(rq as int)) from ta))t
exec(@s+' from (select *,rn = (select count(1) from ta where card = t.card and rq = t.rq and sktime <= t.sktime)
from ta t) t group by rn,card order by 1')
/*
card       01    02    03    04
---------- ----- ----- ----- -----
123456     00:01 01:01       00:01
123456     00:02 02:01       
123456     00:03             
234567     00:01             
234567     00:02             

(5 行受影响)
*/