求助sql写法

[onefunnyday]

各位大侠，求助sql
原表
A B time
A1 down 10:01
A1 up 10:02
A1 down 10:25
A1 up 10:35
A2 down 10:36
A2 up 10:55

希望得出某个A项每次down 和up的时间

A time1 time2
A1 10:01 10:02
A1 10:25 10:35
A2 10:36 10:55

原表有上百万笔数据，效率上需要考虑一下

[SQL77]

有没有其它情况??

[onefunnyday]

不会，正常同一个A都是先dowm,然后UP

[ks_reny]

貌似得需要一個序號列好協助完成.

[jiangshun]

每个down和up是连续的吗？会不会出现只有down或者up的情况？

[SQL77]

DECLARE @TB TABLE(A VARCHAR(20),   B  VARCHAR(20),       time VARCHAR(20))
INSERT @TB
SELECT 'A1' , 'down'    ,  '10:01' UNION ALL
SELECT 'A1' , 'up'      ,  '10:02' UNION ALL
SELECT 'A1' , 'down'    ,  '10:25' UNION ALL
SELECT 'A1' , 'up'      ,  '10:35' UNION ALL
SELECT 'A2' , 'down'    ,  '10:36' UNION ALL
SELECT 'A2' , 'up'      ,  '10:55' 

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @TB

SELECT * FROM #T

--DROP TABLE #TT

SELECT T.A,T.TIME,T1.TIME FROM #T T,#T T1 WHERE T1.ID=T.ID+1

A                    TIME                 TIME                 
-------------------- -------------------- -------------------- 
A1                   10:01                10:02
A1                   10:02                10:25
A1                   10:25                10:35
A1                   10:35                10:36
A2                   10:36                10:55

（所影响的行数为 5 行）

[htl258_Tony]

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-14 18:43:02
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
	Mar 29 2009 10:27:29 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:原表

IF NOT OBJECT_ID('[原表]') IS NULL
	DROP TABLE [原表]
GO
CREATE TABLE [原表]([A] NVARCHAR(10),[B] NVARCHAR(10),[time] NVARCHAR(10))
INSERT [原表]
SELECT 'A1','down','10:01' UNION ALL
SELECT 'A1','up','10:02' UNION ALL
SELECT 'A1','down','10:25' UNION ALL
SELECT 'A1','up','10:35' UNION ALL
SELECT 'A2','down','10:36' UNION ALL
SELECT 'A2','up','10:55'
GO
--SELECT * FROM [原表]

-->SQL查询如下:
SELECT A,down AS time1,up AS time2
FROM (
	SELECT rn=(ROW_NUMBER()OVER(PARTITION BY a ORDER BY TIME)-1)/2,* 
	FROM  原表
	) a
	PIVOT (MAX(time) FOR B IN([down],[up]))b
/*
A          time1      time2
---------- ---------- ----------
A1         10:01      10:02
A1         10:25      10:35
A2         10:36      10:55

(3 行受影响)
*/

[onefunnyday]

[Quote=引用 4 楼 jiangshun 的回复:]
每个down和up是连续的吗？会不会出现只有down或者up的情况？
[/Quote]

是连续的，是一组一组的，每次down，都会有一次UP，只有最后一次down，有可能没有UP
相当与机器的关闭，启动，关闭，再启动，我要每次关闭到启动的时间

[指间扣]

[Quote=引用楼主 onefunnyday 的回复:]
各位大侠，求助sql
原表
A    B        time
A1  down      10:01
A1  up        10:02
A1  down      10:25
A1  up        10:35
A2  down      10:36
A2  up        10:55

希望得出某个A项每次down 和up的时间

A  time1      time2 
A1  10:01      10:02     
A1  10:25      10:35 
A2  10:36      10:55

原表有上百万笔数据，效率上需要考虑一下
[/Quote]
看一下可以不
select a ,(select time from biao where b=down) time1,(select time from biao where b=up) time1 from baio

[onefunnyday]

补充：是SQL2000

[onefunnyday]

[Quote=引用 6 楼 htl258 的回复:]
SQL code/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-14 18:43:02
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29 ¡­
[/Quote]

