现在要将table1中的数据转到table2中,请教SQL语句该如何实现(只需要查询语句就行了);
将table1中2条 name 和 time 都相同的数据整合为table2的一条数据--这条SQL语句是不行的,查出来的结果变少了,有的数据查不出来
select a.name,a.time,a.value1,b.value2 from
(select name,time,value from table1 where type=1) as a,
(select name,time,value from table1 where type=2) as b
where a.name=b.name and a.time=b.time
表 table1
name time value type
A 2011-6-13 51 1
B 2011-6-13 35 1
C 2011-6-1 43 1
B 2011-6-1 8 2
A 2011-6-1 4 2
C 2011-6-13 53 1
C 2011-6-13 7 2
A 2011-6-13 6 2表 table2
name time value1 value2
A 2011-6-13 51 6
A 2011-6-1 0 4
B 2011-6-13 35 0
B 2011-6-1 0 8
将table1中2条 name 和 time 都相同的数据整合为table2的一条数据--这条SQL语句是不行的,查出来的结果变少了,有的数据查不出来
select a.name,a.time,a.value1,b.value2 from
(select name,time,value from table1 where type=1) as a,
(select name,time,value from table1 where type=2) as b
where a.name=b.name and a.time=b.time
表 table1
name time value type
A 2011-6-13 51 1
B 2011-6-13 35 1
C 2011-6-1 43 1
B 2011-6-1 8 2
A 2011-6-1 4 2
C 2011-6-13 53 1
C 2011-6-13 7 2
A 2011-6-13 6 2表 table2
name time value1 value2
A 2011-6-13 51 6
A 2011-6-1 0 4
B 2011-6-13 35 0
B 2011-6-1 0 8
(select name,time,value from table1 where type=1) as a,
full join
(select name,time,value from table1 where type=2) as b
on a.name=b.name and a.time=b.time
go
create table [table1]([name] varchar(1),[time] datetime,[value] int,[type] int)
insert [table1]
select 'A','2011-6-13',51,1 union all
select 'B','2011-6-13',35,1 union all
select 'C','2011-6-1',43,1 union all
select 'B','2011-6-1',8,2 union all
select 'A','2011-6-1',4,2 union all
select 'C','2011-6-13',53,1 union all
select 'C','2011-6-13',7,2 union all
select 'A','2011-6-13',6,2
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)/**
name time value1 value2
---- ---------- ----------- -----------
A 2011-06-01 0 4
B 2011-06-01 0 8
C 2011-06-01 43 0
A 2011-06-13 51 6
B 2011-06-13 35 0
C 2011-06-13 53 7(6 行受影响)
**/
select a,b from tb1
可以在查询语句后加个条件 time=整点 吗?
如果可以,那么这个条件改如何用sql语句表示?
(select name,time,value from table1 where type=1) as a
inner join
(select name,time,value from table1 where type=2) as b
on
a.name=b.name and a.time=b.time