有a1表和b1表 里面的字段为
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请问各位前辈,有没有知道该如何写语句的 我着急用 万分感谢!!
set col = b.col
from a1,b1
where a1.id = b1.id and a1.col is null
请问您的 COL 是什么意思
select 新表a1=isnull(a.a1,b.b2) from a1表 a join b1表 b on a.PID=b.PID--否则:
select 新表a1=case a.a1 when '' then b.b2 else a.a1 end from a1表 a join b1表 b on a.PID=b.PID
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时,取大的那个吧
,如果是这个意思的话:select pid,max(a1) a1 from
(select pid,a1 from a1
union all
select pid,b2 from b1
) x
group by pid
(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'
select isnull(c.a,d.b) from @t c left join @tt d
on c.id=d.id
/*
id a
----------- -----------------------
1 1900-01-01 00:00:00.000
2 NULL
3 2000-01-10 00:00:00.000
-----------------------
1900-01-01 00:00:00.000
4001-01-13 00:00:00.000
2000-01-10 00:00:00.000
*/