SQL查询问题........................ [英] SQL QUERY Question........................
问题描述
我有两个桌子,
第一个是"tbl_login",其中包含两列"userid"和"name"(均为varchar字段)
第二个是"tbl_leave",其中包含三列"userid","fromdate","duration"(均为varchar字段)
tbl_login.userid = tbl_leave.userid
例如
tbl_login:
用户名名称
001 abcd
002 defc
tbl_leave
从日期持续时间起的用户ID
001 2011/03/08 2天
002 2011/03/28 5天
我希望查询像这样显示
用户ID名称状态
001 abcd不存在
002 defc存在
首先在tbl_login中显示所有名称,然后在tbl_leave的fromdate列中检查具有今天日期的任何用户ID
如果存在则不存在,否则将显示
请帮助
I have two tables,
first one is "tbl_login" which contains two columns "userid'' and "name" (both are varchar fields)
second one is "tbl_leave" which conatins three columns "userid","fromdate","duration" (all are varchar fields)
tbl_login.userid=tbl_leave.userid
for example
tbl_login :
userid name
001 abcd
002 defc
tbl_leave
userid fromdate duration
001 2011/03/08 2 days
002 2011/03/28 5 days
i want a query to display like this
userid name status
001 abcd absent
002 defc present
display all names in tbl_login first,then check any userid having today date in fromdate column of tbl_leave
if existing then Absent,else Present to be displayed
Please help
推荐答案
尝试一下-
Try this--
select userid, name, (Case when Exist(Select 1 from tbl_leave where tbl_leave.UserID=tbl_login.userid and Convert(varchar(11),tbl_leave.fromdate,106)=convert(varchar(11),getdate(),106)) then 'absent' else 'present' end) as status from tbl_login
--Pankaj
--Pankaj
这篇关于SQL查询问题........................的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!