realvalue
0.0
0.0
0.0
0.0
0.0
1.0
3.0
2.0
4.0
6.0
上面是此查询得到的结果select realvalue from openquery(LnkRtDb_WinCCOLEDB,'Tag:R,(1;8),''2014-08-18 03:08:00.000'',''2014-08-18 03:08:05.000''')
求输出结果为
realvalue realvalue
0.0 1.0
0.0 3.0
0.0 2.0
0.0 4.0
0.0 6.0
的查询语句
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:32:44
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([Valueid] int,[Realvalue] numeric(2,1))
insert [TB]
select 1,0.0 union all
select 1,0.0 union all
select 1,0.0 union all
select 1,0.0 union all
select 1,0.0 union all
select 8,1.0 union all
select 8,3.0 union all
select 8,2.0 union all
select 8,4.0 union all
select 8,6.0
--------------开始查询--------------------------
SELECT a.[realvalue1],b.[realvalue2]
FROM
(select [realvalue] AS [realvalue1],ROW_NUMBER()OVER(ORDER BY GETDATE())id
from [TB]
WHERE Valueid=1)a FULL JOIN (select [realvalue] AS [realvalue2],ROW_NUMBER()OVER(ORDER BY GETDATE())id
from [TB]
WHERE Valueid=8)b ON b.id = a.id
UNION ALL
SELECT SUM(a.[realvalue1]),SUM(b.[realvalue2])
FROM
(select [realvalue] AS [realvalue1],ROW_NUMBER()OVER(ORDER BY GETDATE())id
from [TB]
WHERE Valueid=1)a FULL JOIN (select [realvalue] AS [realvalue2],ROW_NUMBER()OVER(ORDER BY GETDATE())id
from [TB]
WHERE Valueid=8)b ON b.id = a.id
----------------结果----------------------------
/*
realvalue1 realvalue2
--------------------------------------- ---------------------------------------
0.0 1.0
0.0 3.0
0.0 2.0
0.0 4.0
0.0 6.0
0.0 16.0
*/