select m.id,m.name,dDate as 借款日期,mMoney as 借款金额,dDate2 as 还款日期,mMoney2 as 还款金额
from M,(
select isnull(t1.id,t2.id) as id,t1.dDate,t1.mMoney,t2.dDate as dDate2,t2.mMoney as mMoney2
from t1 full join t2
on t1.id=t2.id and t1.tid=t2.tid
) as t
where m.id=t.id
from M,(
select isnull(t1.id,t2.id) as id,t1.dDate,t1.mMoney,t2.dDate as dDate2,t2.mMoney as mMoney2
from t1 full join t2
on t1.id=t2.id and t1.tid=t2.tid
) as t
where m.id=t.id
解决方案 »
- 求助SQL汇总动态生成新的问题
- 存储过程中,语句中表名是上一个select搜出来的,老出错!!!
- top10的问题~
- 提取多张表的语法 赶紧急呢 谢谢 在线等
- 批量修改时间语句~
- ??? 如下统计,该如何生成结果 ???
- 求个带条件的分页 查询 SQL语句
- 请问如何能嵌套使用select count(*) from tableName 所得到的值?
- 一次插入多条记录的触发器问题?
- 有请大力等高手:如何计算每日合计,每月合计,本年累计(直接用sql能不能实现,给个思路也行)
- 初学者的菜鸟问题,很菜,请各位多多帮忙呀!!!!感激不尽!关于用代码实现与数据库的连接
- SQLSERVER2000 XML问题
create table M
(
id int,
name varchar(10)
)insert into m values(1,'张三')
insert into m values(2,'李四')
insert into m values(3,'王五')
insert into m values(4,'赵六')create table t1
(
id int,
tid int,
ddate datetime,
mmoney int
)create table t2
(
id int,
tid int,
ddate datetime,
mmoney int
)insert into t1 values(1,1,'20040101',50)
insert into t1 values(1,2,'20040202',100)
insert into t1 values(2,1,'20031001',1000)
insert into t1 values(2,2,'20031225',500)
insert into t1 values(2,3,'20040401',200)
insert into t1 values(3,1,'20040501',100)insert into t2 values(1,1,'20040102',30)
insert into t2 values(1,2,'20040215',120)
insert into t2 values(2,1,'20031101',800)
insert into t2 values(2,2,'20031228',600)
insert into t2 values(2,3,'20040415',100)
insert into t2 values(2,4,'20040420',200)
--以上为建立测试环境
select m.id as ID,m.name as 姓名,ttt.date1 as 借款日期, ttt.date2 as 还款日期
from m left outer join
(select (case when tab1.id is null then tab2.id else tab1.id end) id,
tab1.ddate date1,tab2.ddate date2 from
(select id,tid,ddate,mmoney,
(select min(t2.ddate) from t2 where t2.ddate>t1.ddate) as ldate
from t1) tab1
full outer join
(select id,tid,ddate,mmoney,
(select max(t1.ddate) from t1 where t1.ddate<t2.ddate) as ldate
from t2) tab2
on tab1.ldate=tab2.ddate and tab2.ldate=tab1.ddate and tab1.id=tab2.id
) ttt on m.id=ttt.id
select m.id,m.name,T1.dDate as 借款日期,T1.mMoney as 借款金额,T2.dDate2 as 还款日期,T2.mMoney2 as 还款金额 from M,T1,T2 where M.id=T1.id and T1.id=T2.id and T1.tid=T2.tid;IS,
from m left outer join
(select (case when tab1.id is null then tab2.id else tab1.id end) id,
tab1.ddate date1,tab1.mmoney as money1,tab2.ddate date2, tab2.mmoney as money2 from
(select id,tid,ddate,mmoney,
(select min(t2.ddate) from t2 where t2.ddate>t1.ddate) as ldate
from t1) tab1
full outer join
(select id,tid,ddate,mmoney,
(select max(t1.ddate) from t1 where t1.ddate<t2.ddate) as ldate
from t2) tab2
on tab1.ldate=tab2.ddate and tab2.ldate=tab1.ddate and tab1.id=tab2.id
) ttt on m.id=ttt.id
比如 1,2,4,null,6,9dDate也不是必填的字段可能为空!还能不能够查出来???
4 赵六 NULL NULL NULL NULL