group by 意义

关于groupby

1> select * from bank_info
2> go
bankno acctround
—— ———
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround=’1′ group by bankno order by bankno
2> go
bankno acctround
—— ———
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround=’1′ order by bankno
2> go
bankno acctround
—— ———
123456 1

(1 row affected)
1> select * from bank_info
2> go
bankno acctround
—— ———
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround=’1′ group by bankno,acctround order by bankno
2> go
bankno acctround
—— ———
123456 1

(1 row affected)

group by bankno,acctround的意思是找出bankno,acctround两列完全相同的不同行作为一组.那上面的数据就分成两组了,因为acctround不同,而结果需要acctround=’1’的组.所以只有一行结果.如果是group by bankno,那么两行会合成一行.它并没有先通过where分析出只有一行符合结果集,再group就只有一行结果了.这里的问题是where和group谁先谁后分析的问题?在这个问题的上下文中,本来的group by bankno就没有任何意义.其实是这样的:

select bankno,sum(money) from bankdiff group by bankno;

select bankno,acctround,money from bankdiff where acctround=’1′ group by bankno,acctround;

1> select * from bank_info group by bankno
2> go
bankno acctround
—— ———
123456 1
123456 2