SQL查询

Question

如下数据
id |  current | state | time
1  |     1.5  |  放电 | 2011-3-22 10:14:15
2  |    1.4   |  放电  | 2011-3-22 10:14:32
3  |   1.7   |   放电  |  2011-3-22 10:14:53
4  |   1.4    |  充电  |  2011-3-22 10:15:20
5  |    1.7    | 充电  |  2011-3-22 10:15:46
6  |    1.6    | 放电   | 2011-3-22 10:15:57
7  |    1.4    | 放电   | 2011-3-22 10:16:15
用SQL查成以下结果
state | statetime             |  endtime
放电  |    2011-3-22 10:14:15  |    2011-3-22 10:14:53
充电  | 2011-3-22 10:15:20    |  2011-3-22 10:15:46
放电  | 2011-3-22 10:15:57    | 2011-3-22 10:16:15

Answer 1

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([id] int,[id2] decimal(18,1),[current] nvarchar(2),[state] NVARCHAR(20),[time] NVARCHAR(10))
Insert #1
select 1,1.5,N'放电','2011-3-22','10:14:15' union all
select 2,1.4,N'放电','2011-3-22','10:14:32' union all
select 3,1.7,N'放电','2011-3-22','10:14:53' union all
select 4,1.4,N'充电','2011-3-22','10:15:20' union all
select 5,1.7,N'充电','2011-3-22','10:15:46' union all
select 6,1.6,N'放电','2011-3-22','10:15:57' union all
select 7,1.4,N'放电','2011-3-22','10:16:15'
Go
SELECT a.[current],a.startDate,endDate=min(b.endDate)
 FROM (Select [current],CAST([state] AS DATETIME)+[time] AS startDate from #1 AS a WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE [current]=a.[current] AND ID=a.ID-1)) AS a,
 (SELECT [current],CAST([state] AS DATETIME)+[time] AS endDate from #1 AS a WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE [current]=a.[current] AND ID=a.ID+1)) AS b
WHERE a.[current]=b.[current]
GROUP BY a.[current],a.startDate/*
current startDate endDate
放电 2011-03-22 10:14:15.000 2011-03-22 10:14:53.000
充电 2011-03-22 10:15:20.000 2011-03-22 10:15:46.000
放电 2011-03-22 10:15:57.000 2011-03-22 10:14:53.000
*/

Answer 2

id实质上是随机数 是不会重复的
不用id也可以实现吗？

Answer 3

是什麼版本?SQL2005以上用row_number按日期排序生成IDSQL2000用臨時表生表自增ID

Answer 4

if object_id(N'Tempdb.dbo.#temp') is NOT null  drop table #temp
Go
Create table #temp([id] int,[current] decimal(18,1),[state] nvarchar(2),[time] DATETIME)
Insert #temp
select 1,1.5,N'放电','2011-3-22 10:14:15' union all
select 2,1.4,N'放电','2011-3-22 10:14:32' union all
select 3,1.7,N'放电','2011-3-22 10:14:53' union all
select 4,1.4,N'充电','2011-3-22 10:15:20' union all
select 5,1.7,N'充电','2011-3-22 10:15:46' union all
select 6,1.6,N'放电','2011-3-22 10:15:57' union all
select 7,1.4,N'放电','2011-3-22 10:16:15'
Go
--SQL:
;WITH cte AS
(
SELECT groupno = id - ROW_NUMBER() OVER(PARTITION BY [state] ORDER BY [id]),* FROM #temp
)
SELECT a.[state], b.statetime, c.endtime FROM
(SELECT groupno, [state], [id]=MAX(id) FROM cte GROUP BY groupno, [state]) a
CROSS APPLY
(SELECT statetime = MIN([time]) FROM cte WHERE groupno = a.groupno AND [state] = a.[state]) b
CROSS APPLY
(SELECT endtime = MAX([time]) FROM cte WHERE groupno = a.groupno AND [state] = a.[state]) c
ORDER BY a.[id]
/*
放电 2011-03-22 10:14:15.000 2011-03-22 10:14:53.000
充电 2011-03-22 10:15:20.000 2011-03-22 10:15:46.000
放电 2011-03-22 10:15:57.000 2011-03-22 10:16:15.000
*/

Answer 5

roy_88如果要加row_number的话就要用临时表来存放
但这个数据量是上万的 

Answer 6

;with t as(
select * from test111
union
select 0 as 'id',0 as 'currect',case (select top 1 state from test111 order by id) when '放电' then '充电' else '放电' end as 'state','1900-01-01 00:00:00.000' as 'time'
union all
select (select max(id)+1 from test111) as 'id',0 as 'currect',case (select top 1 state from test111 order by id desc) when '放电' then '充电' else '放电' end as 'state',(select max([time]) from test111) as 'time'
),
t2 as (
select state,(select b.time from t b where b.id=(select max(id)+1 from t a where a.id<=t.id and a.state<>t.state)) as starttime,
(select b.time from t b where b.id=(select min(id)-1 from t a where a.id>=t.id and a.state<>t.state)) as endtime
from t)
select  distinct * from t2 
where starttime is not null
and endtime is not null
order by starttime

Answer 7

if object_id(N'Tempdb.dbo.#temp') is NOT null  drop table #temp
Go
Create table #temp([id] int,[current] decimal(18,1),[state] nvarchar(2),[time] DATETIME)
Insert #temp
select 1,1.5,N'放电','2011-3-22 10:14:15' union all
select 2,1.4,N'放电','2011-3-22 10:14:32' union all
select 3,1.7,N'放电','2011-3-22 10:14:53' union all
select 4,1.4,N'充电','2011-3-22 10:15:20' union all
select 5,1.7,N'充电','2011-3-22 10:15:46' union all
select 6,1.6,N'放电','2011-3-22 10:15:57' union all
select 7,1.4,N'放电','2011-3-22 10:16:15'
Go;with t as(
select * from #temp
union
select 0 as 'id',0 as 'currect',case (select top 1 state from #temp order by id) when '放电' then '充电' else '放电' end as 'state','1900-01-01 00:00:00.000' as 'time'
union all
select (select max(id)+1 from #temp) as 'id',0 as 'currect',case (select top 1 state from #temp order by id desc) when '放电' then '充电' else '放电' end as 'state',(select max([time]) from #temp) as 'time'
),
t2 as (
select state,(select b.time from t b where b.id=(select max(id)+1 from t a where a.id<=t.id and a.state<>t.state)) as starttime,
(select b.time from t b where b.id=(select min(id)-1 from t a where a.id>=t.id and a.state<>t.state)) as endtime
from t)
select  distinct * from t2 
where starttime is not null
and endtime is not null
order by starttime借6楼的表用下。

Answer 8

Create table #temp([id] int,[current] decimal(18,1),[state] nvarchar(2),[time] DATETIME)
Insert #temp
select 1,1.5,N'放电','2011-3-22 10:14:15' union all
select 2,1.4,N'放电','2011-3-22 10:14:32' union all
select 3,1.7,N'放电','2011-3-22 10:14:53' union all
select 4,1.4,N'充电','2011-3-22 10:15:20' union all
select 5,1.7,N'充电','2011-3-22 10:15:46' union all
select 6,1.6,N'放电','2011-3-22 10:15:57' union all
select 7,1.4,N'放电','2011-3-22 10:16:15'
Go;WITH cte AS
(
SELECT  id-ROW_NUMBER() OVER(PARTITION BY [state] ORDER BY [id]) id1,* FROM #temp
)
select distinct state,
min(time) over (partition by state,id1 ) statetime,
max(time) over (partition by state,id1 ) endtime
from cte order by statetime