表T: 表S1: 表S2:
-------------------- ----------------- -----------------
ID STSRTTIME ENDTIME ID TIME MONEY ID TIME MONEY
-------------------- ----------------- -----------------
01 01 31 01 01 101 01 06 201
02 01 31 01 22 101 03 22 203
03 01 31 01 30 101 03 20 203
04 01 31 02 15 102 04 10 204
05 01 31 02 16 101 -----------------
-------------------- ----------------- T中ID是key,S1,S2中ID和TIME是key, 现在我想检索出以下的效果: ---------------
01 101
01 101
01 101
01 201
02 102
02 102
03 203
03 203
04 204
05 null null
--------------- 建表SQL文,用的是mysql:
create table t(id varchar(10),starttime varchar(10),endtime varchar(10))
insert into t select '01','01','31'
insert into t select '02','01','31'
insert into t select '03','01','31'
insert into t select '04','01','31'
insert into t select '05','01','31'create table s1(Id varchar(10),[time] varchar(10),[money] varchar(10))
insert into s1 select '01','01','101'
insert into s1 select '01','22','101'
insert into s1 select '01','30','101'
insert into s1 select '02','15','102'
insert into s1 select '02','16','101'create table s2(Id varchar(10),[time] varchar(10),[money] varchar(10))
insert into s2 select '01','06','201'
insert into s2 select '03','22','203'
insert into s2 select '03','20','203'
insert into s2 select '04','10','204'
这里有两个sql文
select
T.ID,
(case when S2.MONEY IS NULL then S1.MONEY end) as MONEY1,
(case when S1.MONEY IS NULL then S2.MONEY end) as MONEY2
from T LEFT JOIN S1 ON T.ID = S1.ID LEFT JOIN S2 ON T.ID = S2.ID id MONEY1 MONEY2
---------- ---------- ----------
01
01
01
02 102
02 102
03 203
03 203
04 204
05select
a.id,
IFNULL(b.money,'') as s1money,
IFNULL(c.money,'') as s2money
from t a
left join s1 b
on a.id=b.id
left join s2 c
on a.id=c.id id s1money s2money
---------- ---------- ----------
01 101 201
01 101 201
01 101 201
02 102
02 102
03 203
03 203
04 204
05用以上两个sql文能实现部分的查询效果:
不过还是01显示的有问题...
求助高手啊....
-------------------- ----------------- -----------------
ID STSRTTIME ENDTIME ID TIME MONEY ID TIME MONEY
-------------------- ----------------- -----------------
01 01 31 01 01 101 01 06 201
02 01 31 01 22 101 03 22 203
03 01 31 01 30 101 03 20 203
04 01 31 02 15 102 04 10 204
05 01 31 02 16 101 -----------------
-------------------- ----------------- T中ID是key,S1,S2中ID和TIME是key, 现在我想检索出以下的效果: ---------------
01 101
01 101
01 101
01 201
02 102
02 102
03 203
03 203
04 204
05 null null
--------------- 建表SQL文,用的是mysql:
create table t(id varchar(10),starttime varchar(10),endtime varchar(10))
insert into t select '01','01','31'
insert into t select '02','01','31'
insert into t select '03','01','31'
insert into t select '04','01','31'
insert into t select '05','01','31'create table s1(Id varchar(10),[time] varchar(10),[money] varchar(10))
insert into s1 select '01','01','101'
insert into s1 select '01','22','101'
insert into s1 select '01','30','101'
insert into s1 select '02','15','102'
insert into s1 select '02','16','101'create table s2(Id varchar(10),[time] varchar(10),[money] varchar(10))
insert into s2 select '01','06','201'
insert into s2 select '03','22','203'
insert into s2 select '03','20','203'
insert into s2 select '04','10','204'
这里有两个sql文
select
T.ID,
(case when S2.MONEY IS NULL then S1.MONEY end) as MONEY1,
(case when S1.MONEY IS NULL then S2.MONEY end) as MONEY2
from T LEFT JOIN S1 ON T.ID = S1.ID LEFT JOIN S2 ON T.ID = S2.ID id MONEY1 MONEY2
---------- ---------- ----------
01
01
01
02 102
02 102
03 203
03 203
04 204
05select
a.id,
IFNULL(b.money,'') as s1money,
IFNULL(c.money,'') as s2money
from t a
left join s1 b
on a.id=b.id
left join s2 c
on a.id=c.id id s1money s2money
---------- ---------- ----------
01 101 201
01 101 201
01 101 201
02 102
02 102
03 203
03 203
04 204
05用以上两个sql文能实现部分的查询效果:
不过还是01显示的有问题...
求助高手啊....
(
select a.id, IFNULL(b.money,'') as s1money, '' as s2money
from t a
left join s1 b
on a.id=b.id uinon allselect a.id, '' as s1money, money as s2money
from t a
left join s2 b
on a.id=b.id
) C
order by C.id ,C.s2money
select t.id,a.[money],a.money2 from t left join
(select *,'' as money2 from s1 union all (select id,time,'' as money1,[money] from s2) ) a on t.id=a.id order by t.id ---嘿嘿,厉害吧!!!
select * from
(select a.id, IsNULL(b.money,'') as 's1money', '' as 's2money'
from t a
left join s1 b
on a.id = b.id
union all
select a.id, '' as 's1money', IsNULL(b.money,'') as 's1money'
from t a
left join s2 b
on a.id=b.id ) a
where s1money <> s2money
union all
select id,'','' from t where id not in
(select t.id from t inner join s1 on t.id = s1.id union select t.id from t inner join s2 on t.id = s2.id )
执行结果
id s1money s2money
---------- ---------- ----------
01 101
01 101
01 101
02 102
02 101
01 201
03 203
03 203
04 204
05 (10 row(s) affected)
借用楼主数据
select * from
(select a.id, IsNULL(b.money,'') as 's1money', '' as 's2money'
from t a
left join s1 b
on a.id = b.id
union all
select a.id, '' as 's1money', IsNULL(b.money,'') as 's1money'
from t a
left join s2 b
on a.id=b.id ) a
where s1money <> s2money
union all
select id,'','' from t where id not in
(select t.id from t inner join s1 on t.id = s1.id union select t.id from t inner join s2 on t.id = s2.id )
order by a.id
执行结果
id s1money s2money
---------- ---------- ----------
01 101
01 101
01 101
01 201
02 102
02 101
03 203
03 203
04 204
05 (10 row(s) affected)
from
(
select * from
(select a.id, IFNULL(b.money,'') as s1money, '' as s2money
from t a
left join s1 b
on a.id = b.id
union all
select a.id, '' as s1money, IFNULL(b.money,'') as s1money
from t a
left join s2 b
on a.id=b.id ) a
where s1money <> s2money
union all
select id,'','' from t where id not in
(select t.id from t inner join s1 on t.id = s1.id union select t.id from t inner join s2 on t.id = s2.id )
) as tt
order by tt.id不过上边的sql文,嵌套了三层selelct,数据量非常大的时候是否会很影响效率?
貌似不嵌套这么多层也不行...郁闷
4楼的那个sql文,想问一下字段加上[]是什么意思?在mysql下运行不了?
-----------------------------------------------------------
money是个关键字,作为字段名的时候最好加上 [ ]或者“ ”