select isnull(SUM(netPaid),0.00) from dbo.F_AccountEntry a
right join dbo.RPT_resultA41 b
on a.CompanyID=b.CompanyID
and a.AcctID=b.acctid
and b.CompanyID=1 and b.userName='sally_law'
and a.FiscalDateID>-13 and a.FiscalDateID<0
group by b.acctid如上语句,我的B表在and b.CompanyID=1 and b.userName='sally_law' 条件下 只有66条数据,
但这样出来的如果却有77条之多,什么原因呢,是我的语句有问题吗?
right join dbo.RPT_resultA41 b
on a.CompanyID=b.CompanyID
and a.AcctID=b.acctid
and b.CompanyID=1 and b.userName='sally_law'
and a.FiscalDateID>-13 and a.FiscalDateID<0
group by b.acctid如上语句,我的B表在and b.CompanyID=1 and b.userName='sally_law' 条件下 只有66条数据,
但这样出来的如果却有77条之多,什么原因呢,是我的语句有问题吗?
如果怀疑不对的,把条件写到where中去
就如图片所示的,查询出以A表acctid为组的B表的closingAmtBase的总和,
最后得到的结果应该是acctid closingAmtBase
81 9506.98
82 183207.21
83 0.00
84 0.00
select SUM(closingAmtBase) ,b.acctid from tal a right join kk b
on a.acctid=b.acctid and a.companyid=b.companyid
group by b.acctid
sum acctid
--------------------- -----------
19013.96 81
366414.42 82
NULL 83
NULL 84
Warning: Null value is eliminated by an aggregate or other SET operation.(4 row(s) affected)
select *
看看结果是不是你想要的结果,多了哪些,哪些重复了,修改条件得到你需要的再修改为汇总语句,这样才能保证结果与你预想一样(当然搞清楚逻辑也可以一次写对)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-14 16:39:38
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([companyid] int,[acctid] int)
insert [A]
select 1,81 union all
select 1,82 union all
select 1,83 union all
select 1,84if object_id('[b]') is not null drop table [b]
go
create table [b]([companyid] int,[fiscaldateid] int,[acctid] int,[closingamtbase] numeric(8,2))
insert [b]
select 1,1,81,9506.98 union all
select 1,1,82,148151.56 union all
select 1,2,81,0.00 union all
select 1,2,82,11585.23 union all
select 1,3,81,0.00 union all
select 1,3,82,23470.42
--------------开始查询--------------------------SELECT a.acctid,SUM(ISNULL(closingamtbase,0))closingamtbase
FROM a LEFT JOIN b ON a.companyid=b.companyid AND a.acctid=b.acctid
GROUP BY a.acctid/*
acctid closingamtbase
----------- ---------------------------------------
81 9506.98
82 183207.21
83 0.00
84 0.00(4 行受影响)
*/
这不是和我的一样吗?就是两个表的位置互換了,改成rihgt join,为什么那样就出问题了?
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-14 16:39:38
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([companyid] int,[acctid] int)
insert [A]
select 1,81 union all
select 1,82 union all
select 1,83 union all
select 1,84if object_id('[b]') is not null drop table [b]
go
create table [b]([companyid] int,[fiscaldateid] int,[acctid] int,[closingamtbase] numeric(8,2))
insert [b]
select 1,1,81,9506.98 union all
select 1,1,82,148151.56 union all
select 1,2,81,0.00 union all
select 1,2,82,11585.23 union all
select 1,3,81,0.00 union all
select 1,3,82,23470.42
--------------开始查询--------------------------SELECT a.acctid,SUM(ISNULL(closingamtbase,0))closingamtbase
FROM b right JOIN a ON a.companyid=b.companyid AND a.acctid=b.acctid
GROUP BY a.acctid/*
acctid closingamtbase
----------- ---------------------------------------
81 9506.98
82 183207.21
83 0.00
84 0.00(4 行受影响)
*/
SELECT a.acctid,isnull(bb.closingAmtBase,0) closingAmtBase FROM a LEFT JOIN (
SELECT SUM(closingAmtBase) closingAmtBase ,acctid,companyid FROM b
GROUP BY acctid,companyid
) bb ON bb.companyid = a.companyid AND bb.acctid = a.acctid81 9506.98
82 183207.21
83 0.00
84 0.00
----------- ---------------------
81 19013.96
82 366414.42
83 0.00
84 0.00(4 row(s) affected)看来我的是神了
right join 完全造价于left join,你左右一换,比对一番,即可找出相异之处。