百分求解

Question

select id=identity(int),* into #t from 表1
select b.*,a.a.col1,a.col2,a.col3
from #t a right join 表2 b on a.col1=b.id
drop table #t

Answer 1

select b.*,a.字段2,a.字段3
from 表2 b left join 表1 a 
     on a.字段1=b.字段2 and b.id=(select top 1 id from 表2 where 字段2=a.字段2)

Answer 2

改一点select b.*,a.字段2,a.字段3
from 表2 b left join 表1 a 
     on a.字段1=b.字段2 and b.id=(select top 1 id from 表2 where 字段2=a.字段1)

Answer 3

        pbmb
        cardid  kqdate           ystime                  yxtime 
       002 2004-12-29 2004-12-29 06:30:00 2004-12-29 17:30:00 0
002 2004-12-30 2004-12-30 06:30:00 2004-12-30 17:30:00 0
002 2004-12-31 2004-12-31 06:30:00 2004-12-31 17:30:00 0

       kqtable        cardid   rkqdate                 ystime                   stime                   yxtime                  xtime 
        002 2004-12-29 06:23:24 2004-12-29 06:30:00 2004-12-29 06:23:24 2004-12-29 17:30:00 2004-12-29 17:23:31 1
002 2004-12-29 06:23:24 2004-12-29 06:30:00 2004-12-29 06:23:24 2004-12-29 17:30:00 2004-12-29 17:23:31 1
查询结果为：2004-12-29          002           2004-12-29 06:23:24     2004-12-29 17:23:31
2004-12-29          002           2004-12-29 06:23:24     2004-12-29 17:23:31
2004-12-30          002            未考勤                    未考勤
2004-12-31          002            未考勤                    未考勤

Answer 4

select a.kqdate,a.cardid
,isnull(convert(varchar,b.rkqdate,120),'未考勤')
,isnull(convert(varchar,b.xtime,120),'未考勤')
from pbmb a
left join kqtable b on a.cardid=b.cardid 
and datediff(day,a.kqdate,b.rkqdate)=0