A 表ID,Type,TypeNo,batchMoney
1 WWW 001 1000
2 WWW 002 3000
3 CCC 003 4000B表Type,TypeNo Money
WWW 001 500
WWW 001 10
WWW 001 20
WWW 002 200
WWW 002 300
WWW 003 400
结果:
Type,TypeNo,batchMoney TotalMoney( sum(Money) )
WWW 001 1000 530
WWW 002 3000 500
WWW 003 4000 400我的方法
Select A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) as TotalMoney
From A Left join B ON
A.Type=B.Type and A.TypeNo = B.typeNoGroup by
A.Type,A.TypeNo,A.batchMoney,
B.Type,B.TypeNo我的方法好像非常笨,B数据一旦非常庞大,Group这么多列,很慢,搞不懂怎么优化!那位帮忙优化一下
1 WWW 001 1000
2 WWW 002 3000
3 CCC 003 4000B表Type,TypeNo Money
WWW 001 500
WWW 001 10
WWW 001 20
WWW 002 200
WWW 002 300
WWW 003 400
结果:
Type,TypeNo,batchMoney TotalMoney( sum(Money) )
WWW 001 1000 530
WWW 002 3000 500
WWW 003 4000 400我的方法
Select A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) as TotalMoney
From A Left join B ON
A.Type=B.Type and A.TypeNo = B.typeNoGroup by
A.Type,A.TypeNo,A.batchMoney,
B.Type,B.TypeNo我的方法好像非常笨,B数据一旦非常庞大,Group这么多列,很慢,搞不懂怎么优化!那位帮忙优化一下
看看
From A
Left join (select Type,TypeNo,sum(Money) TotalMoney from b group by Type,TypeNo) bb
ON A.Type=bb.Type and A.TypeNo = bb.typeNo
select a.*,
TotalMoney=(select sum(Money)from b where A.Type=B.Type and A.TypeNo = B.typeNo )
from a
FROM A LEFT JOIN
(
SELECT [Type],TypeNo,[Money]=sum([Money])
FROM B
GROUP BY [Type],TypeNo
) TB
ON A.Type=TB.Type and A.TypeNo = TB.typeNo
1 WWW 001 1000
2 WWW 002 3000
3 CCC 003 4000 B表 Type,TypeNo Money
WWW 001 500
WWW 001 10
WWW 001 20
WWW 002 200
WWW 002 300
WWW 003 400
结果:
Type,TypeNo,batchMoney TotalMoney( sum(Money) )
WWW 001 1000 530
WWW 002 3000 500
WWW 003 4000 400 我的方法
Select A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) as TotalMoney
From A Left join B ON
A.Type=B.Type and A.TypeNo = B.typeNo Group by
A.Type,A.TypeNo,A.batchMoney,
B.Type,B.TypeNo
Select A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) as TotalMoney
From A join B ON
A.Type=B.Type and A.TypeNo = B.typeNo Group by
A.Type,A.TypeNo,A.batchMoney
Select A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) over() as TotalMoney
,ROW_NUMBER() over(partition by A.type,A.typeno order by A.type) Rownumber
From @A A Left join @B B ON
A.Type=B.Type and A.TypeNo = B.typeNo)T where Rownumber = 1试试这样
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-07 21:28:49
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,Type VARCHAR(3),TypeNo VARCHAR(3),batchMoney INT)
INSERT INTO [tb]
SELECT 1,'WWW','001',1000 UNION ALL
SELECT 2,'WWW','002',3000 UNION ALL
SELECT 3,'CCC','003',4000select * from [tb]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-07 21:29:08
---------------------------------
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
go
CREATE TABLE [tc] (Type VARCHAR(3),TypeNo VARCHAR(3),Money INT)
INSERT INTO [tc]
SELECT 'WWW','001',500 UNION ALL
SELECT 'WWW','001',10 UNION ALL
SELECT 'WWW','001',20 UNION ALL
SELECT 'WWW','002',200 UNION ALL
SELECT 'WWW','002',300 UNION ALL
SELECT 'WWW','003',400
select t.type,s.typeno,s.batchmoney,t.totalmoney
from tb s,(select type,typeno,totalmoney=sum(money) from tc group by type,typeno) t
where s.typeno=t.typenotype typeno batchmoney totalmoney
WWW 001 1000 530
WWW 002 3000 500
WWW 003 4000 400
DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,Type VARCHAR(3),TypeNo VARCHAR(3),batchMoney INT)
INSERT INTO [tb]
SELECT 1,'WWW','001',1000 UNION ALL
SELECT 2,'WWW','002',3000 UNION ALL
SELECT 3,'CCC','003',4000select * from [tb]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-07 21:29:08
---------------------------------
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
go
CREATE TABLE [tc] (Type VARCHAR(3),TypeNo VARCHAR(3),Money INT)
INSERT INTO [tc]
SELECT 'WWW','001',500 UNION ALL
SELECT 'WWW','001',10 UNION ALL
SELECT 'WWW','001',20 UNION ALL
SELECT 'WWW','002',200 UNION ALL
SELECT 'WWW','002',300 UNION ALL
SELECT 'CCC','003',400--即要显示batchMoney原数,又要分组统计金额合计数,所以合计数通过子查询得到
select id,TYPE,typeno,batchmoney,TotalMoney=(select sum(Money)from tc a where A.Type=B.Type and A.TypeNo = B.typeNo )
from tb b
/*
id TYPE typeno batchmoney TotalMoney
1 WWW 001 1000 530
2 WWW 002 3000 500
3 CCC 003 4000 400
*/
2.分组聚合
3.LEFT JOIN
SQL如下:
Select distinct A.Type,A.TypeNo,A.batchMoney,Sum(B.Money) over(partition by b.typeno ) as TotalMoney
From A Left join B ON
A.Type=B.Type and A.TypeNo = B.typeNo
From A
Left join (select Type,TypeNo,sum(Money) TotalMoney from b group by Type,TypeNo) bb
ON A.Type=bb.Type and A.TypeNo = bb.typeNo