3张表
表A
AID
888
777
表B
BID AID BNO(BNO不能重复,每个AID至少有一条BID=1的记录)
1 888 2
3 888 3
5 888 4
6 888 5
1 777 6
3 777 7
5 777 8
6 777 9
表C
BNO BDATA BTIME
2 1.5 2008-08-08 11:00:00
3 1.6 2008-08-08 11:00:00
4 1.8 2008-08-08 11:00:00
5 1.9 2008-08-08 11:00:00
6 1.5 2008-08-08 11:00:00
7 1.6 2008-08-08 11:00:00
8 1.8 2008-08-08 11:00:00
9 1.9 2008-08-08 11:00:00
2 2.5 2008-08-08 12:00:00
3 2.6 2008-08-08 12:00:00
4 2.8 2008-08-08 12:00:00
5 2.9 2008-08-08 12:00:00
6 2.5 2008-08-08 12:00:00
7 2.6 2008-08-08 12:00:00
8 2.8 2008-08-08 12:00:00
9 2.9 2008-08-08 12:00:00 给两个时间 计算AID=777 的BID=1 的两个时间点在c表里的数据差tempA,再计算属于AID的其他终端两个时间点的数据差tempB,tempC... 最后用result=tempA-(tempB+tempC+temp...)希望返回所有用户的result.
说的比较罗嗦,大家帮忙阿
表A
AID
888
777
表B
BID AID BNO(BNO不能重复,每个AID至少有一条BID=1的记录)
1 888 2
3 888 3
5 888 4
6 888 5
1 777 6
3 777 7
5 777 8
6 777 9
表C
BNO BDATA BTIME
2 1.5 2008-08-08 11:00:00
3 1.6 2008-08-08 11:00:00
4 1.8 2008-08-08 11:00:00
5 1.9 2008-08-08 11:00:00
6 1.5 2008-08-08 11:00:00
7 1.6 2008-08-08 11:00:00
8 1.8 2008-08-08 11:00:00
9 1.9 2008-08-08 11:00:00
2 2.5 2008-08-08 12:00:00
3 2.6 2008-08-08 12:00:00
4 2.8 2008-08-08 12:00:00
5 2.9 2008-08-08 12:00:00
6 2.5 2008-08-08 12:00:00
7 2.6 2008-08-08 12:00:00
8 2.8 2008-08-08 12:00:00
9 2.9 2008-08-08 12:00:00 给两个时间 计算AID=777 的BID=1 的两个时间点在c表里的数据差tempA,再计算属于AID的其他终端两个时间点的数据差tempB,tempC... 最后用result=tempA-(tempB+tempC+temp...)希望返回所有用户的result.
说的比较罗嗦,大家帮忙阿
一个AID对应一个用户?
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([AID] int)
insert [A]
select 888 union all
select 777
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([BID] int,[AID] int,[BNO] int)
insert [B]
select 1,888,2 union all
select 3,888,3 union all
select 5,888,4 union all
select 6,888,5 union all
select 1,777,6 union all
select 3,777,7 union all
select 5,777,8 union all
select 6,777,9
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
create table [C]([BNO] int,[BDATA] numeric(2,1),[BTIME] datetime)
insert [C]
select 2,1.5,'2008-08-08 11:00:00' union all
select 3,1.6,'2008-08-08 11:00:00' union all
select 4,1.8,'2008-08-08 11:00:00' union all
select 5,1.9,'2008-08-08 11:00:00' union all
select 6,1.5,'2008-08-08 11:00:00' union all
select 7,1.6,'2008-08-08 11:00:00' union all
select 8,1.8,'2008-08-08 11:00:00' union all
select 9,1.9,'2008-08-08 11:00:00' union all
select 2,2.5,'2008-08-08 12:00:00' union all
select 3,2.6,'2008-08-08 12:00:00' union all
select 4,2.8,'2008-08-08 12:00:00' union all
select 5,2.9,'2008-08-08 12:00:00' union all
select 6,2.5,'2008-08-08 12:00:00' union all
select 7,2.6,'2008-08-08 12:00:00' union all
select 8,2.8,'2008-08-08 12:00:00' union all
select 9,2.9,'2008-08-08 12:00:00'DECLARE @s datetime, @e datetime
select @s='2008-08-08 11:00:00', @e='2008-08-08 12:00:00'select AID, result=sum(case B.BID when 1 then S.BDATA else -S.BDATA end)
+sum(case B.BID when 1 then -E.BDATA else E.BDATA end)
from B left join (select BNO, BDATA from C where BTIME=@s ) S on B.BNO=S.BNO
left join (select BNO, BDATA from C where BTIME=@e ) E on B.BNO=E.BNO
group by B.AID/*
AID result
----------- ---------------------------------------
777 2.0
888 2.0(2 行受影响)
*/DROP TABLE A,B,C
select @s='2008-08-08 11:00:00', @e='2008-08-08 12:00:00'select AID, result=sum(case B.BID when 1 then S.BDATA-E.BDATA else E.BDATA-S.BDATA end)
from B left join (select BNO, BDATA from C where BTIME=@s ) S on B.BNO=S.BNO
left join (select BNO, BDATA from C where BTIME=@e ) E on B.BNO=E.BNO
group by B.AID/*
AID result
----------- ---------------------------------------
777 2.0
888 2.0(2 行受影响)
*/
INSERT @TB
SELECT 1, 888, 2 UNION ALL
SELECT 3, 888, 3 UNION ALL
SELECT 5, 888, 4 UNION ALL
SELECT 6, 888, 5 UNION ALL
SELECT 1, 777, 6 UNION ALL
SELECT 3, 777, 7 UNION ALL
SELECT 5, 777, 8 UNION ALL
SELECT 6, 777, 9DECLARE @TC TABLE(BNO INT, BDATA DECIMAL(8,1), BTIME DATETIME)
INSERT @TC
SELECT 2, 1.5, '2008-08-08 11:00:00' UNION ALL
SELECT 3, 1.6, '2008-08-08 11:00:00' UNION ALL
SELECT 4, 1.8, '2008-08-08 11:00:00' UNION ALL
SELECT 5, 1.9, '2008-08-08 11:00:00' UNION ALL
SELECT 6, 1.5, '2008-08-08 11:00:00' UNION ALL
SELECT 7, 1.6, '2008-08-08 11:00:00' UNION ALL
SELECT 8, 1.8, '2008-08-08 11:00:00' UNION ALL
SELECT 9, 1.9, '2008-08-08 11:00:00' UNION ALL
SELECT 2, 2.5, '2008-08-08 12:00:00' UNION ALL
SELECT 3, 2.6, '2008-08-08 12:00:00' UNION ALL
SELECT 4, 2.8, '2008-08-08 12:00:00' UNION ALL
SELECT 5, 2.9, '2008-08-08 12:00:00' UNION ALL
SELECT 6, 2.5, '2008-08-08 12:00:00' UNION ALL
SELECT 7, 2.6, '2008-08-08 12:00:00' UNION ALL
SELECT 8, 2.8, '2008-08-08 12:00:00' UNION ALL
SELECT 9, 2.9, '2008-08-08 12:00:00'SELECT SUM(Result) as Result
FROM (
SELECT CASE WHEN B.BID=1 THEN -1*BDATA+ISNULL((SELECT BDATA FROM @TC WHERE BNO=A.BNO AND BTIME>A.BTIME),0)
ELSE BDATA-ISNULL((SELECT BDATA FROM @TC WHERE BNO=A.BNO AND BTIME>A.BTIME),0)
END AS Result
FROM @TC AS C JOIN
(
SELECT B.BNO,MIN(BTIME) AS BTIME
FROM @TB AS B JOIN @TC AS C ON B.BNO=C.BNO
WHERE AID=777
GROUP BY B.BNO
) A
ON A.BNO=C.BNO AND A.BTIME=C.BTIME
JOIN @TB AS B ON A.BNO=B.BNO
) T
/*
Result
----------------------------------------
-2.0
*/
--> By dobear_0922(小熊) 2008-11-21 17:46:14
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([AID] int)
insert [A]
select 888 union all
select 777
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([BID] int,[AID] int,[BNO] int)
insert [B]
select 1,888,2 union all
select 3,888,3 union all
select 5,888,4 union all
select 6,888,5 union all
select 1,777,6 union all
select 3,777,7 union all
select 5,777,8 union all
select 6,777,9
--> 测试数据:[C]
if object_id('[C]') is not null drop table [C]
create table [C]([BNO] int,[BDATA] numeric(2,1),[BTIME] datetime)
insert [C]
select 2,1.5,'2008-08-08 11:00:00' union all
select 3,1.6,'2008-08-08 11:00:00' union all
select 4,1.8,'2008-08-08 11:00:00' union all
select 5,1.9,'2008-08-08 11:00:00' union all
select 6,1.5,'2008-08-08 11:00:00' union all
select 7,1.6,'2008-08-08 11:00:00' union all
select 8,1.8,'2008-08-08 11:00:00' union all
select 9,1.9,'2008-08-08 11:00:00' union all
select 2,2.5,'2008-08-08 12:00:00' union all
select 3,2.6,'2008-08-08 12:00:00' union all
select 4,2.8,'2008-08-08 12:00:00' union all
select 5,2.9,'2008-08-08 12:00:00' union all
select 6,2.5,'2008-08-08 12:00:00' union all
select 7,2.6,'2008-08-08 12:00:00' union all
select 8,2.8,'2008-08-08 12:00:00' union all
select 9,2.9,'2008-08-08 12:00:00'DECLARE @s datetime, @e datetime
select @s='2008-08-08 11:00:00', @e='2008-08-08 12:00:00'select AID, result=sum(case B.BID when 1 then E.BDATA-S.BDATA else S.BDATA-E.BDATA end)
from B left join (select BNO, BDATA from C where BTIME=@s ) S on B.BNO=S.BNO
left join (select BNO, BDATA from C where BTIME=@e ) E on B.BNO=E.BNO
group by B.AID/*
AID result
----------- ---------------------------------------
777 -2.0
888 -2.0(2 行受影响)
*/drop table A,B