Querry的问题-连接三个表 [英] Problem in querry -joining three tables

问题描述

您好，我正在使用Querry查询员工每天工作多少小时.

员工表-employeeid，employeename

时间表-员工编号，总时数，开始日期

请假-请假雇员，请假类型

Hi, I am using querry for checking employees working how much hours in a day.

employee table -employeeid,employeename

timesheet - employeeid,totalhours,startdate

leave - leaveemployeeid,leavetype

SELECT e.employeename,convert(VARCHAR, t.startdate, 103) as startdate,SUM(t.hourstaken) as hourstaken
 FROM employee e 
INNER JOIN 
timesheet t  ON e.employeeid=t.employeeid
 where convert(VARCHAR, t.startdate, 103) = '23/04/2012'
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, t.startdate, 103) --,leave.leavetype
 HAVING SUM(t.hourstaken) < 9 order by e.employeename

取得成功就可以了.几小时后，如果任何员工花了半天时间，我想再增加一个字段.所以我要在结果中再增加一列，添加名称"leavetype"
并且任何员工都需要半天，所以只需要在Colum中呆半天.

it works fine for getting no. of hours now i want to add one more field if any employee take half day.so i want in result one more column add name ''leavetype''
and any employee take half day so just wirte in colum half day.

推荐答案

where convert(VARCHAR, t.startdate, 103) = ''23/04/2012''

您的查询中有误，TimeSheet没有startDate列，请使用e.startDate进行更正..

试试这个，它工作正常..

is wrong in Your Query, TimeSheet has no startDate column, Corrrect it with e.startDate..

Try This, Its working fine..

SELECT e.employeename,convert(VARCHAR, e.startdate, 103) as startdate,SUM(t.totalhours) as hourstaken,l.leavetype
 FROM employee e
INNER JOIN
timesheet t  ON e.employeeid=t.employeeid
inner join
leave l on l.leaveemployeeid=t.employeeid
 where convert(VARCHAR, e.startdate, 103) = '27/04/2012'
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, e.startdate, 103),l.leavetype
 HAVING SUM(t.totalhours) < 9 order by e.employeename

尝试一下..

Try this..

SELECT e.employeename,convert(VARCHAR, t.startdate, 103) as startdate,SUM(t.hourstaken) as hourstaken
 FROM employee e
INNER JOIN
timesheet t  ON e.employeeid=t.employeeid
 where convert(VARCHAR, t.startdate, 103) = '23/04/2012'
INNER JOIN 
leave l ON e.employeeid = l.leaveemployeeid
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, t.startdate, 103), l.leavetype
 HAVING SUM(t.hourstaken) < 9 order by e.employeename

HTH！

HTH!

这篇关于Querry的问题-连接三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！