有这样的表结构
CREATE TABLE [dbo].[StationHistoryData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StationSN] [int] NOT NULL,
[CollectedDateTime] [datetime] NOT NULL,
[Data] [xml] NOT NULL
)
示例数据这样：
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>
有这样的存储过程：
create proc usp_Logic_DifferentStationSameSensorTypeChart
(
@StartDateTime datetime,
@EndDateTime datetime,
@QueryQueue StationSNSensorChannelTableType READONLY
)
as
begin
--请问这里怎么写
end
其中 StationSNSensorChannelTableType 变类型结构如下：
CREATE TYPE [dbo].[StationSNSensorChannelTableType] AS TABLE(
[StationSN] [int] NOT NULL,
[Channel] [nvarchar](3) NOT NULL
)
GO
传递到这个(usp_Logic_DifferentStationSameSensorTypeChart)存储过程中的 StationSNSensorChannelTableType 中的数据如下：
13505 A01
13505 A02
13505 A04
13506 A01
现在希望存储过程返回CollectedDateTime介于@StartDateTime和@EndDateTime之间的类似以下结果集
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 A04 90
13506 2014-06-15 00:40:00.000 A01 225
注： 存储过程中的 StationSNSensorChannelTableType 中的查询数据中
13505 A04
并没有在示例数据中ID是1的记录中出现，所以返回结果集中并不返回此数据
有点复杂啊，多谢各位帮忙。。。
鄙人不才木有看懂,LZ的智商让我望尘莫及啊.
抽象成以下表示方式：
表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 行受影响)
*/
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)
*/
