left join on ...and ... 与left join on ... where.. 的区别

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 还是right join 的时候 on 后面的只允许是两个表中的关联字段，多个的话使用and连接，如果你写了某一个表的条件在里面，就像上面的例子，其实a.uname='mike'是不起作用的。所以在使用left join 和right join的时候需要注意，如果不是两个表的关联字段都需要写在where语句的后面。


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