select中有sum时，如何写，性能比较优化？高手帮忙！

[zhjian6]

A表：
aid　atype aname
1 1 abc
2 2 bcd
3 2 def
4 1 ccc
5 3 ddd
6 3 wzy
字段：aid　为int4，自动加1。atype　为int4，分类。aname　char20 ，名称。

B表：
bid aid bcount
1 2 100
2 3 200
3 4 50
4 5 120
5 3 140
6 6 300
7 4 210
8 2 110
字段：bid　为int4，自动加1。aid 为A表中aid的值。bcount　为数值，float8。

要得到的结果是：
A.atype A.aname B.bcount
1 abc 0
1 ccc 250
2 bcd 210
2 def 340
3 ddd 120
3 wzy 300

就是从B表中找到A.aid相同的记录，把B.bcount相加，再根据A.atype把它们排列出记录。
怎么写，比较快？

[子陌红尘]

select
a.atype,
a.aname,
bcount = isnull(sum(B.bcount),0)
from
A
left join
B
on
A.aid = b.aid
group by
a.type,a.aname
order by
a.type,a.aname

[zhjian6]

bcount = isnull(sum(B.bcount),0)
有错

[GRLD8888]

select atype,aname,sum(isnull(bcount,0)) from a left join b on a.aid=b.aid
group by atype,b.aid,aname order by atype

[GRLD8888]

select atype,aname,sum(isnull(bcount,0)) from a left join b on a.aid=b.aid
group by atype,aname order by atype

[zhjian6]

上面是可以执行，但是想sum(isnull(bcount,0)) 值为0的，就不用列出来了

[paoluo]

--建立测试环境
Create Table A
(aid Int Identity(1,1),
atype Int,
aname char(20))
Create Table B
(bid Int Identity(1,1),
aid Int,
bcount float(8))
--插入数据
Insert A Values(1, 'abc')
Insert A Values(2, 'bcd')
Insert A Values(2, 'def')
Insert A Values(1, 'ccc')
Insert A Values(3, 'ddd')
Insert A Values(3, 'wzy')

Insert B Values(2, 100)
Insert B Values(3, 200)
Insert B Values(4, 50)
Insert B Values(5, 120)
Insert B Values(3, 140)
Insert B Values(6, 300)
Insert B Values(4, 210)
Insert B Values(2, 110)
--测试
Select
A.atype,
Rtrim(A.aname) As aname ,
IsNull(SUM(B.bcount),0) As bcount
from A
Left Join B On A.aid=B.aid
Group By A.atype,A.aname
Order By A.atype,A.aname
--删除测试环境
Drop Table A,B
--结果
/*
atype aname bcount
1 abc 0.0
1 ccc 260.0
2 bcd 210.0
2 def 340.0
3 ddd 120.0
3 wzy 300.0
*/

[paoluo]

为0 的不列出来

--建立测试环境
Create Table A
(aid Int Identity(1,1),
atype Int,
aname char(20))
Create Table B
(bid Int Identity(1,1),
aid Int,
bcount float(8))
--插入数据
Insert A Values(1, 'abc')
Insert A Values(2, 'bcd')
Insert A Values(2, 'def')
Insert A Values(1, 'ccc')
Insert A Values(3, 'ddd')
Insert A Values(3, 'wzy')

Insert B Values(2, 100)
Insert B Values(3, 200)
Insert B Values(4, 50)
Insert B Values(5, 120)
Insert B Values(3, 140)
Insert B Values(6, 300)
Insert B Values(4, 210)
Insert B Values(2, 110)
--测试
Select
A.atype,
Rtrim(A.aname) As aname ,
IsNull(SUM(B.bcount),0) As bcount
from A
Inner Join B On A.aid=B.aid
Group By A.atype,A.aname
Order By A.atype,A.aname
--删除测试环境
Drop Table A,B
--结果
/*
atype aname bcount
1 ccc 260.0
2 bcd 210.0
2 def 340.0
3 ddd 120.0
3 wzy 300.0
*/

[summerICEREDTEA]

Select
A.atype,
A.aname aname ,
IsNull(SUM(B.bcount),0) bcount
from A
Left Join B On A.aid=B.aid
Group By A.atype,A.aname

[MorningTea]

create table #a(aid int,atype int, aname char(20))
insert into #a values(1,1,'abc')
insert into #a values(2,2,'bcd')
insert into #a values(3,2,'def')
insert into #a values(4,1,'ccc')
insert into #a values(5,3,'ddd')
insert into #a values(6,3,'wzy')
insert into #a values(1,1,'abc')

create table #b (bid int,aid int,bcount float)
insert into #b values(1,2,100)
insert into #b values(2,2,200)
insert into #b values(3,4,50)
insert into #b values(4,5,120)
insert into #b values(5,3,140)
insert into #b values(6,6,300)
insert into #b values(7,4,210)
insert into #b values(8,2,110)

select * from #a
select * from #b

/*
aid atype aname
----------- ----------- --------------------
1 1 abc
2 2 bcd
3 2 def
4 1 ccc
5 3 ddd
6 3 wzy
1 1 abc

(7 row(s) affected)

bid aid bcount
----------- ----------- -----------------------------------------------------
1 2 100.0
2 2 200.0
3 4 50.0
4 5 120.0
5 3 140.0
6 6 300.0
7 4 210.0
8 2 110.0

(8 row(s) affected)
*/


select a.atype,a.aname,sum(isnull(b.bcount,0)) from #a a
left join #b b on
a.aid = b.aid
group by a.aid,a.atype,a.aname
order by a.atype,a.aname

