SELECT
sum(GetAmount),c.Amount
FROM
ZY_OA_AssignmentUsers a
INNER JOIN
ZY_OA_ProjectsCategory b ON a.TypeID = b.ID
INNER JOIN
ZY_OA_UsersInfo d ON a.UID = d.UID
INNER JOIN
(select ContractID,sum(Amount) from ZY_OA_ContractsInfo group by ContractID) c ON a.ContractID = c.ContractID我的语句是这样的 但是我知道是错的 我要的效果是这样的 主要涉及到2张表
我假设成 A B 2张A表 包含
字段
ID ContractID Amount
1 1 10000
2 12 20000
3 123 30000B表包含字段
ID ContractID GetAmout
1 1 1000
2 1 2000
2 12 3000
3 12 4000现在要的统计效果就是
sum(B.GetAmount)和 对应A表中出现的ContractID的sum(Amount)
比如上面2个数据 需要的查询结果就是
sum(B.GetAmount)=1000+2000+3000+4000,
b表中出现的ContractID的sum(Amount)=10000+2000 (即B表的ContractID 没有123 这个,就不会把123这个数据统计进去
不知道表达清楚没有 在线等 麻烦大家了
-- Author :SQL77(只为思齐老)
-- Date :2010-01-20 14:32:01
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([ID] int,[ContractID] int,[Amount] int)
insert #TB
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000
--> 测试数据:#TB1
if object_id('tempdb.dbo.#TB1') is not null drop table #TB1
go
create table #TB1([ID] int,[ContractID] int,[GetAmout] int)
insert #TB1
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000
--------------开始查询--------------------------
select ContractID,SUM(GetAmout)GetAmout from #TB1 T WHERE EXISTS(SELECT 1 FROM #TB WHERE ContractID=T.ContractID)
GROUP BY ContractID
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 4 行)ContractID GetAmout
----------- -----------
1 3000
12 7000(所影响的行数为 2 行)
*/
(select sum(Amount) from A where ContractID in (select ContractID from B)) as Amout_A
from B
if object_id('A') is not null drop table A
go
create table A([ID] int,[ContractID] int,[Amount] int)
insert A
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000
--> 测试数据:B
if object_id('B') is not null drop table B
go
create table B([ID] int,[ContractID] int,[GetAmout] int)
insert B
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000
select sum(GetAmout) as Amout_B,
(select sum(Amount) from A where ContractID in (select ContractID from B)) as Amout_A
from B ---------------------------
10000 30000
-- Author :SQL77(只为思齐老)
-- Date :2010-01-20 14:32:01
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([ID] int,[ContractID] int,[Amount] int)
insert #TB
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000
--> 测试数据:#TB1
if object_id('tempdb.dbo.#TB1') is not null drop table #TB1
go
create table #TB1([ID] int,[ContractID] int,[GetAmout] int)
insert #TB1
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000
--------------开始查询--------------------------
select SUM(GetAmout)GetAmout from #TB1 T WHERE EXISTS(SELECT 1 FROM #TB WHERE ContractID=T.ContractID)
--GROUP BY ContractID
----------------结果----------------------------
/*
(所影响的行数为 3 行)
(所影响的行数为 4 行)GetAmout
-----------
10000(所影响的行数为 1 行)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-20 14:36:20
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[ContractID] int,[Amount] int)
insert [A]
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[ContractID] int,[GetAmout] int)
insert [B]
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000
--------------开始查询--------------------------
select
sum(GetAmout) as Amout_B,
(select sum(Amount) from A t where exists(select ContractID from B where ContractID=t.ContractID)) as Amout_A
from
B
----------------结果----------------------------
/* Amout_B Amout_A
----------- -----------
10000 30000(1 行受影响)
*/
sum(GetAmount) as Amout_B,
(select sum(Amount) from ZY_OA_ContractsInfo t where exists(select ContractID from ZY_OA_AssignmentUsers where ContractID=t.ContractID)) as Amout_A
from
ZY_OA_AssignmentUsers我的语句是这样的
数据时这样的
ZY_OA_ContractsInfo
ID ContractID Amount
4 2009123011111 10000
5 2009123011112 90000
6 2009123011112 150000 ZY_OA_AssignmentUsers
ID ContractID GetAmount
1 2009123011111 1
2 2009123011111 22
3 2009123011111 333
4 2009123011112 4444
10 2009123011112 20100207 55555
可以了 谢谢啊
但是 如果我设置了条件查询要怎么改呢 我自己试了一下 发现不行 我的原来代码如下 select
sum(GetAmount) as Amout_B,
(select sum(Amount) from ZY_OA_ContractsInfo t where exists(select ContractID from ZY_OA_AssignmentUsers where ContractID=t.ContractID)) as Amout_A
from
ZY_OA_AssignmentUsers a
INNER JOIN
ZY_OA_ProjectsCategory b ON a.TypeID = b.ID
INNER JOIN
ZY_OA_ContractsInfo c ON a.ContractID = c.ContractID
INNER JOIN
ZY_OA_UsersInfo d ON a.UID = d.UID
where a.ContractID=@key or c.PartyA=@key or d.UserName=@key or b.ProjectsName=@key
if object_id('tempdb.dbo.#tb1') is not null
drop table #tb1
goif object_id('tempdb.dbo.#tb2') is not null
drop table #tb2
gocreate table #tb1
(id int,contractid int,amount int);
insert #tb1
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000create table #tb2
(id int,contractid int,getamount int);
insert #tb2
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000select * from #tb1
select * from #tb2select a.contractid,a.amount,b.getamount from
(
select contractid,sum(amount) as amount from #tb1 group by contractid) a
left join (
select contractid,sum(getamount) as getamount from #tb2 group by contractid) b
on a.contractid = b.contractid/*
contractid amount getamount
1 10000 3000
12 20000 7000
123 30000 NULL
*/