select a.username
,表2=case when b.userid is not null then '有' else '无' end
,表3=case when c.userid is not null then '有' else '无' end
,表4=case when d.userid is not null then '有' else '无' end
,表5=case when e.userid is not null then '有' else '无' end
,表6=case when f.userid is not null then '有' else '无' end
from 表1 a
left join (select userid from 表2 where DateDiff(day,日期,@d)=0) b
on a.id=b.userid
left join (select userid from 表3 where DateDiff(day,日期,@d)=0) c
on a.id=c.userid
left join (select userid from 表4 where DateDiff(day,日期,@d)=0) d
on a.id=d.userid
left join (select userid from 表5 where DateDiff(day,日期,@d)=0) e
on a.id=e.userid
left join (select userid from 表6 where DateDiff(day,日期,@d)=0) f
on a.id=f.userid
小改一下:
select a.username
,表2=case when b.userid is not null then '有' else '无' end
,表3=case when c.userid is not null then '有' else '无' end
,表4=case when d.userid is not null then '有' else '无' end
,表5=case when e.userid is not null then '有' else '无' end
,表6=case when f.userid is not null then '有' else '无' end
from 表1 a
left join (select userid from 表2 where 日期=@d) b on a.id=b.userid
left join (select userid from 表3 where 日期=@d) c on a.id=c.userid
left join (select userid from 表4 where 日期=@d) d on a.id=d.userid
left join (select userid from 表5 where 日期=@d) e on a.id=e.userid
left join (select userid from 表6 where 日期=@d) f on a.id=f.userid
Select username as 用户,
IsNull((Select top 1 '有' from 表2 where userid = a.userid),'无') as 表2,
IsNull((Select top 1 '有' from 表3 where userid = a.userid),'无') as 表3,
IsNull((Select top 1 '有' from 表4 where userid = a.userid),'无') as 表4,
IsNull((Select top 1 '有' from 表5 where userid = a.userid),'无') as 表5,
IsNull((Select top 1 '有' from 表6 where userid = a.userid),'无') as 表6
from 表1 a
--或:
declare @d datedime
set @d=getdate() --要查询的日期
select username
,表2=case when exists(select 1 from 表2 where 日期=@d and userid=a.id) then '有' else '无' end
,表3=case when exists(select 1 from 表3 where 日期=@d and userid=a.id) then '有' else '无' end
,表4=case when exists(select 1 from 表4 where 日期=@d and userid=a.id) then '有' else '无' end
,表5=case when exists(select 1 from 表5 where 日期=@d and userid=a.id) then '有' else '无' end
,表5=case when exists(select 1 from 表6 where 日期=@d and userid=a.id) then '有' else '无' end
from 表1 a
select a.username
,表2=case when b.userid is null then '无' else '有' end
,表3=case when c.userid is null then '无' else '有' end
,表4=case when d.userid is null then '无' else '有' end
,表5=case when e.userid is null then '无' else '有' end
,表6=case when f.userid is null then '无' else '有' end
from 表1 a
left join (select userid from 表2 where 日期=@d) b on a.id=b.userid
left join (select userid from 表3 where 日期=@d) c on a.id=c.userid
left join (select userid from 表4 where 日期=@d) d on a.id=d.userid
left join (select userid from 表5 where 日期=@d) e on a.id=e.userid
left join (select userid from 表6 where 日期=@d) f on a.id=f.userid