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
