Select A.*,
完成情况=case when b.subject is not null then '完成' else 未完成 end,
b.time
From 表1 A
left join
(Select ID,Max(Time) Time
From 表2
where convert(char(10),TIME,20) = convert(char(10),getdate(),20)
Group by ID) B
on A.id = b.id
完成情况=case when b.subject is not null then '完成' else 未完成 end,
b.time
From 表1 A
left join
(Select ID,Max(Time) Time
From 表2
where convert(char(10),TIME,20) = convert(char(10),getdate(),20)
Group by ID) B
on A.id = b.id
Select A.*,
完成情况=case when b.subject is not null then '完成' else 未完成 end,
b.time
From 表1 A
left join
(Select ID,Max(Time) Time
From 表2
where convert(char(10),TIME,20) = convert(char(10),getdate(),20)
Group by ID) B
on A.id = b.id
Select A.*,
完成情况=case when b.subject is not null then '完成' else 未完成 end,
b.time
From 表1 A
left join
(Select Name,Max(Time) Time
From 表2
where convert(char(10),TIME,20) = convert(char(10),getdate(),20)
Group by Name) B
on A.Name= b.Name ---->Here Changed
select NAME form (select NAME,max(TIME) from 表2 group by NAME) B
union
select ID ,NAME, DEPT_ID , 未完成 from 表2 where NAME not in
select NAME form (select NAME,max(TIME) from 表2 group by NAME) B
(select NAME form (select NAME,max(TIME) from 表2 group by NAME) B )
union
select ID ,NAME, DEPT_ID , 未完成 from 表2 where NAME not in
(select NAME form (select NAME,max(TIME) from 表2 group by NAME) B )
select NAME form (select NAME,max(TIME) from 表2 group by NAME) B
union
select ID ,NAME, DEPT_ID , 未完成 from 表2 where NAME not in
select NAME form (select NAME,max(TIME) from 表2 group by NAME) B
Select B.TheDay,A.*,
完成情况=case when b.subject is not null then '完成' else 未完成 end,
B.Time
From 表1 A
left join
(Select TheDay = convert(char(10),TIME,20),Name,Max(Time) Time
From 表2
Group by convert(char(10),TIME,20),Name) B
on A.Name= B.Name
select name,(case when datediff(dd,max(表2.time),getdate)=0 then '完成'
else '未完成' end) as "完成情况", .*,(case when datediff(dd,max(表2.time),getdate)=0
then max(表2.time) else '' end) as '工作记录的时间' from 表2 group by name) tmp
where 表1.name=tmp.name
from 员工工作记录表 where DATEDIFF ( datepart , TIME
, getdate()=0 ) group by name ) b on a.name=b.name
As
Select distinct A.*,
完成情况=case when b.subject is not null then '完成' else '未完成' end,
时间=(select max(C.time) from 表2 C where B.ID=C.ID)
From 表1 A,
表2 B
where A.ID*=B.ID
asselect a.*,
(case when convert ( varchar(10),b.time,120)='2003-06-04' then '完成' else '未完成' end ) as '完成情况',
b.time as 时间
from 员工信息表 a left join ( select * from 员工工作记录表 a where time
in (select max(time) from 员工工作记录表 where name=a.name ) ) b
on a.name=b.name
as
select a.*,
(case when convert ( varchar(10),b.time,120)=convert(varchar(10),getdate(),120)
then '完成' else '未完成' end ) as '完成情况',
b.time as 时间
from 员工信息表 a left join
( select * from 员工工作记录表 a where time
in (select max(time) from 员工工作记录表 where name=a.name ) ) b
on a.name=b.name
as select a.*,
(case when convert ( varchar(10),b.time,120)=convert(varchar(10),getdate(),120)
then '完成' else '未完成' end ) as '完成情况',
b.time as 时间
from 员工信息表 a left join
( select * from 员工工作记录表 a where time
in (select max(time) from 员工工作记录表 where name=a.name ) ) b
on a.name=b.name
--用你的数据测试 getdate()换为 '2003-06-04' 加一条记录
04 a2 xxxxxxx 2003-6-4 18:32:36--结果
a1 010000 未完成 2003-06-03 17:41:08.000
a2 010000 完成 2003-06-04 12:55:35.000
a3 010401 未完成 NULL
a4 010402 完成 2003-6-4 18:32:36
a5 010403 未完成 NULL
a6 010601 未完成 NULL
a7 010602 未完成 NULL