求SQL语句

Question

编号          时间         控制器  门号        门名称          用户Id
80431 2008-2-1 8:39 1 1 大门(进门) 12
80432 2008-2-1 8:49 1 1 大门(进门) 128
80433 2008-2-1 8:54 1 1 大门(进门) 168
80434 2008-2-1 8:54 1 1 大门(进门) 228
80435 2008-2-1 8:55 1 1 大门(进门) 12
80436 2008-2-1 8:58 1 2 大门(出门) 12
80437 2008-2-1 8:59 1 1 大门(进门) 128
80438 2008-2-1 8:59 1 2 大门(出门) 128
80439 2008-2-1 8:59 1 1 大门(进门) 228
80440 2008-2-1 9:02 1 1 大门(进门) 128
80441 2008-2-1 9:03 1 1 大门(进门) 12
80442 2008-2-1 9:04 1 1 大门(进门) 228
80443 2008-2-1 9:04 1 1 大门(进门) 228
80444 2008-2-1 9:04 1 2 大门(出门) 228
如上数据中，门号为1的时间为进门时间，门号为2的为出门时间，想根据如上数据统计出每个用户每天的最早进门时间和最晚出门时间在一条数据中，即每个人每天只有一条数据。如下所示
 最早进门时间     最晚出门时间       用户
2008-2-1 8:39   2008-2-1 8:58    12
2008-2-1 8:49   2008-2-1 8:59    128
2008-2-1 8:54   2008-2-1 9:04     228
请问如何实现？

Answer 1

select i.用户Id,i.CHKTIME,o.CHKTIME
from (
select 用户Id,min(时间) as CHKTIME
from yourTable
where 门号=1) as i inner join (
select 用户Id,max(时间) as CHKTIME
from yourTable
where 门号=2 ) as o on i.用户Id=o.用户Id

== 思想重于技巧 ==

  

Answer 2

select iif(isnull(a.用户Id),b.用户Id,a.用户Id),a.CHKTIME as i,b.CHKTIME as o from (
(select 用户Id,min(时间) as CHKTIME
from tt where 门号=1 group by 用户Id ) a
left join
(select 用户Id,min(时间) as CHKTIME
from tt where 门号=2 group by 用户Id ) b
on a.用户Id=b.用户Id)

Answer 3

select i.用户Id,i.CDAY,i.CHKTIME,o.CHKTIME
from (
select 用户Id,DATE_FORMAT(date,'%Y%m%d') as CDAY,min(时间) as CHKTIME
from yourTable
where 门号=1
group by 用户Id,DATE_FORMAT(date,'%Y%m%d')
) as i 
inner join  (
select 用户Id,DATE_FORMAT(date,'%Y%m%d') as CDAY,max(时间) as CHKTIME
from yourTable
where 门号=2 
用户Id,DATE_FORMAT(date,'%Y%m%d')
) as o on i.用户Id=o.用户Id and i.CDAY=o.CDAY

== 思想重于技巧 ==

  

Answer 4

可以视情况换inner join 为 left join

== 思想重于技巧 ==

  

Answer 5

