select d.userno,username,d.fdate,fdesc,funit from table1 a right join ( select distinct b.userno,c.fdate from table1 b, table2 c ) d on a.userno=d.userno and a.fdate=d.fdate
select d.userno,username,d.fdate,fdesc,funit from table1 a right join ( select distinct b.userno,c.fdate from table1 b, table2 c ) d on a.userno=d.userno and a.fdate=d.fdate其中and a.fdate=d.fdate不对,如不加此条件,又有重复fdate
declare int @a select @a=001 while @a< (select max(userno) from table1)+1 insert into table1(userno,username,fdate,fdesc,funit) select a.userno,a.username,b.fdate,b.fdesc,b.funit from table1 a inner join(select a,fdate,fdesc,funit from table2 where not fdate in( select fdate from table1 where userno =a))b on a.userno=b.a select @a=@a+1 end 不知USERNO是否为整型如果不是的话可用IDENTIEY根据USERNO生成一个整型的列然后执行。
上面的方法较麻烦,可以试试这个方法 select fdate,fdesc,funit from table2 where not fdate in (select fdate from table1)into table3 select a.userno,b.fdate,b.fdesc,b.funit into table4 from table1 a ,table3 b select a.userno,a.username,b.fdate ,b.fdesc,b.funit from table1 a inner join table4 b on a.userno=b.userno
select isnull(a.userno,b.userno) userno,isnull(a.username,b.username) username, isnull(a.fdate,b.fdate) fdate,isnull(a.fdesc,b.fdesc) fdesc,isnull(a.funit,b.funit) funit from table1 a full join (select * from (select distinct userno,username from table1) t,table2) b on a.userno=b.userno and a.fdate=b.fdate
Select @min = min(fdate), @max = Max(fdate) From #t while @min <= @max begin Select @fdate = fdate from #t where fdate = @min if @fdate not null begin Insert #t (userno, username, fdate, fdesc, funit) Select userno, username, fdate, fdesc, funit from table1 where end set @min = @min + 1 end
--没有测试 select a.userno,a.username,b.fdate,a.fdesc,a.funit from table1 a,table2 b where b.fdate=a.fdate(+)
Declare @table1 table (userno varchar(10),username varchar(2),fdate datetime,fdesc varchar(2),funit varchar(2)) Insert into @table1 Select '001','a','2004-08-01','m','n' Union ALL Select '001','a','2004-08-02','m','n' Union ALL Select '001','a','2004-08-03','m','n' Union ALL Select '001','a','2004-08-04','m','n' Union ALL Select '001','a','2004-08-06','m','n' Union ALL Select '002','b','2004-08-01','m','n' Union ALL Select '002','b','2004-08-02','m','n' Union ALL Select '002','b','2004-08-04','m','n'Declare @table2 table (fdate datetime,fdesc varchar(10),funit varchar(10)) Insert into @table2 Select '2004-08-01',null,null Union All Select '2004-08-02',null,null Union All Select '2004-08-03',null,null Union All Select '2004-08-04',null,null Union All Select '2004-08-05',null,null Union All Select '2004-08-06',null,nullSelect d.userno,d.fdate,c.fdesc,c.funit From @table1 c right join (Select distinct a.userno,b.fdate From @table1 a,@table2 b) d on c.userno=d.userno and c.fdate=d.fdate
userno fdate fdesc funit ---------- ------------------------------------------------------ ----- ----- 001 2004-08-01 00:00:00.000 m n 001 2004-08-02 00:00:00.000 m n 001 2004-08-03 00:00:00.000 m n 001 2004-08-04 00:00:00.000 m n 001 2004-08-05 00:00:00.000 NULL NULL 001 2004-08-06 00:00:00.000 m n 002 2004-08-01 00:00:00.000 m n 002 2004-08-02 00:00:00.000 m n 002 2004-08-03 00:00:00.000 NULL NULL 002 2004-08-04 00:00:00.000 m n 002 2004-08-05 00:00:00.000 NULL NULL 002 2004-08-06 00:00:00.000 NULL NULL
select d.userno,username,d.fdate,fdesc,funit
from table1 a right join
( select distinct b.userno,c.fdate
from table1 b, table2 c
) d
on a.userno=d.userno and a.fdate=d.fdate
from table1 a right join
( select distinct b.userno,c.fdate
from table1 b, table2 c
) d
on a.userno=d.userno and a.fdate=d.fdate其中and a.fdate=d.fdate不对,如不加此条件,又有重复fdate
select @a=001
while @a< (select max(userno) from table1)+1
insert into table1(userno,username,fdate,fdesc,funit) select a.userno,a.username,b.fdate,b.fdesc,b.funit from table1 a inner join(select a,fdate,fdesc,funit from table2 where not fdate in( select fdate from table1 where userno =a))b on a.userno=b.a
select @a=@a+1
end
不知USERNO是否为整型如果不是的话可用IDENTIEY根据USERNO生成一个整型的列然后执行。
select fdate,fdesc,funit from table2 where not fdate in (select fdate from table1)into table3
select a.userno,b.fdate,b.fdesc,b.funit into table4 from table1 a ,table3 b
select a.userno,a.username,b.fdate ,b.fdesc,b.funit from table1 a inner join table4 b on a.userno=b.userno
isnull(a.fdate,b.fdate) fdate,isnull(a.fdesc,b.fdesc) fdesc,isnull(a.funit,b.funit) funit
from table1 a full join (select * from (select distinct userno,username from table1) t,table2) b on a.userno=b.userno and a.fdate=b.fdate
CREATE table #t (([id] [int] IDENTITY, userno varchar(20), username varchar(8),
fdate datetime, fdesc varchar(20) = null, funit varchar(20) = null)
Insert #t (userno, username, fdate, fdesc, funit)
Select userno, username, fdate, fdesc, funit
From table1
declare @min datetime, @Max datetime, @fdate datetime=null, @id int,
Select @min = min(fdate), @max = Max(fdate) From #t
while @min <= @max
begin
Select @fdate = fdate from #t where fdate = @min
if @fdate not null
begin
Insert #t (userno, username, fdate, fdesc, funit)
Select userno, username, fdate, fdesc, funit
from table1
where
end
set @min = @min + 1
end
select a.userno,a.username,b.fdate,a.fdesc,a.funit from table1 a,table2 b
where b.fdate=a.fdate(+)
Insert into @table1 Select '001','a','2004-08-01','m','n'
Union ALL Select '001','a','2004-08-02','m','n'
Union ALL Select '001','a','2004-08-03','m','n'
Union ALL Select '001','a','2004-08-04','m','n'
Union ALL Select '001','a','2004-08-06','m','n'
Union ALL Select '002','b','2004-08-01','m','n'
Union ALL Select '002','b','2004-08-02','m','n'
Union ALL Select '002','b','2004-08-04','m','n'Declare @table2 table (fdate datetime,fdesc varchar(10),funit varchar(10))
Insert into @table2 Select '2004-08-01',null,null
Union All Select '2004-08-02',null,null
Union All Select '2004-08-03',null,null
Union All Select '2004-08-04',null,null
Union All Select '2004-08-05',null,null
Union All Select '2004-08-06',null,nullSelect d.userno,d.fdate,c.fdesc,c.funit
From @table1 c right join (Select distinct a.userno,b.fdate From @table1 a,@table2 b) d
on c.userno=d.userno and c.fdate=d.fdate
userno fdate fdesc funit
---------- ------------------------------------------------------ ----- -----
001 2004-08-01 00:00:00.000 m n
001 2004-08-02 00:00:00.000 m n
001 2004-08-03 00:00:00.000 m n
001 2004-08-04 00:00:00.000 m n
001 2004-08-05 00:00:00.000 NULL NULL
001 2004-08-06 00:00:00.000 m n
002 2004-08-01 00:00:00.000 m n
002 2004-08-02 00:00:00.000 m n
002 2004-08-03 00:00:00.000 NULL NULL
002 2004-08-04 00:00:00.000 m n
002 2004-08-05 00:00:00.000 NULL NULL
002 2004-08-06 00:00:00.000 NULL NULL