请问:
1.存储过程测试输入参数@StartDateTime,@EndDateTime的值是什么?
2.以下结果集是如何计算出来的?
13505 2014-06-15 00:39:31.000 A01 20
13505 2014-06-15 00:39:31.000 A02 30 --> StationHistoryData表ID=1,2记录的XML中都有A02的值,结果只取ID=1的A02的计算逻辑是什么?
13505 2014-06-15 08:39:31.000 A04 90
13506 2014-06-15 00:40:00.000 A01 225
1.存储过程测试输入参数@StartDateTime,@EndDateTime的值是什么?
2.以下结果集是如何计算出来的?
13505 2014-06-15 00:39:31.000 A01 20
13505 2014-06-15 00:39:31.000 A02 30 --> StationHistoryData表ID=1,2记录的XML中都有A02的值,结果只取ID=1的A02的计算逻辑是什么?
13505 2014-06-15 08:39:31.000 A04 90
13506 2014-06-15 00:40:00.000 A01 225
解决方案 »
- SQL 2005数据库转到SQL 2000的步骤
- 显示出当前月的1号到30号(31号)的查询语句
- 请教:网管需要给我开放什么功能??
- 如何在数据库中查时间最新的同一物品的不同记录?
- SQL存储过程动态拼接SQL,INTO 实表改临时表
- 不太难也不太容易的问题,其实跟数据库关系不太大,只是求一个解题思路。
- text 和char 有什么区别?若有一段文本,格式较为漂亮,欲存SQL中,应选用那种类型?
- 关于客户端连接MS-SQL超时过长,想改短时间。
- 这样的三表sql查询语句怎么写?
- 关于站内信箱:如果是两个不同的实体,相互间通信:比如普通用户和商家成员。如何设计数据库?
- sql server management stdio 第一行报错
- 一个关于触发器的小问题
表1(@QueryQueue变量 StationSNSensorChannelTableType类型)1 13505 A01
2 13505 A02
3 13505 A04
4 13506 A01
表2(需要查询数据的表 [dbo].[StationHistoryData])1 13505 2014-06-15 00:39:31.000 <root><A01>20</A01><A02>30</A02><A03>40</A03></root>
2 13505 2014-06-15 08:39:31.000 <root><A02>50</A02><A04>90</A04><P01>20</P01></root>
4 13506 2014-06-15 00:40:00.000 <root><V>9.25</V><A01>225</A01></root>
执行过程:从表1.1看表2.1,13505匹配,且xml中包括A01,返回表2.1中的
13505 2014-06-15 00:39:31.000 A01 20从表1.1看表2.2,13505匹配,但xml中不包括A01,丢弃从表1.1看表2.4,13505不匹配,丢弃从表1.2看表2.1,13505匹配,xml中包含A02,返回
13505 2014-06-15 00:39:31.000 A02 30从表1.2看表2.2,13505匹配,xml中包含A02,返回
13505 2014-06-15 08:39:31.000 A02 50继续上述过程
;with dataXml as (
select StationSn ,cast(channel as char(3)) as channel,channelValue from (
select StationSn,data.query('/root/A01').value('.','varchar(20)') as A01
,data.query('/root/A02').value('.','varchar(20)') AS A02
,data.query('/root/A03').value('.','varchar(20)') AS A03
,data.query('/root/A04').value('.','varchar(20)') AS A04
from StationHistoryData) as a
unpivot
( channelValue for channel in (A01,A02,A03,A04) ) AS B
where channelValue<>''
)
select a.StationSn,a.channel,a.channelValue ,b.CollectedDateTime
from dataXml as a inner join StationHistoryData as b on a.StationSn=b.StationSn and cast(b.data as varchar(max)) like '%'+a.channelValue+'%' and cast(b.data as varchar(max)) like '%'+a.channel+'%' --结果展示
/*
StationSn channel channelValue CollectedDateTime
----------- ------- -------------------- -----------------------
13505 A01 20 2014-06-15 00:39:31.000
13505 A02 30 2014-06-15 00:39:31.000
13505 A03 40 2014-06-15 00:39:31.000
13505 A02 50 2014-06-15 08:39:31.000
13505 A04 90 2014-06-15 08:39:31.000
13506 A01 225 2014-06-15 00:40:00.000(6 行受影响) */
[StationSN] [int] NOT NULL,
[Channel] [nvarchar](3) NOT NULL
)
GO这个表类型中会包含类似下面的数据:
13505 A01
13505 A02
13505 A04
13506 A01
CREATE TYPE [dbo].[StationSNSensorChannelTableType] AS table
([StationSN] [int] NOT NULL,
[Channel] [nvarchar](3) NOT NULL
)CREATE TABLE [dbo].[StationHistoryData]
([ID] [int] NOT NULL,
[StationSN] [int] NOT NULL,
[CollectedDateTime] [datetime] NOT NULL,
[Data] [xml] NOT NULL
)insert into StationHistoryData
select 1,13505,'2014-06-15 00:39:31.000','<root><A01>20</A01><A02>30</A02><A03>40</A03></root>' union all
select 2,13505,'2014-06-15 08:39:31.000','<root><A02>50</A02><A04>90</A04><P01>20</P01></root>' union all
select 4,13506,'2014-06-15 00:40:00.000','<root><V>9.25</V><A01>225</A01></root>'
-- 查询1
select s.ID,
s.StationSN,
s.CollectedDateTime,
o.value('local-name(.)','varchar(10)') 'c',
o.value('.','varchar(10)') 'v'
from StationHistoryData s
cross apply Data.nodes('/root/*') x(o)/*
ID StationSN CollectedDateTime c v
----------- ----------- ----------------------- ---------- ----------
1 13505 2014-06-15 00:39:31.000 A01 20
1 13505 2014-06-15 00:39:31.000 A02 30
1 13505 2014-06-15 00:39:31.000 A03 40
2 13505 2014-06-15 08:39:31.000 A02 50
2 13505 2014-06-15 08:39:31.000 A04 90
2 13505 2014-06-15 08:39:31.000 P01 20
4 13506 2014-06-15 00:40:00.000 V 9.25
4 13506 2014-06-15 00:40:00.000 A01 225(8 row(s) affected)
*/
-- 查询2
declare @x [StationSNSensorChannelTableType]insert into @x
select 13505,'A01' union all
select 13505,'A02' union all
select 13505,'A04' union all
select 13506,'A01'select a.StationSN,b.CollectedDateTime,a.Channel,b.v
from @x a
cross join
(select s.ID,
s.StationSN,
s.CollectedDateTime,
o.value('local-name(.)','varchar(10)') 'c',
o.value('.','varchar(10)') 'v'
from StationHistoryData s
cross apply Data.nodes('/root/*') x(o)) b
where a.StationSN=b.StationSN and a.Channel=b.c/*
StationSN CollectedDateTime Channel v
----------- ----------------------- ------- ----------
13505 2014-06-15 00:39:31.000 A01 20
13505 2014-06-15 00:39:31.000 A02 30
13505 2014-06-15 08:39:31.000 A02 50
13505 2014-06-15 08:39:31.000 A04 90
13506 2014-06-15 00:40:00.000 A01 225(5 row(s) affected)
*/