关于数据库中使用 left join on ...and ...和 left join on ....where ...区别,和使用group by 要注意的情...

关于数据库中使用 left join on ...and ...和 left join on ....where ...区别,和使用group by 要注意的情况

create table UserAccount
(
UID int,
UName varchar(50),
JoinDate datetime
)


insert into UserAccount values(1,'Jimmy','2006-10-3')
insert into UserAccount values(2,'Jack','2006-10-25')
insert into UserAccount values(3,'Mike','2006-11-13')
insert into UserAccount values(4,'Tom','2006-9-5')


create table ChargeHistory(UId int,ChargeDate datetime,Amount int)

insert into ChargeHistory values(1,'2007-3-1',50)
insert into ChargeHistory values(1,'2007-2-15', 30)
insert into ChargeHistory values(2,'2007-2-1' ,40)
insert into ChargeHistory values(3,'2007-1-15' ,60)
insert into ChargeHistory values(2,'2007-1-15' ,50)
insert into ChargeHistory values(3,'2007-1-1' ,100)
insert into ChargeHistory values(1,'2006-12-20' ,60)


select * from UserAccount
select * from ChargeHistory

1
Jimmy 2006-10-03 00:00:00.000 2
Jack 2006-10-25 00:00:00.000 3
Mike 2006-11-13 00:00:00.000 4
Tom 2006-09-05 00:00:00.000

1
2007-03-01 00:00:00.000 50 1
2007-02-15 00:00:00.000 30 2
2007-02-01 00:00:00.000 40 3
2007-01-15 00:00:00.000 60 2
2007-01-15 00:00:00.000 50 3
2007-01-01 00:00:00.000 100 1
2006-12-20 00:00:00.000 60





假设有一个游戏帐户充值网站

系统内有两个表

UserAccount

UID
UName
JoinDate

1
Jimmy
2006-10-3

2
Jack
2006-10-25

3
Mike
2006-11-13

4
Tom
2006-9-5





ChargeHistory

UId
ChargeDate
Amount

1
2007-3-1
50

1
2007-2-15
30

2
2007-2-1
40

3
2007-1-15
60

2
2007-1-15
50

3
2007-1-1
100

1
2006-12-20
60







1. 建表并填充数据

2. 完成如下操作:

A. 查询用户名为mike的充值记录,包含如下列

UName,UId,JoinDate,ChargeDate,Amount

B. 查询用户Id为1共充值多少金额.包含

UName,UId,JoinDate,Amount

C. 统计每个用户的充值总额,结果如下

UId
UName
Amount
JoinDate

1
Jimmy
140
2006-10-3

2
Jack
90
2006-10-25

3
Mike
160
2006-11-13

4
Tom
Null
2006-9-5







select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
left join ChargeHistory B on A.UID=B.UID and a.uname='mike' 这里的结果是:不是我们想要的结果因为:

--通过以下结果我们可以看出:左连接是left join 嘛,无非是左边表为基础, 扫描右边表匹配的记录
--按条件 a.uname='mike', 来扫描右边表的记录
--对于右边表的每条记录, 显然 a.uname='mike' 这个条件都是成立.


Jimmy 2006-10-03 00:00:00.000 NULL NULL 2
Jack 2006-10-25 00:00:00.000 NULL NULL 3
Mike 2006-11-13 00:00:00.000 2007-01-15 00:00:00.000 60 3
Mike 2006-11-13 00:00:00.000 2007-01-01 00:00:00.000 100 4
Tom 2006-09-05 00:00:00.000 NULL NULL







select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
left join ChargeHistory B on A.UID=B.UID where A.UNAME='Mike'所以这里要使用where .where是left join 后筛选结果.

结果:

Mike 2006-11-13 00:00:00.000 2007-01-15 00:00:00.000 60 3
Mike 2006-11-13 00:00:00.000 2007-01-01 00:00:00.000 100 4

--但是inner join 是选择符合条件的数据出来
select A.UID,A.UName,A.JoinDate,B.ChargeDate,B.Amount from UserAccount A
inner join ChargeHistory B on A.UID=B.UID and a.uname='mike'

--group by 的操作:
--有函数存在 :sum,count,min,avg,max
--slecect多少个字段,group by 多少个字段
select A.UID,A.UName,A.JoinDate,sum(B.amount) from UserAccount A left join ChargeHistory B ON A.uID=B.UID WHERE A.UID=1
group by a.uid,A.UName,A.JoinDate



--这里还要注意 having的用法:是用来在group by 后再筛选数据的
select A.UID,A.UName,A.JoinDate,sum(B.amount) from UserAccount A left join ChargeHistory B ON A.uID=B.UID
group by a.uid,A.UName,A.JoinDate
having A.UID=1





转于：http://www.cnblogs.com/tinachang021/archive/2008/09/25/1298949.html