/*
atype aname
----------- -------------------- -----------------------------------------------------
1 abc 0.0
1 ccc 260.0
2 bcd 410.0
2 def 140.0
3 ddd 120.0
3 wzy 300.0

(6 row(s) affected)
*/

[zhjian6]

就不要建临时表了，太麻烦了！就说说：如何不让，加起来为0的值列出来！

[MorningTea]

1。实在不好意思，由于手误，keyin的测试数据和搂主不一样，导致我的结果不对，后来我一个个去试其他人，好像也不对，才发觉，自己的失误：更正
create table #a(aid int,atype int, aname char(20))
insert into #a values(1,1,'abc')
insert into #a values(2,2,'bcd')
insert into #a values(3,2,'def')
insert into #a values(4,1,'ccc')
insert into #a values(5,3,'ddd')
insert into #a values(6,3,'wzy')

create table #b (bid int,aid int,bcount float)
insert into #b values(1,2,100)
insert into #b values(2,3,200)
insert into #b values(3,4,50)
insert into #b values(4,5,120)
insert into #b values(5,3,140)
insert into #b values(6,6,300)
insert into #b values(7,4,210)
insert into #b values(8,2,110)

/*
aid atype aname
----------- ----------- --------------------
1 1 abc
2 2 bcd
3 2 def
4 1 ccc
5 3 ddd
6 3 wzy

(6 row(s) affected)

bid aid bcount
----------- ----------- -----------------------------------------------------
1 2 100.0
2 3 200.0
3 4 50.0
4 5 120.0
5 3 140.0
6 6 300.0
7 4 210.0
8 2 110.0

(8 row(s) affected)
*/

select a.atype,a.aname,sum(isnull(b.bcount,0)) from #a a
left join #b b on
a.aid = b.aid
group by a.atype,a.aname
order by a.atype,a.aname
/*
atype aname
----------- -------------------- -----------------------------------------------------
1 abc 0.0
1 ccc 260.0
2 bcd 210.0
2 def 340.0
3 ddd 120.0
3 wzy 300.0

(6 row(s) affected)
*/

2. libin_ftsafe(子陌红尘) ( ) 信誉：100 的这样结果对的，会有一个warning,因为你先sum()，遇到null,系统会略过，可是你又要修出对应的a.atype,a.aname,那么系统会给你一个warning,按照他的思路，修改了一下
select
a.atype,
a.aname,
bcount = isnull(
(select sum(B1.bcount) from #b b1 where b1.aid = a.aid group by b1.aid)
,0)
from
#a a
left join
#b b
on
A.aid = b.aid
group by a.aida.atype,a.aname
order by
a.atype,a.aname
结果ok,也不会有warning!!
3.看到回复人： paoluo(一天到晚游泳的鱼) ( ) 信誉：100 ,哦，好像我用的也是一样的:p
我刚想到一个，就按我刚才修改libin_ftsafe时候得到的灵感
select
a.atype,
a.aname,
bcount = isnull(
(select sum(isnull(B1.bcount,0)) from #b b1 where b1.aid = a.aid)
,0)
from
#a a
order by a.atype,a.aname
反正a表的aid是pk,那么select a.atype,
a.aname
from a也就是结果表的前面两个栏位，然后第三个栏位再去加总，因为我在selece sum(isnull..
用了isnull，所以就是null，也是ok~

[MorningTea]

回复人： zhjian6(明年今日，让我们继续一起欢笑吧！) ( ) 信誉：100 2005-07-25 21:19:00 得分: 0


就不要建临时表了，太麻烦了！就说说：如何不让，加起来为0的值列出来！



---我们都没有建立临时表呀~，如果你说如何不让加起来为0的值列出来！（应该指的是为null的值吧）除非你有负数，正数相加，才会等于0,前面的0其实是null,如果你的意思是这样，那么我们不要让null值得情况出现即可：

select a.atype,a.aname,sum(bcount)
from #a a
inner join
#b b
on a.aid = b.aid
group by a.atype,a.aname
order by a.atype,a.aname
atype aname
----------- -------------------- -----------------------------------------------------
1 ccc 260.0
2 bcd 210.0
2 def 340.0
3 ddd 120.0
3 wzy 300.0

(5 row(s) affected)
这个比你列出来的结果表要简单

[paoluo]

回复人： zhjian6(明年今日，让我们继续一起欢笑吧！) ( ) 信誉：100 2005-07-25 21:19:00 得分: 0


就不要建临时表了，太麻烦了！就说说：如何不让，加起来为0的值列出来！


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
你可能没仔细看我写的两段代码，使用Inner Join就可以让为0 的值不列出来。

我上面的第二段代码就是啊，而且根本没用什么临时表。

--测试
Select
A.atype,
Rtrim(A.aname) As aname ,
IsNull(SUM(B.bcount),0) As bcount
from A
Inner Join B On A.aid=B.aid
Group By A.atype,A.aname
Order By A.atype,A.aname
--结果
/*
atype aname bcount
1 ccc 260.0
2 bcd 210.0
2 def 340.0
3 ddd 120.0
3 wzy 300.0
*/

[zhjian6]

呵呵，谢谢各位，搞定了，有可能a表中有的，b 表中没有，所以就会有0值
把　from b 然再　join a　就可以了
再问一下，这样会不会减少效率？

[paoluo]

Inner Join是SQL的关键字，不会影响效率的。

[rivery]

来晚了。既然这么热闹，就顶一下。

[MorningTea]

rivery(river) ,的確晚了，蚊子都睡觉了~
我也要睡觉了,hehe