在上次中,http://topic.csdn.net/u/20080425/20/3616e13a-579e-4ba9-ba0b-538781bd10cf.html
如果将S表,G表和加一个数据.就会出现问题:create table S(ID int, name varchar(10))
insert into S values(1 , '第一板数')
insert into S values(2 , '第二板数')
insert into S values(3 , '第三板数')
create table X(ID int, name varchar(10) , sname varchar(10))
insert into X values(1 , 'A箱' , '第一板数')
insert into X values(2 , 'B箱' , '第一板数')
insert into X values(3 , 'C箱' , '第二板数')
insert into X values(4 , 'E箱' , '第二板数')
insert into X values(5 , 'F箱' , '第二板数')
create table G(ID int, name varchar(10) , xname varchar(10))
insert into G values(1 , 'L个' , 'A箱')
insert into G values(2 , 'p个' , 'A箱')
insert into G values(3 , 'm个' , 'B箱')
insert into G values(4 , 'T个' , 'A箱')
insert into G values(5 , 'V个' , 'C箱')
insert into G values(6 , 'Q个' , 'D箱')
insert into G values(7 , 'N个' , 'F箱')
insert into G values(8 , 'L个' , '')
goselect S.* , count(distinct X.name) [箱数(count)], count(g.xname) [个数(count)] from S
left outer join X on S.name = X.sname
left outer join G on X.name = G.xname
group by S.id , S.name
order by S.id , S.name所以这次换一个真实表与数据
表:tblManage
MID Mcode Mname
-----------------------
1 M10001 Manager01
2 M10002 Manager02
3 M10003 Manager03
4 M10004 Manager04
表:tblTrader
TID Tcode Tname Mane TTotal TMax
----------------------------------------------------------
20 T100001 Trader01 Manager01 80000.00 78000.00
21 T100002 Trader02 Manager02 5000.00 3000.00
22 T100003 Trader03 Manager03 1.00 1.00
23 T100004 Trader04 Manager03 1.00 1.00
24 T100005 Trader05 Manager01 888.00 800.00
表:tblAccount
AID Aweb Aname Tname
-----------------------------------------
104 1.com user02 trader01
105 2.com user01 trader02
106 2.com user02 trader01
107 2.com user03 NULL
108 2.com user04 trader02
109 2.com user05 trader02
110 1.com user01 trader03
结果:
Mname Trader(count) tatal(sum) max(sum) Account(count)
-------------------------------------------------------------------
Manager01 2 80888 78800 3
Manager02 1 5000 3000 2
Manager03 2 2 2 1
Manager03 0 0 0 0自己愚笨.只有再次谢谢各位...
如果将S表,G表和加一个数据.就会出现问题:create table S(ID int, name varchar(10))
insert into S values(1 , '第一板数')
insert into S values(2 , '第二板数')
insert into S values(3 , '第三板数')
create table X(ID int, name varchar(10) , sname varchar(10))
insert into X values(1 , 'A箱' , '第一板数')
insert into X values(2 , 'B箱' , '第一板数')
insert into X values(3 , 'C箱' , '第二板数')
insert into X values(4 , 'E箱' , '第二板数')
insert into X values(5 , 'F箱' , '第二板数')
create table G(ID int, name varchar(10) , xname varchar(10))
insert into G values(1 , 'L个' , 'A箱')
insert into G values(2 , 'p个' , 'A箱')
insert into G values(3 , 'm个' , 'B箱')
insert into G values(4 , 'T个' , 'A箱')
insert into G values(5 , 'V个' , 'C箱')
insert into G values(6 , 'Q个' , 'D箱')
insert into G values(7 , 'N个' , 'F箱')
insert into G values(8 , 'L个' , '')
goselect S.* , count(distinct X.name) [箱数(count)], count(g.xname) [个数(count)] from S
left outer join X on S.name = X.sname
left outer join G on X.name = G.xname
group by S.id , S.name
order by S.id , S.name所以这次换一个真实表与数据
表:tblManage
MID Mcode Mname
-----------------------
1 M10001 Manager01
2 M10002 Manager02
3 M10003 Manager03
4 M10004 Manager04
表:tblTrader
TID Tcode Tname Mane TTotal TMax
----------------------------------------------------------
20 T100001 Trader01 Manager01 80000.00 78000.00
21 T100002 Trader02 Manager02 5000.00 3000.00
22 T100003 Trader03 Manager03 1.00 1.00
23 T100004 Trader04 Manager03 1.00 1.00
24 T100005 Trader05 Manager01 888.00 800.00
表:tblAccount
AID Aweb Aname Tname
-----------------------------------------
104 1.com user02 trader01
105 2.com user01 trader02
106 2.com user02 trader01
107 2.com user03 NULL
108 2.com user04 trader02
109 2.com user05 trader02
110 1.com user01 trader03
结果:
Mname Trader(count) tatal(sum) max(sum) Account(count)
-------------------------------------------------------------------
Manager01 2 80888 78800 3
Manager02 1 5000 3000 2
Manager03 2 2 2 1
Manager03 0 0 0 0自己愚笨.只有再次谢谢各位...
insert into tblManage select 1, 'M10001', 'Manager01'
insert into tblManage select 2, 'M10002', 'Manager02'
insert into tblManage select 3, 'M10003', 'Manager03'
insert into tblManage select 4, 'M10004', 'Manager04' create table tblTrader (TID int,TCode varchar(10),TName varchar(10),Mane varchar(10),TTotal decimal(15,2),TMax decimal(15,2))
insert into tblTrader select 20, 'T100001' , 'Trader01', 'Manager01', 80000.00, 78000.00
insert into tblTrader select 21, 'T100002' , 'Trader02', 'Manager02', 5000.00 , 3000.00
insert into tblTrader select 22, 'T100003' , 'Trader03', 'Manager03', 1.00 , 1.00
insert into tblTrader select 23, 'T100004' , 'Trader04', 'Manager03', 1.00 , 1.00
insert into tblTrader select 24, 'T100005' , 'Trader05', 'Manager01', 888.00 , 800.00 create table tblAccount(AID int,AWeb varchar(10),AName varchar(10),TName varchar(10))
insert into tblAccount select 104 , '1.com', 'user02' , 'trader01'
insert into tblAccount select 105 , '2.com', 'user01' , 'trader02'
insert into tblAccount select 106 , '2.com', 'user02' , 'trader01'
insert into tblAccount select 107 , '2.com', 'user03' , NULL
insert into tblAccount select 108 , '2.com', 'user04' , 'trader02'
insert into tblAccount select 109 , '2.com', 'user05' , 'trader02'
insert into tblAccount select 110 , '1.com', 'user01' , 'trader03' goselect distinct S.MName,isnull(X.Trader,0) Trader,rtrim(isnull(X.TTotal,0)) TTotal,
rtrim(isnull(X.TMax,0)) TMax,(select count(*) from tblAccount a join tblTrader b on a.TName=b.TName where b.Mane=s.MName) [AccCount]
from tblManage S
left outer join
(select Mane,sum(TTotal) TTotal,count(*) Trader,sum(TMax) TMax from tblTrader group by Mane) X
on S.MName=X.Manego
drop table tblManage,tblTrader,tblAccount/*
MName Trader TTotal TMax AccCount
---------- ----------- ------------------ ------------------------ -----------
Manager01 2 80888.00 78800.00 2
Manager02 1 5000.00 3000.00 3
Manager03 2 2.00 2.00 1
Manager04 0 0.00 0.00 0(所影响的行数为 4 行)
*/
insert into tblManage values(1,'M10001', 'Manager01' )
insert into tblManage values(2,'M10002', 'Manager02' )
insert into tblManage values(3,'M10003', 'Manager03' )
insert into tblManage values(4,'M10004', 'Manager04' )create table tblTrader((TID int,Tcode varchar(20),Tcode varchar(20),Mname varchar(20),TTotal decimal(18,2),TMax decimal(18,2))
insert into tblTrader values(20,'T100001','Trader01','Manager01','80000.00','78000.00')
insert into tblTrader values(21, 'T100002', 'Trader02' ,'Manager02','5000.00','3000.00')
insert into tblTrader values(22, 'T100003', 'Trader03', 'Manager03', '1.00,'1.00')
insert into tblTrader values(23, 'T100004' , 'Trader04', 'Manager03', '1.00,'1.00')
insert into tblTrader values(24, 'T100005' , 'Trader05'' 'Manager01', '888.00,''800.00') create table tblAccount(AID INT,Aweb varchar(20),Aname varchar(20),Tname varchar(20))
insert into tblAccount values(104,'1.com',' user02','trader01')
insert into tblAccount values(105,'2.com',' user01','trader02')
insert into tblAccount values(106,'2.com',' user02','trader01')
insert into tblAccount values(107,'2.com',' user03','NULL')
insert into tblAccount values(108,'2.com ','user04','trader02')
insert into tblAccount values(109,'2.com','user05','trader02')
insert into tblAccount values(110,'1.com','user01','trader03')
谢谢
insert into tblManage values(1,'M10001', 'Manager01' )
insert into tblManage values(2,'M10002', 'Manager02' )
insert into tblManage values(3,'M10003', 'Manager03' )
insert into tblManage values(4,'M10004', 'Manager04' )create table tblTrader((TID int,Tcode varchar(20),Tcode varchar(20),Mname varchar(20),TTotal decimal(18,2),TMax decimal(18,2))
insert into tblTrader values(20,'T100001','Trader01','Manager01','80000.00','78000.00')
insert into tblTrader values(21, 'T100002', 'Trader02' ,'Manager02','5000.00','3000.00')
insert into tblTrader values(22, 'T100003', 'Trader03', 'Manager03', '1.00,'1.00')
insert into tblTrader values(23, 'T100004' , 'Trader04', 'Manager03', '1.00,'1.00')
insert into tblTrader values(24, 'T100005' , 'Trader05'' 'Manager01', '888.00,''800.00') create table tblAccount(AID INT,Aweb varchar(20),Aname varchar(20),Tname varchar(20))
insert into tblAccount values(104,'1.com',' user02','trader01')
insert into tblAccount values(105,'2.com',' user01','trader02')
insert into tblAccount values(106,'2.com',' user02','trader01')
insert into tblAccount values(107,'2.com',' user03','NULL')
insert into tblAccount values(108,'2.com ','user04','trader02')
insert into tblAccount values(109,'2.com','user05','trader02')
insert into tblAccount values(110,'1.com','user01','trader03')
谢谢