一个表:ID、时间、名称、状态。其中状态只有3种值:1、2、3.
比如:同一个名称下的状态是按顺序的,但是可能会没有状态3。这些名称也会重复。
时间 状态 名称
------------------------------------------
2011/8/2 23:36:18 1 A
2011/8/2 23:36:18 1 B
2011/8/2 23:36:19 2 A
2011/8/2 23:36:19 2 B
2011/8/2 23:36:31 3 A
2011/8/2 23:36:31 3 B
2011/8/2 23:46:56 1 A
2011/8/2 23:46:57 2 A现在想转换成下面这种
名称 状态1时间 状态2时间 状态3时间用sql能实现吗
比如:同一个名称下的状态是按顺序的,但是可能会没有状态3。这些名称也会重复。
时间 状态 名称
------------------------------------------
2011/8/2 23:36:18 1 A
2011/8/2 23:36:18 1 B
2011/8/2 23:36:19 2 A
2011/8/2 23:36:19 2 B
2011/8/2 23:36:31 3 A
2011/8/2 23:36:31 3 B
2011/8/2 23:46:56 1 A
2011/8/2 23:46:57 2 A现在想转换成下面这种
名称 状态1时间 状态2时间 状态3时间用sql能实现吗
,max(case when 状态 = 1 then 时间 end) as [状态1时间]
,max(case when 状态 = 2 then 时间 end) as [状态2时间]
,max(case when 状态 = 3 then 时间 end) as [状态3时间]
from tab
group by 名称
select
名称,
max(case when 状态 = 1 then 时间 end) as [状态1时间],
max(case when 状态 = 2 then 时间 end) as [状态2时间],
max(case when 状态 = 3 then 时间 end) as [状态3时间]
from
tb
group by
名称[/code]
create table #tb(时间 datetime,状态 varchar(10), 名称 varchar(10)
)
insert into #tb
select '2011/8/2 23:36:18','1', 'A'
union all
select '2011/8/2 23:36:18','1', 'B'
union all
select '2011/8/2 23:36:18','2', 'A'
union all
select '2011/8/2 23:36:18','2', 'B'
union all
select '2011/8/2 23:36:18','3', 'A'
union all
select '2011/8/2 23:36:18','3', 'B'
union all
select '2011/8/2 23:36:18','1', 'A'
union all
select '2011/8/2 23:36:18','2', 'A'
union all
select '2011/8/2 23:36:18','3', 'A'
SELECT 名称,
max(CASE WHEN 状态='1' THEN 时间 ELSE '' END) as 状态1时间,
max(CASE WHEN 状态='2' THEN 时间 ELSE '' END) as 状态2时间,
max(CASE WHEN 状态='3' THEN 时间 ELSE '' END) as 状态3时间
from #tb
group by 名称
)
truncate table #tb
insert into #tb
select '2011/8/2 23:36:18','1', 'A'
union all
select '2011/8/2 23:36:18','1', 'B'
union all
select '2011/8/2 23:36:18','2', 'A'
union all
select '2011/8/2 23:36:18','2', 'B'
union all
select '2011/8/2 23:36:18','3', 'A'
union all
select '2011/8/2 23:36:18','3', 'B'
union all
select '2011/8/3 23:36:18','1', 'A'
union all
select '2011/8/3 23:36:18','2', 'A'
union all
select '2011/8/3 23:36:18','3', 'A'
select 名称,STUFF((select ','+convert(varchar(19),时间,120) from #tb where 状态=1 and 名称=a.名称 for xml path('')),1,1,'') as 状态1时间,
STUFF((select ','+convert(varchar(19),时间,120) from #tb where 状态=2 and 名称=a.名称 for xml path('')),1,1,'') as 状态2时间,
STUFF((select ','+convert(varchar(19),时间,120) from #tb where 状态=3 and 名称=a.名称 for xml path('')),1,1,'') as 状态3时间
from #tb a
group by 名称
对于每个名称,它会重复出现,同名称下出现时间最为接近的且状态不同的分成了一组,要都显示出来
时间 状态 名称
------------------------------------------
2011/8/2 23:36:18 1 A
2011/8/2 23:36:18 1 B
2011/8/2 23:36:19 2 A
2011/8/2 23:36:19 2 B
2011/8/2 23:36:31 3 A
2011/8/2 23:36:31 3 B
2011/8/2 23:46:56 1 A
2011/8/2 23:46:57 2 A转换后
名称 状态1时间 状态2时间 状态3时间
A 2011/8/2 23:36:18 2011/8/2 23:36:19 2011/8/2 23:36:31
B 2011/8/2 23:36:18 2011/8/2 23:36:19 2011/8/2 23:36:31
A 2011/8/2 23:46:56 2011/8/2 23:46:57
create table #tb(时间 datetime,状态 varchar(10), 名称 varchar(10)
)
insert into #tb
select '2011/8/2 23:36:18','1', 'A'
union all
select '2011/8/2 23:36:18','1', 'B'
union all
select '2011/8/2 23:36:18','2', 'A'
union all
select '2011/8/2 23:36:18','2', 'B'
union all
select '2011/8/2 23:36:18','3', 'A'
union all
select '2011/8/2 23:36:18','3', 'B'
union all
select '2011/8/2 23:36:18','1', 'A'
union all
select '2011/8/2 23:36:18','2', 'A'
go;with ach as
(
select *,px=row_number() over (partition by 状态,名称 order by getdate())
from #tb
)select 名称,
max(CASE WHEN 状态='1' THEN 时间 ELSE null END) as 状态1时间,
max(CASE WHEN 状态='2' THEN 时间 ELSE null END) as 状态2时间,
max(CASE WHEN 状态='3' THEN 时间 ELSE null END) as 状态3时间
from ach
group by 名称,pxdrop table #tb/***************名称 状态1时间 状态2时间 状态3时间
---------- ----------------------- ----------------------- -----------------------
A 2011-08-02 23:36:18.000 2011-08-02 23:36:18.000 2011-08-02 23:36:18.000
B 2011-08-02 23:36:18.000 2011-08-02 23:36:18.000 2011-08-02 23:36:18.000
A 2011-08-02 23:36:18.000 2011-08-02 23:36:18.000 NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
select 名称,时间 as time1 ,(select top 1 时间 from Table b where 名称=a.名称 and 时间>a.时间) as time2,
((select top 1 时间 from Table b where 名称=a.名称 and 时间>a.时间 and 标志=3)) as time3
from Table a
where 标志=1
order by 时间和大家的差距好大