2张表结构不同, 将数据进行 行,列转换

[风影枫林]

现在要将table1中的数据转到table2中,请教SQL语句该如何实现(只需要查询语句就行了);
将table1中2条 name 和 time 都相同的数据整合为table2的一条数据

--在这条SQL语句上加上一条得到table2中value3的值
--假设现在时间为12点过5分   2011-6-13 12:05:00 
--value3的值是当type=2时,2011-6-13 12:00:00 时间点的value减去2011-6-13 11:00:00时间点的value
select name,convert(varchar(10),[time],120) as [time],
sum(case when type=1 then value else 0 end) as value1,
sum(case when type=2 then value else 0 end) as value2
from table1
group by name,convert(varchar(10),[time],120)

表 table1
name        time                   value        type
A        2011-6-13 11:00:00         51           1
B        2011-6-13 12:00:00         35           1
C        2011-6-13 11:00:00         43           1
B        2011-6-13 12:00:00         8            2
A        2011-6-13 11:00:00         4            2
C        2011-6-13 12:00:00         10           2
C        2011-6-13 11:00:00         7            2
A        2011-6-13 12:00:00         6            2
A        2011-6-13 12:00:00         54           1
B        2011-6-13 11:00:00         3            2
B        2011-6-13 11:00:00         38           1

表 table2
name time value1 value2 value3
A 2011-6-13 11:00:00 51 4 0
A 2011-6-13 12:00:00 54 6 2
B 2011-6-13 11:00:00 38 3 0
B 2011-6-13 12:00:00 35 8 5
C 2011-6-13 11:00:00 43 7 0
C 2011-6-13 12:00:00 0 10 3

[-晴天]

行转列.
这种问题前赴后继!

[风影枫林]

这不是单纯的行,列转换,还涉及到计算

[cd731107]

select a.*,d.value-a.value2
from 
(
select name,convert(varchar(100),[time],120) as [time],
sum(case when type=1 then value else 0 end) as value1,
sum(case when type=2 then value else 0 end) as value2
from table1
group by name,convert(varchar(100),[time],120)
) a 
left join 
(
select *
from table1 b where  type=2 
and not exists 
(select 1 from table1 c 
 where b.name=c.name 
 and c.type=2 
 and datediff(day,b.time,c.time)=0 
 and c.time>b.time)
) d
on a.name=d.name and datediff(day,a.time,d.time)=0

[-晴天]

