三表，一对多，并两表判定是否值有值？求SQL
以c表，c_nbr为关连查询：
币别、单价、最后日期，先取B表对应的记录，以交易日期最后的一条为准，如果没有记录，取A表中对应记录。不知怎样写SQL好点的，谢谢
[解决办法]
select A.*,'B表数据' as col from #tmp_asset3 A inner join #tmp_asset2 B on A.c_nbr=B.b_nbrwhere A.C_nbr=(select max(b_nbr) from #tmp_asset2)union select A.*,'A表数据' as col from #tmp_asset3 A inner join #tmp_asset1 B on A.c_nbr=B.a_nbrwhere A.C_nbr=(select max(a_nbr) from #tmp_asset1)--再取结果集的第一列,忘了咋写取中间结果集的数据了
[解决办法]
确保只有一条数据的话，union两个结果集，取第一列，这样写应该可以实现，
[解决办法]
select top 1 result.* from (select A.*,'B表数据' as col from #tmp_asset3 A inner join #tmp_asset2 B on A.c_nbr=B.b_nbrwhere A.C_nbr=(select max(b_nbr) from #tmp_asset2)union select A.*,'A表数据' as col from #tmp_asset3 A inner join #tmp_asset1 B on A.c_nbr=B.a_nbrwhere A.C_nbr=(select max(a_nbr) from #tmp_asset1)) result
[解决办法]
这个要求能用last（）函数不能？貌似可以啊
[解决办法]
b表应该可以（我认为）
[解决办法]
max（）函数对datetime类型可不可以用啊
[解决办法]
if (object_id('c')is not null) drop table ccreate table c(c_nbr varchar(50),c_cur varchar(50),c_untprc varchar(20),c_dt datetime)insert into cselect 'aa','rmb','1.3','2011-5-6'union allselect 'bb','usd','1.4','2012-10-1'union allselect 'cc','cc','usd','2012-10-1'union allselect 'dd','rmb','22','2012-6-9'/*（所影响的行数为 4 行）*/if(object_id('b')is not null) drop table bcreate table b(b_nbr varchar(20),b_cur varchar(20),b_untprc decimal(18,1),b_dt datetime)insert into bselect 'aa','rmb',1.3,'2011-5-6'union allselect 'bb','usd',1.5,'2012-5-9'union allselect 'bb','usd',1.4,'2012-10-1'union allselect 'dd','rmb',21,'2011-12-12'union allselect 'ee','rmb',24,'2012-4-9'union allselect 'dd','rmb',22,'2012-6-9'if(object_id('a') is not null) drop table acreate table a(a_nbr varchar(20),a_cur varchar(20) null,a_untprc decimal(18,1) null,a_dt datetime)insert into aselect 'aa','rmb',1.2,'2012-10-1'union allselect 'bb','',0,'2012-10-1'union allselect 'cc','usd',0.5,'2012-10-1'union allselect 'dd','',0,'2012-10-1'union allselect 'ee','',0,'2012-10-1'union allselect 'ff','',0,'2012-10-1'alter table aadd id int identity(1,1)alter table badd id int identity(1,1)alter table cadd id int identity(1,1)select * from a/*aa rmb 1.2 2012-10-01 00:00:00.000 1bb .0 2012-10-01 00:00:00.000 2cc usd .5 2012-10-01 00:00:00.000 3dd .0 2012-10-01 00:00:00.000 4ee .0 2012-10-01 00:00:00.000 5ff .0 2012-10-01 00:00:00.000 6*/select * from b/*aa rmb 1.3 2011-05-06 00:00:00.000 1bb usd 1.5 2012-05-09 00:00:00.000 2bb usd 1.4 2012-10-01 00:00:00.000 3dd rmb 21.0 2011-12-12 00:00:00.000 4ee rmb 24.0 2012-04-09 00:00:00.000 5dd rmb 22.0 2012-06-09 00:00:00.000 6*/select * from c/*aa rmb 1.3 2011-05-06 00:00:00.000 1bb usd 1.4 2012-10-01 00:00:00.000 2cc cc usd 2012-10-01 00:00:00.000 3dd rmb 22 2012-06-09 00:00:00.000 4*/select d.c_cur, case when b.b_untprc is null then a.a_untprc else b.b_untprc end as untprc, case when b.b_dt is null then a.a_dt else b.b_dt end as dt from (select c_nbr,c_cur,max(b.id) as id from c left join b on c.c_nbr=b.b_nbrgroup by c_cur,c_nbr)as d left join b on d.c_nbr = b.b_nbr and d.id = b.id inner join a on a.a_nbr = d.c_nbr /*rmb 1.3 2011-05-06 00:00:00.000usd 1.4 2012-10-01 00:00:00.000cc .5 2012-10-01 00:00:00.000rmb 22.0 2012-06-09 00:00:00.000*/
[解决办法]
--第二种方法select d.c_cur, case when b.b_untprc is null then a.a_untprc else b.b_untprc end as untprc, case when b.b_dt is null then a.a_dt else b.b_dt end as dt from (select c_nbr,c_cur,max(b.b_dt) as dt from c left join b on c.c_nbr=b.b_nbrgroup by c_cur,c_nbr)as d left join b on d.c_nbr = b.b_nbr and d.dt = b.b_dt inner join a on a.a_nbr = d.c_nbr /*rmb 1.3 2011-05-06 00:00:00.000usd 1.4 2012-10-01 00:00:00.000cc .5 2012-10-01 00:00:00.000rmb 22.0 2012-06-09 00:00:00.000*/