sql2000中怎么写？

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(2),[B] varchar(4),[time] datetime)
insert [tb]
select 'A1','down','10:01' union all
select 'A1','up','10:02' union all
select 'A1','down','10:25' union all
select 'A1','up','10:35' union all
select 'A2','down','10:36' union all
select 'A2','up','10:55'

-->查询
select 
  a.A,
  convert(varchar(5),a.[time],108) as time1,
  convert(varchar(5),b.[time],108) as time2
from
  (select *,px=(select count(1) from tb where b='down' and [time]<t.[time]) from tb t where b='down') a
left join
  (select *,px=(select count(1) from tb where b='up' and [time]<t.[time]) from tb t where b='up') b
on 
  a.a=b.a and a.px=b.px
  


--测试结果:
/*
A    time1 time2
---- ----- -----
A1   10:01 10:02
A1   10:25 10:35
A2   10:36 10:55

(3 行受影响)

*/

[htl258_Tony]

IF NOT OBJECT_ID('[原表]') IS NULL
    DROP TABLE [原表]
GO
CREATE TABLE [原表]([A] NVARCHAR(10),[B] NVARCHAR(10),[time] NVARCHAR(10))
INSERT [原表]
SELECT 'A1','down','10:01' UNION ALL
SELECT 'A1','up','10:02' UNION ALL
SELECT 'A1','down','10:25' UNION ALL
SELECT 'A1','up','10:35' UNION ALL
SELECT 'A2','down','10:36' UNION ALL
SELECT 'A2','up','10:55'
GO
--SELECT * FROM [原表]

-->SQL2000查询如下:

SELECT A,MAX(CASE WHEN B='down' THEN time END) AS time1,MAX(CASE WHEN B='up' THEN time END) AS time2
FROM (
    SELECT rn=(SELECT COUNT(1) FROM 原表 WHERE a=T.a AND TIME<T.TIME)/2,* 
    FROM  原表 t
    ) a
GROUP BY A,rn
/*
A          time1      time2
---------- ---------- ----------
A1         10:01      10:02
A2         10:36      10:55
A1         10:25      10:35

(3 行受影响)
*/

[SQL77]

DECLARE @TB TABLE(A VARCHAR(20),   B  VARCHAR(20),       time VARCHAR(20))
INSERT @TB
SELECT 'A1' , 'down'    ,  '10:01' UNION ALL
SELECT 'A1' , 'up'      ,  '10:02' UNION ALL
SELECT 'A1' , 'down'    ,  '10:25' UNION ALL
SELECT 'A1' , 'up'      ,  '10:35' UNION ALL
SELECT 'A2' , 'down'    ,  '10:36' UNION ALL
SELECT 'A2' , 'up'      ,  '10:55' 

SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM @TB

SELECT * FROM #T

--DROP TABLE #TT

SELECT A,TIME1,TIME2 FROM 
(
SELECT 
	T.A,
	CASE WHEN T.ID%2=1 THEN T.TIME END AS TIME1,
	CASE WHEN T1.ID%2=0 THEN T1.TIME END AS TIME2
FROM #T T,#T T1 WHERE T1.ID=T.ID+1 
)AS T WHERE TIME1 IS NOT NULL AND TIME2 IS NOT NULL

A                    TIME1                TIME2                
-------------------- -------------------- -------------------- 
A1                   10:01                10:02
A1                   10:25                10:35
A2                   10:36                10:55

（所影响的行数为 3 行）

[soft_wsx]

if OBJECT_ID('TB') IS NOT NULL DROP TABLE tb
  go
create table tb(a nvarchar(10),b nvarchar(10),time nvarchar(10))
  go
insert into tb
select  
'A1',  'down',      '10:01'  union all select
'A1',  'up',        '10:02' union all select
'A1',  'down',      '10:25' union all select
'A1',  'up',        '10:35' union all select
'A2',  'down',      '10:36' union all select
'A2',  'up',        '10:55' 
alter table tb add id int identity(1,1)
select *
from 
(
select a.a,time1=(select time from tb where a=a.a and b='down' and ID=a.id),
           time2=(select time from tb where a=a.a and b='up' and ID=a.id+1)  
  from tb a 
)k
where time1 is not null
/*
a	time1	time2
A1	10:01	10:02
A1	10:25	10:35
A2	10:36	10:55
*/

来晚了，再提供一种方法！

[onefunnyday]

謝謝大家