======== 高手帮忙,有关XML查询,感谢不尽 ===============

愚者只看星不看答案 2014-06-15 07:10:08


有这样的表结构

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的记录中出现,所以返回结果集中并不返回此数据

有点复杂啊,多谢各位帮忙。。。

...全文
268 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
@ap0405140 感谢不尽。。
  • 打赏
  • 举报
回复
引用 7 楼 ap0405140 的回复:

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)
*/
多谢,先看看,稍候回复。
唐诗三百首 2014-06-16
  • 打赏
  • 举报
回复

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)
*/
  • 打赏
  • 举报
回复
引用 5 楼 DBAXMMDS 的回复:

;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 行受影响)

 */





感谢你的回复。 A01,A02这些并不是硬编码的,而是通过下面的表类型传递到存储过程的 CREATE TYPE [dbo].[StationSNSensorChannelTableType] AS TABLE( [StationSN] [int] NOT NULL, [Channel] [nvarchar](3) NOT NULL ) GO 这个表类型中会包含类似下面的数据: 13505 A01 13505 A02 13505 A04 13506 A01
极品老土豆 2014-06-15
  • 打赏
  • 举报
回复

;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 行受影响)

 */





  • 打赏
  • 举报
回复
板主,你太坏了,不带这么bs别人智商的。 换一种说话,看看。。


抽象成以下表示方式:


表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

继续上述过程
唐诗三百首 2014-06-15
  • 打赏
  • 举报
回复
鄙人不才木有看懂,LZ的智商让我望尘莫及啊.
  • 打赏
  • 举报
回复
1.存储过程测试输入参数@StartDateTime,@EndDateTime的值是什么?

值由存储过程的调用方发出,是一个datetime类型,测试时可以从 2014-06-15 0:0:0 到 2014-06-15 23:59:59

2、第二个问题画个图上来。意思是多对多的看待问题。不知道能不能表述清楚。
唐诗三百首 2014-06-15
  • 打赏
  • 举报
回复
请问: 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

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