请教一个 SQL 查询语句

显示全部楼层 · 2008-6-28 11:38:42

计算每位销售员的订单数
表salesperson:
name
age
salary
Able
..
..
Baker
..
..
Jones
..
..
Murphy
..
..
Zenith
..
..
Kobad
..
..
表ordered:
Number
salespersonname
Amount
100
Zenith
560
200
Jones
1800
300
Able
480
400
Able
2500
500
Murphy
6000
600
Able
700
700
Jones
150
我的语句是:select salespersonname,sum(distinct amount) 订单数

from ordered group by salespersonname
执行结果为:salespersonname
订单数

Able
3680

Jones
1950

Murphy
6000

Zeith
560
若我想让执行的结果为下面这样,那SQL语句该怎么样来写,请各位指教.

salespersonname
订单数

Able
3680

Jones
1950

Murphy
6000

Zeith
560

Baker
0

Kobad
0
(显示顺序无所谓)

千问 · 2008-6-28 11:38:42

select salesperson.salespersonname,order.sum(distinct amount) 订单数 from salesperson left outer join order onsalesperson.name =order.salespersonname group by salesperson.salespersonname