create table table1(name varchar(10),time datetime,value int,type int)
insert into table1 select 'A','2011-6-13 11:00:00',51,1
insert into table1 select 'B','2011-6-13 12:00:00',35,1
insert into table1 select 'C','2011-6-13 11:00:00',43,1
insert into table1 select 'B','2011-6-13 12:00:00',8, 2
insert into table1 select 'A','2011-6-13 11:00:00',4, 2
insert into table1 select 'C','2011-6-13 12:00:00',10,2
insert into table1 select 'C','2011-6-13 11:00:00',7, 2
insert into table1 select 'A','2011-6-13 12:00:00',6, 2
insert into table1 select 'A','2011-6-13 12:00:00',54,1
insert into table1 select 'B','2011-6-13 11:00:00',3, 2
insert into table1 select 'B','2011-6-13 11:00:00',38,1
go
--补全
;with c1 as(
select * from table1
union all
select name,time,0 as value,(case when type=1 then 2 else 1 end)type from(
select * from table1 a where not exists(select 1 from table1 where name=a.name and time=a.time and type<>a.type)
)t
),c2 as(
select a.name,a.time,a.value v1,b.value v2
from c1 a inner join c1 b on a.name=b.name and a.time=b.time and a.type=b.type-1
)--,c3 as(
select a.name,a.time,a.v1,a.v2,a.v2-b.v2 v3 
from c2 a left join(
select * from c2 a where not exists(select 1 from c2 where name=a.name and time<a.time)
)b on a.name=b.name order by name,time
/*
name       time                    v1          v2          v3
---------- ----------------------- ----------- ----------- -----------
A          2011-06-13 11:00:00.000 51          4           0
A          2011-06-13 12:00:00.000 54          6           2
B          2011-06-13 11:00:00.000 38          3           0
B          2011-06-13 12:00:00.000 35          8           5
C          2011-06-13 11:00:00.000 43          7           0
C          2011-06-13 12:00:00.000 0           10          3

(6 行受影响)

*/
go
drop table table1

[风影枫林]

3楼的运行结果不正确,value1 和 value 3 全是0


4楼的sql语句执行报错,列名name不明确.

[AcHerat]

[Quote=引用 5 楼 huang005x 的回复:]
3楼的运行结果不正确,value1 和 value 3 全是0


4楼的sql语句执行报错,列名name不明确.
[/Quote]

4楼可以出来的，楼主是不是复制错了，还是改错地方了。

set nocount on
create table table1(name varchar(10),time datetime,value int,type int)
insert into table1 select 'A','2011-6-13 11:00:00',51,1
insert into table1 select 'B','2011-6-13 12:00:00',35,1
insert into table1 select 'C','2011-6-13 11:00:00',43,1
insert into table1 select 'B','2011-6-13 12:00:00',8, 2
insert into table1 select 'A','2011-6-13 11:00:00',4, 2
insert into table1 select 'C','2011-6-13 12:00:00',10,2
insert into table1 select 'C','2011-6-13 11:00:00',7, 2
insert into table1 select 'A','2011-6-13 12:00:00',6, 2
insert into table1 select 'A','2011-6-13 12:00:00',54,1
insert into table1 select 'B','2011-6-13 11:00:00',3, 2
insert into table1 select 'B','2011-6-13 11:00:00',38,1
go
--补全
;with c1 as(
select * from table1
union all
select name,time,0 as value,(case when type=1 then 2 else 1 end)type from(
select * from table1 a where not exists(select 1 from table1 where name=a.name and time=a.time and type<>a.type)
)t
),c2 as(
select a.name,a.time,a.value v1,b.value v2
from c1 a inner join c1 b on a.name=b.name and a.time=b.time and a.type=b.type-1
)--,c3 as(
select a.name,a.time,a.v1,a.v2,a.v2-b.v2 v3 
from c2 a left join(
select * from c2 a where not exists(select 1 from c2 where name=a.name and time<a.time)
)b on a.name=b.name order by name,time
go
set nocount off
drop table table1


/******************************************

name       time                    v1          v2          v3
---------- ----------------------- ----------- ----------- -----------
A          2011-06-13 11:00:00.000 51          4           0
A          2011-06-13 12:00:00.000 54          6           2
B          2011-06-13 11:00:00.000 38          3           0
B          2011-06-13 12:00:00.000 35          8           5
C          2011-06-13 11:00:00.000 43          7           0
C          2011-06-13 12:00:00.000 0           10          3

[crazy_boom]

看到这样的问题 就想起了 大乌龟

[风影枫林]

为什么我运行的时候老是说列名name不明确呢?

我用的是 sql server 2008

[风影枫林]

消息 209，级别 16，状态 1，第 15 行
列名 'name' 不明确。
消息 209，级别 16，状态 1，第 15 行
列名 'time' 不明确。

[cd731107]

--小改一下就OK了
create table table1(name varchar(10),time datetime,value int,type int)

insert table1
--name        time                   value        type
select 'A','2011-6-13 11:00:00',         51      ,     1 union all
select 'B','2011-6-13 12:00:00',         35       ,    1 union all
select 'C','2011-6-13 11:00:00',         43        ,   1 union all
select 'B','2011-6-13 12:00:00',         8          ,  2 union all
select 'A','2011-6-13 11:00:00',         4         ,   2 union all
select 'C','2011-6-13 12:00:00',         10         ,  2 union all
select 'C','2011-6-13 11:00:00',         7           , 2 union all
select 'A','2011-6-13 12:00:00',         6            ,2 union all
select 'A','2011-6-13 12:00:00',         54           ,1 union all
select 'B','2011-6-13 11:00:00',         3            ,2 union all
select 'B','2011-6-13 11:00:00',         38           ,1


select a.*,a.value2-d.value
from 
(
select name,convert(varchar(100),[time],120) as [time],
sum(case when type=1 then value else 0 end) as value1,
sum(case when type=2 then value else 0 end) as value2
from table1
group by name,convert(varchar(100),[time],120)
) a 
left join 
(
select *
from table1 b where  type=2 
and not exists 
(select 1 from table1 c 
 where b.name=c.name 
 and c.type=2 
 and datediff(day,b.time,c.time)=0 
 and c.time<b.time)
) d
on a.name=d.name and datediff(day,a.time,d.time)=0

[--小F--]

select
 a.*,a.value2-d.value
from 
( select
 name,convert(varchar(100),[time],120) as [time],
 sum(case when type=1 then value else 0 end) as value1,
 sum(case when type=2 then value else 0 end) as value2
from
 table1
group by
 name,convert(varchar(100),[time],120))a 
left join 
(select
  *
 from
  table1 b where  type=2 
and
 not exists (select 1 from table1 c  where b.name=c.name and c.type=2 and datediff(day,b.time,c.time)=0 and c.time<b.time)
)b
on
 a.name=b.name and datediff(day,a.time,b.time)=0

[风影枫林]

10楼是不是只考虑了我举例的那些数据?

--假设现在时间为11点过5分 2011-6-13 11:05:00 
--2011-6-13 11:00:00 的value3的值为
--2011-6-13 11:00:00 时间点的value减去2011-6-13 10:00:00时间点的value


--假设现在时间为12点过5分   2011-6-13 12:05:00 
--value3的值是当type=2时,2011-6-13 12:00:00的value3的值为
--2011-6-13 12:00:00 时间点的value减去2011-6-13 11:00:00时间点的value

--依次类推            


create table table1(name varchar(10),time datetime,value int,type int)

insert table1
--name        time                   value        type
select 'A','2011-6-13 11:00:00',         51      ,     1 union all
select 'B','2011-6-13 12:00:00',         35       ,    1 union all
select 'C','2011-6-13 11:00:00',         43        ,   1 union all
select 'B','2011-6-13 12:00:00',         8          ,  2 union all
select 'A','2011-6-13 11:00:00',         4         ,   2 union all
select 'C','2011-6-13 12:00:00',         10         ,  2 union all
select 'C','2011-6-13 11:00:00',         7           , 2 union all
select 'A','2011-6-13 12:00:00',         6            ,2 union all
select 'A','2011-6-13 12:00:00',         54           ,1 union all
select 'B','2011-6-13 11:00:00',         3            ,2 union all
select 'B','2011-6-13 11:00:00',         38           ,1 union all
select 'A','2011-6-13 10:00:00',         6           , 2 union all
select 'A','2011-6-13  9:00:00',         5           , 2 union all
select 'A','2011-6-13  8:00:00',         4           , 2 union all
select 'A','2011-6-13  7:00:00',         3           , 2