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

2011-06-18 03:44:41 128 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

STM32F103VET6实战：用W25Q64外部Flash实现Bootloader+Application双备份（含完整代码解析）本文详细介绍了基于STM32F103VET6微控制器和W25Q64外部Flash实现Bootloader+Application双备份固件升级的完整方案。通过精心设计的Flash分区策略、可靠的Bootloader程序以及包含双重校验和自动回滚机制的升级流程，有效解决了远程升级过程中断电导致设备变砖的风险，确保了工业嵌入式系统的高可靠性与稳定性。阅读详情

现在要将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

...全文

STM32与SD卡SPI接口硬件设计：从原理图到调试的完整指南 SPI（串行外设接口）作为一种广泛应用的同步串行通信协议，以其接线简单、驱动成熟的特点，成为连接微控制器与各类外设的经典方案。其工作原理基于主从架构，通过时钟、数据线和片选信号实现全双工通信，在嵌入式系统中具有极高的技术价值，常用于连接传感器、存储设备和显示模块等。在数据存储场景中，SD卡凭借其大容量和便携性成为首选，而通过SPI模式驱动SD卡，则平衡了性能与设计复杂度。本文聚焦于使用Altium Designer进行硬件设计的工程实践，详细解析了STM32微控制器与SD卡通过SPI通信时，电源管理、上拉电阅读详情

风影枫林

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

风影枫林 2011-06-19

打赏
举报

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

--小F-- 2011-06-18

打赏
举报

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

cd731107 2011-06-18

打赏
举报

--小改一下就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

风影枫林 2011-06-18

打赏
举报

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

风影枫林 2011-06-18

打赏
举报

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

我用的是 sql server 2008

crazy_boom 2011-06-18

打赏
举报

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

AcHerat 元老 2011-06-18

打赏
举报

[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

风影枫林 2011-06-18

打赏
举报

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

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

-晴天 2011-06-18

打赏
举报

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

cd731107 2011-06-18

打赏
举报

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

风影枫林 2011-06-18

打赏
举报

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

-晴天 2011-06-18

打赏
举报

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

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

相关推荐

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