表一
NO1 DATE1 DATE2
001 2004-3-1
001 2004-3-2
001 2004-3-3
002 2004-3-1
002 2004-3-2
表二
NO1 DATE1
001 2004-3-1 12:00:00
001 2004-3-3 2:09:08
002 2004-3-1 3:00:07
如何通过查询求出如下结果?
NO1 DATE1 DATE2
001 2004-3-1 2004-3-1 12:00:00001 2004-3-2001 2004-3-3 2004-3-3 2:09:08002 2004-3-1 2004-3-1 3:00:07002 2004-3-2
应该是这样
NO1 DATE1 DATE2
001 2004-3-1
001 2004-3-2
001 2004-3-3
002 2004-3-1
002 2004-3-2
表二
NO1 DATE1
001 2004-3-1 12:00:00
001 2004-3-3 2:09:08
002 2004-3-1 3:00:07
如何通过查询求出如下结果?
NO1 DATE1 DATE2
001 2004-3-1 2004-3-1 12:00:00001 2004-3-2001 2004-3-3 2004-3-3 2:09:08002 2004-3-1 2004-3-1 3:00:07002 2004-3-2
应该是这样
convert(varchar(10),date1,112)=convert(varchar(10),a.date1,112)) from 表一 a
declare @tb table (NO1 char(10),DATE1 datetime)
insert @ta select
'001', '2004-3-1' union all select
'001' , '2004-3-2' union all select
'001' , '2004-3-3' union all select
'002' , '2004-3-1' union all select
'002' , '2004-3-2'insert @tb select
'001' , '2004-3-1 12:00:00' union all select
'001' , '2004-3-3 2:09:08' union all select
'002' , '2004-3-1 3:00:07'select a.NO1,convert(varchar(10),a.DATE1,120) as DATE1,b.DATE1 as DATE2
from @ta a left join @tb b
on a.NO1=b.NO1 and datediff(day,a.DATE1,b.DATE1)=0--测试结果
NO1 DATE1 DATE2
---------- ---------- ------------------------------------------------------
001 2004-03-01 2004-03-01 12:00:00.000
001 2004-03-02 NULL
001 2004-03-03 2004-03-03 02:09:08.000
002 2004-03-01 2004-03-01 03:00:07.000
002 2004-03-02 NULL(5 row(s) affected)