有a1表和b1表 里面的字段是完全一致的 两表中唯一值的字段为PID 现在想对其中时间这一字段进行比对.将A1中的空的时间从B2中将时间追加到A1中,如果 A1有时间的的还保留A1的时间.
a1 b2 想要的结果 新表a1
2007-06-11 2007-06-11
2007-10-22 2007-10-22
2007-05-15 2007-05-15
2007-08-13 2007-08-13
2007-10-12 2007-04-12 2007-10-12
请问各位前辈,有没有知道该如何写语句的 我着急用 万分感谢!!
a1 b2 想要的结果 新表a1
2007-06-11 2007-06-11
2007-10-22 2007-10-22
2007-05-15 2007-05-15
2007-08-13 2007-08-13
2007-10-12 2007-04-12 2007-10-12
请问各位前辈,有没有知道该如何写语句的 我着急用 万分感谢!!
[a1]=isnull(a1.a1,b1.b2)
from
a1
full join
b1 on a1.PID=b1.PID
--只比较存在的用join/inner join /,
select
[a1]=isnull(a1.a1,b1.b2)
from
a1
join
b1 on a1.PID=b1.PID
--更新
update a1
set a1=b1.b2
from b1
where a1.PID=b1.PID and a1.a1 is null
当 a1,b2列某一列为null时,取不为null的列
当两列都不为null时,取大的那个吧
用a1,b1表有 pid对应时才取,如果是这个意思的话: select pid,max(a1) a1 from
(select pid,a1 from a1
union all
select pid,b2 from b1
) x
group by pid
having count(isnull(a1,'1900-1-1'))>1
declare @t table(id int,a datetime)
insert @t select 1,'1900-01-01'
insert @t select 2,null
insert @t select 3,'2000-01-10'
select * from @t
declare @tt table (id int,b datetime)
insert @tt select 1,'1900-01-01'
insert @tt select 2,'4001-01-13'
insert @tt select 3,'2000-01-10'
update @t
set c.a=d.b
from @t c,@tt d
where c.id=d.id and c.a is null
select * from @t
/*
id a
----------- -----------------------
1 1900-01-01 00:00:00.000
2 NULL
3 2000-01-10 00:00:00.000id a
----------- -----------------------
1 1900-01-01 00:00:00.000
2 4001-01-13 00:00:00.000
3 2000-01-10 00:00:00.000
*/
IF “空” 是NULL
*/--select
select 新表a1=isnull(a.a1,b.b2) from a1表 a join b1表 b on a.PID=b.PID
--update a1
update a1表 set a1=b.b2 from a1表 a join b1表 b on a.PID=b.PID where a.a1 is null
/*
ELSE
*/--select
select 新表a1=case a.a1 when '' then b.b2 else a.a1 end from a1表 a join b1表 b on a.PID=b.PID
--update a1
update a1表 set a1=b.b2 from a1表 a join b1表 b on a.PID=b.PID where a.a1=''
from a1 a
inner join b1 b
on a.pid=b.pid