declare @d datedime
set @d=getdate() --要查询的日期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
set @d=getdate() --要查询的日期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
解决方案 »
- 用C语言写了一个多线程序下载器,下载速度快的无与伦比,大小只有86K,可下载任何文件!(免费+开源)
- 存储过程的问题
- 请教两个数据库查询的问题,分不够可再开贴,顶者有分!~~
- 终于发现了为什么oracle,mysql 的某些性能比sqlserver好的原因了。天大的秘密,也不过仅仅一层窗户纸而已。
- 求SQL 语句
- 关于三范式的小困扰地方。谢谢
- 请问:用VB,知道机器名、用户名、密码,如何得知数据库名称列表。
- 行列转换时怎么总是统计不完全呢? typein不完全
- 请一条SQL语句,关于数据表的过滤更新
- SQL server 建表时候 如何建立两列之间的约束关系
- 求Update语句一条!
- 这样的存储过程有什么错???
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
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
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否則會出現?號
,表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