27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :借测试数据一用
-- Date :2014-04-17 17:02:48
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[usercode] int,[time] int,[detail] nvarchar(4))
insert [A]
select 1,1001,13343432,N'内容A1' union all
select 2,1002,14332233,N'内容A2'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[usercode] int,[time] int,[detail] nvarchar(4))
insert [B]
select 1,1001,1234232111,N'内容B2'
--------------生成数据--------------------------
select A.usercode,A.time-ISNULL(B.time,0),A.detail
from A
left join (select usercode,sum(time) as time from B group by usercode) B on A.usercode=B.usercode
----------------结果----------------------------
/*
usercode detail
----------- ----------- ------
1001 -1220888679 内容A1
1002 14332233 内容A2
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-17 17:02:48
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[usercode] int,[time] int,[detail] nvarchar(4))
insert [A]
select 1,1001,13343432,N'内容' union all
select 2,1002,14332233,N'内容'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[usercode] int,[time] int,[detail] nvarchar(4))
insert [B]
select 1,1001,1234232111,N'内容'
--------------生成数据--------------------------
SELECT usercode,SUM([time])[time],detail
FROM (
select usercode,[time],detail from [A]
UNION
select usercode,-1*[time],detail from [B]
)a
GROUP BY usercode,detail
----------------结果----------------------------
/*
usercode time detail
----------- ----------- ------
1001 -1220888679 内容
1002 14332233 内容
*/