求助sql写法

Question

各位大侠，求助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原表有上百万笔数据，效率上需要考虑一下

Answer 1

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

Answer 2

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

Answer 3

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 @TBSELECT * FROM #T--DROP TABLE #TTSELECT T.A,T.TIME,T1.TIME FROM #T T,#T T1 WHERE T1.ID=T.ID+1A                    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 行）

Answer 4

/*---------------------------------
--  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 行受影响)
*/

Answer 5

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

Answer 6

看一下可以不 
select a ,(select time from biao where b=down) time1,(select time from biao where b=up) time1 from baio 

Answer 7

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 行受影响)*/

Answer 8

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 行受影响)
*/

Answer 9

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 @TBSELECT * FROM #T--DROP TABLE #TTSELECT 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 NULLA                    TIME1                TIME2                
-------------------- -------------------- -------------------- 
A1                   10:01                10:02
A1                   10:25                10:35
A2                   10:36                10:55（所影响的行数为 3 行）

Answer 10

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
*/来晚了，再提供一种方法！