经过测试的，确保DROP TABLE IF EXISTS `test`.`doorrecord`;
CREATE TABLE  `test`.`doorrecord` (
  `rec_id` int(5) NOT NULL auto_increment,
  `rec_datetime` datetime NOT NULL,
  `rec_type1` int(1) NOT NULL default `1`,
  `rec_Flag` int(1) NOT NULL default `1`,
  `rec_Comment` varchar(45) default NULL,
  `rec_userid` varchar(15) default NULL,
  PRIMARY KEY  (`rec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into test.Doorrecord values (80431, '2008-2-1 8:39', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80432, '2008-2-1 8:49', 1, 1, 'Door(IN)', '128' );
insert into test.Doorrecord values (80433, '2008-2-1 8:54', 1, 1, 'Door(IN)', '168' );
insert into test.Doorrecord values (80434, '2008-2-1 8:54', 1, 1, 'Door(IN)', '228');
insert into test.Doorrecord values (80435, '2008-2-1 8:55', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80436, '2008-2-1 8:58', 1, 2, 'Door(OUT)','12' );
insert into test.Doorrecord values (80437, '2008-2-1 8:59', 1, 1, 'Door(IN)', '128');
insert into test.Doorrecord values (80438, '2008-2-1 8:59', 1, 2, 'Door(OUT)','128' );
insert into test.Doorrecord values (80439, '2008-2-1 8:59', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80440, '2008-2-1 9:02', 1, 1, 'Door(IN)', '128');
insert into test.Doorrecord values (80441, '2008-2-1 9:03', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80442, '2008-2-1 9:04', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80443, '2008-2-1 9:04', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80444, '2008-2-1 9:04', 1, 2, 'Door(OUT)','228' );
insert into test.Doorrecord values (80445, '2008-2-1 9:58', 1, 2, 'Door(OUT)','12' );insert into test.Doorrecord values (80451, '2008-2-2 8:39', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80452, '2008-2-2 8:49', 1, 1, 'Door(IN)', '128' );
insert into test.Doorrecord values (80453, '2008-2-2 8:54', 1, 1, 'Door(IN)', '168' );
insert into test.Doorrecord values (80454, '2008-2-2 8:54', 1, 1, 'Door(IN)', '228');
insert into test.Doorrecord values (80455, '2008-2-2 8:55', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80456, '2008-2-2 8:58', 1, 2, 'Door(OUT)','12' );
insert into test.Doorrecord values (80457, '2008-2-2 8:59', 1, 1, 'Door(IN)', '128');
insert into test.Doorrecord values (80458, '2008-2-2 8:59', 1, 2, 'Door(OUT)','128' );
insert into test.Doorrecord values (80459, '2008-2-2 8:59', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80460, '2008-2-2 9:02', 1, 1, 'Door(IN)', '128');
insert into test.Doorrecord values (80461, '2008-2-2 9:03', 1, 1, 'Door(IN)', '12' );
insert into test.Doorrecord values (80462, '2008-2-2 9:04', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80463, '2008-2-2 9:04', 1, 1, 'Door(IN)', '228' );
insert into test.Doorrecord values (80464, '2008-2-2 9:04', 1, 2, 'Door(OUT)','228' );
select
  ifnull(detail1.min_in_time,'') as min_in_time,
  ifnull(detail2.max_out_time,'') as max_out_time,
  main.rec_userid
from 
(  doorrecord as main left join
(
  select 
    rec_userid,
    min(rec_datetime) as min_in_time
  from
    doorrecord
  where
    rec_flag = 1
  group by rec_userid,DATE(rec_datetime)
) as detail1 on main.rec_userid = detail1.rec_userid and main.rec_datetime = detail1.min_in_time) left join
(
  select
    rec_userid,
    max(rec_datetime) as max_out_time
  from
    doorrecord
  where
    rec_flag = 2
  group by rec_userid,DATE(rec_datetime)
) as detail2 on
  detail1.rec_userid = detail2.rec_userid
  and date(detail1.min_in_time) = date(detail2.max_out_time)
where
  detail1.min_in_time is not null or detail2.max_out_time is not null
order by
  main.rec_userid,main.rec_datetime

Answer 6

我用的表名，列名和你得不太一样，改改再用吧~~粗略观察了一下结果是对的，多连了个main表为了解决左联的问题~~

Answer 7

用了7楼数据.写完后发现和7楼差不多, 输出结果除了左联基本相同select come_table.rec_userid, come_table.come_time, leave_table.leave_time from ((select rec_userid, max(rec_datetime) as leave_time from doorrecord where rec_Flag=2 group by rec_userid, DATE(rec_datetime)) as leave_table inner join (select rec_userid, min(rec_datetime) as come_time from doorrecord where rec_Flag=1 group by rec_userid, DATE(rec_datetime)) as come_table on leave_table.rec_userid = come_table.rec_userid and DATE(come_table.come_time)=DATE(leave_table.leave_time))rec_userid  come_time  leave_time  
12 2008-02-01 08:39:00 2008-02-01 09:58:00 
12 2008-02-02 08:39:00 2008-02-02 08:58:00 
128 2008-02-01 08:49:00 2008-02-01 08:59:00 
128 2008-02-02 08:49:00 2008-02-02 08:59:00 
228 2008-02-01 08:54:00 2008-02-01 09:04:00 
228 2008-02-02 08:54:00 2008-02-02 09:04:00 

Answer 8

推荐用left join 不然太不容易发现问题了。例如：这个人当天进来后，当天没出去，或是出去后，当天没回来，
这两个特殊情况显不出来。用left join 可以显示出来为null 的可以在处理。以致于不会从业务上少统计东西。

Answer 9

很是同意10楼的意见，所以我的方案才会复杂了一些~~
最后那个where条件也是为了过滤这一天既没有进门也没有出门的纪录的。
不过效率上可能不是很好~~

Answer 10

的确，如果用INNER会忽略到某些情况。这个就要看具体需求了。我当时是主要考虑到这句“想根据如上数据统计出每个用户每天的最早进门时间和最晚出门时间在一条数据中”