- 爱易网页
-
MSSQL教程
- SQL的复合查询 急该如何处理
日期:2014-05-18 浏览次数:20854 次
SQL的复合查询 急!
两个表A和B
表A 表B
----------------------- ---------------------------
时 间A | 奖金A | | 时 间B | 奖金B
----------------------- ----------------------------
2007-04-09 | 1500 | | 2007-04-10 | 500
----------------------- ------------------------------
2007-04-10 | 1000 | | 2007-04- 12 | 1000
----------------------- -------------------------------
2007-04-11 | 500 |
-----------------------
现在要查询出以下结果
---------------------------------
时 间 | 奖金A | 奖金B |
---------------------------------
2007-04-09 | 1500 | |
---------------------------------
2007-04-10 | 1000 | 500 |
---------------------------------
2007-04-11 | 500 | |
---------------------------------
2007-04-12 | | 1000 |
---------------------------------
注意: 表A的行数不一定会比表B多
------解决方案--------------------
select A.时间A,A.奖金A,B.奖金B from A,B where A.时间A=B.时间B
union select A.时间A,A.奖金A,B.奖金B from A left outer join B on A.时间A=B.时间B
union select B.时间A,A.奖金A,B.奖金B from A right outer join B on A.时间A=B.时间B
------解决方案--------------------
drop table A,B
go
create table A(时间A datetime,奖金A int)
insert into A
select '2007-04-09 ',1500
union all select '2007-04-10 ',1000
union all select '2007-04-11 ',500
create table B(时间B datetime,奖金B int)
insert into B
select '2007-04-10 ',500
union all select '2007-04-12 ',1000
select isnull(时间A,时间B) as '时间 ',奖金A,奖金B
from A
full outer join B on A.时间A=B.时间B
/*
时间 奖金A 奖金B