查询以从表中获取详细信息 [英] query to get details from a table

问题描述

大家好..

我有这样的表:

hi all..

i have table like:

uid	empid	datetime	     
1	9003	2012-09-26 09:20:00	
3	9000	2012-09-26 09:20:00
4	9004	2012-09-26 09:20:00
5	9002	2012-09-26 09:20:00
7	9001	2012-09-26 09:20:00
11	9004	2012-09-26 08:59:00
12	9002	2012-09-26 08:59:00
14	9001	2012-09-26 08:59:00

在这里，我需要获取详细信息，例如谁在9:00之后获得日期时间，但他没有在9之前获得日期时间，因此结果应该是

here i need to get details like who get datetime after 9:00 but he did''t get datetime befor 9 so the result should be

uid	empid	datetime	     
1	9003	2012-09-26 09:20:00	
3	9000	2012-09-26 09:20:00

我实际上已经编写了此查询，我需要绑定三个表并获得一个emp名称的结果，并获取另一个表以获取时间点以获取结果，因此查询如下所示:

i have written this query actually i need to bind three tables and get the result one emp names and another table for getting point of time to get results so the query made like:

SELECT concat(s.firstname,' ',s.midname,' ',s.lastname) as employee,
       id,
       s.shiftname, 
       DATE_FORMAT(DATE(`datetime`),'%d/%b/%y') as logdate,
       DATE_FORMAT(MIN(`datetime`),'%r') as logtime,
       starttime  
  FROM `attlog` a,
       staffdetails s,
       shifts f 
 WHERE s.id = a.empid 
   AND f.shiftname = s.shiftname 
   AND DATE_FORMAT(DATE(a.datetime),'%m/%d/%Y') Between '09/26/2012' AND '09/26/2012' 
   AND TIME(a.datetime)<'9:00:00' 
   AND TIME(a.datetime)<'18:00:00' 
 GROUP BY id, datetime 
 ORDER BY cast(s.id as signed), a.datetime

此图显示了所有emp的结果，其中datetime在9之后
但是我们需要防止条件设置结果，例如谁在9 AM之前获得日期时间
请任何人帮我

this one shows me results of all emps where datetime after 9
but we need to prevent result set by a condition like who are get datetime before 9 AM
please any one help me
thanks in advancce..

推荐答案

我在查询中看到的第一个问题是这个

The first issue I see in your query is this

...
AND TIME(a.datetime)<'9:00:00'
AND TIME(a.datetime)<'18:00:00'

基本上，根据您的查询，您将返回给定日期午夜至18:00之间的所有时间.
我相信您需要的是这个

Basically with your query you are returning all times between midnight and 18:00 on the given day.
What I believe you need is this

AND TIME(a.datetime)>='9:00:00' 
AND TIME(a.datetime)<='18:00:00'

select * from tm where DATEPART(hh, [savedate])>=9 and empid not in (select empid from tm where DATEPART(hh, [savedate])<9)

更新的解决方案:

Updated solution:

;With Cte
as
(select t1.* from tm t1
inner join tm t2 on t1.empid=t2.empid and convert(char(10), t1.[savedate], 111)=convert(char(10), t2.[savedate], 111)
where  DATEPART(hh, t1.[savedate])>=9 and DATEPART(hh, t2.[savedate])<9)
select * from tm where tm.empid not in (select empid from Cte where convert(char(10), tm.[savedate], 111)=convert(char(10), cte.[savedate], 111))

输出:

output:

uid empid   savedate
1   9003    2012-09-26 09:20:00.000
3   9000    2012-09-26 09:20:00.000

我的表并插入查询

my table and insert query

create table tm
(uid int,
empid int,
savedate datetime)
insert into tm values (1,	9003,	'2012-09-26 09:20:00')	
insert into tm values (3,	9000,	'2012-09-26 09:20:00')
insert into tm values (4,	9004,	'2012-09-26 09:20:00')
insert into tm values (5,	9002,	'2012-09-26 09:20:00')
insert into tm values (7,	9001,	'2012-09-26 09:20:00')
insert into tm values (11,	9004,	'2012-09-26 08:59:00')
insert into tm values (12,	9002,	'2012-09-26 08:59:00')
insert into tm values (14,	9001,	'2012-09-26 08:59:00')

)

查看是否

这篇关于查询以从表中获取详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！