用于从表中显示数据的SQL查询 [英] SQL QUERY FOR DISPLAYING DATA FROM TABLE

问题描述

我有两个桌子，
第一个是"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

userid 		fromdate 		duration
-----------------------------------------------------------------------------------
001 		2011/03/08 	2 days
002		2011/03/28 	5 days

我希望查询像这样显示

此处ID为001的用户名将于2011年8月3日休假2天
因此，当我在2011年9月3日登录时，它会像下面这样

userid		name           status
------------------------------------------------------------------------------------- 	
001		 abcd 	  absent
002 		 defc 	  present

因为"abcd"处于休假状态，而defc处于休假状态
当今天= 2011年3月29日
它会显示为

userid		name           status
-------------------------------------------------------------------------------------	
001		 abcd 	  present
002 		 defc 	 absent

因为defc从2011年3月28日起休假5天

所有字段都是VARCHAR字段
我不知道它是否可能，如果可能
请帮助我

解决方案

可以使用存储过程和if语句来完成.

这应该会有所帮助: http://stackoverflow.com/questions/87821/sql-if-如果DateDiff(day，fromdate，sysdate)> 0，然后返回不存在"，否则返回存在"

这行吗?

 选择 tbl_login.userid，
       tbl_login.name，
       案例
         何时 getdate()之间 tbl_leave.fromdate 和 dateadd(day，tbl_leave.duration-1，tbl_leave.fromdate)然后 ' 
         其他 ' 存在'
       结尾  as  ststus
来自 tbl_login
左
加入 tbl_leave 上 tbl_leave.userid = tbl_login.userid 

注意:
-使用左联接(tbl_leave可能没有所有用户的数据)
-tbl_leave.duration必须是int，否则必须将其转换为int

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

here name of user with id 001 have on leave from 08/03/2011 for 2days
so when i login on 09/03/2011,it shos like this following

userid		name           status
------------------------------------------------------------------------------------- 	
001		 abcd 	  absent
002 		 defc 	  present

because "abcd" is on leave and defc is on not leave
and when today=29/03/2011
its will show like

userid		name           status
-------------------------------------------------------------------------------------	
001		 abcd 	  present
002 		 defc 	 absent

because defc is on leave from 28/03/2011 for 5 days

ALL THE FIELDS ARE VARCHAR FIELDS
i dont know if its possible or not,if it is possible
please help me

解决方案

This could be done using a stored procedure and using an if statement.

This should help: http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause

you can use if DateDiff(day, fromdate, sysdate) > 0 then return "absent" else return "present"

Would this work?

select tbl_login.userid,
       tbl_login.name,
       case
         when getdate() between tbl_leave.fromdate and dateadd(day,tbl_leave.duration-1,tbl_leave.fromdate) then 'absent'
         else 'present'
       end as ststus
from   tbl_login
left
join   tbl_leave on tbl_leave.userid=tbl_login.userid

NOTE:
- use left join ( tbl_leave may not have data for all users)
- tbl_leave.duration is (must be) a int, if not you must convert it to an int

这篇关于用于从表中显示数据的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！