环境:
假设我现在有一张表,有两个字段:name,create_time
然后我现在有一台摄像头,当有人从摄像头下走过时表中就会根据当天时间插入一条数据
需求:
给定一个时间段,如:2019-02-01到2019-02-07
有没有办法直接一条SQL就查询出这个时间段每天都出现的人呢?
假设我现在有一张表,有两个字段:name,create_time
然后我现在有一台摄像头,当有人从摄像头下走过时表中就会根据当天时间插入一条数据
需求:
给定一个时间段,如:2019-02-01到2019-02-07
有没有办法直接一条SQL就查询出这个时间段每天都出现的人呢?
SET @begin := '2019-02-01' ;
SET @end := '2019-02-07' ;
SELECT t.name FROM table1 t WHERE DATE_FORMAT(t.create_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(@begin,'%Y-%m-%d') AND DATE_FORMAT(@end,'%Y-%m-%d')
GROUP BY t.name,DATE_FORMAT(t.create_time,'%Y-%m-%d') HAVING COUNT(0) = DATEDIFF(@end,@begin) + 1 ;
我按照你说的是这么写的:
SELECT
p.NAME,
t.photo AS cardImage,
tba.location,
tba.pass_time,
tba.full_image
FROM
pedestrian tba
JOIN person_basic_info p ON p.face_id = tba.face_id
JOIN person_card_feature t on t.id=p.id
where tba.pass_time BETWEEN '2018-10-13 00:00:00' AND '2018-10-15 23:00:00'
AND tba.location ='1'
group by p.name,tba.pass_time HAVING COUNT(0) = DATEDIFF('2018-10-13 00:00:00', '2018-10-15 23:00:00') + 1 ;
说说您修改以后,哪不行吧。
或者把执行结果发出来也行。
您说不行的sql的查询结果。
SET @begin := '2019-02-01' ;
SET @end := '2019-02-07' ;
SELECT t.name FROM table1 t WHERE DATE_FORMAT(t.create_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(@begin,'%Y-%m-%d') AND DATE_FORMAT(@end,'%Y-%m-%d')
GROUP BY t.name,DATE_FORMAT(t.create_time,'%Y-%m-%d') HAVING COUNT(DISTINCT DATE_FORMAT(t.create_time,'%Y-%m-%d')) = DATEDIFF(@end,@begin) + 1 ;
我用的是这个SQL去测试的:
SELECT
p.NAME,
t.photo AS cardImage,
tba.location,
tba.pass_time,
tba.full_image
FROM
pedestrian tba
JOIN person_basic_info p ON p.face_id = tba.face_id
JOIN person_card_feature t on t.id=p.id
where tba.pass_time BETWEEN '2018-10-13 00:00:00' AND '2018-10-15 23:00:00'
AND tba.location ='1'
HAVING COUNT(DISTINCT DATE_FORMAT(tba.pass_time,'%Y-%m-%d')) = DATEDIFF('2018-10-15 23:00:00','2018-10-13 00:00:00') + 1 ;
然后数据是这样的:只显示一条数据了,我想让符合条件的数据都显示,请问有办法么。。
SET @begin := '2019-02-01 01:02:03' ;
SET @end := '2019-02-07 03:02:01' ;
SELECT t.face_id FROM table1 t WHERE DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%i:%s') BETWEEN DATE_FORMAT(@begin,'%Y-%m-%d %H:%i:%s') AND DATE_FORMAT(@end,'%Y-%m-%d %H:%i:%s')
GROUP BY t.face_id,DATE_FORMAT(t.create_time,'%Y-%m-%d') HAVING COUNT(DISTINCT DATE_FORMAT(t.create_time,'%Y-%m-%d')) = DATEDIFF(@end,@begin) + 1 ;
select count(name) as count ,namefrom (
select distinct username,date(create_time ) as date from table where create_time BETWEEN '2018-02-01' and '2019-02-08'
)a GROUP BY name
)a where count=7这样就可以查出来了,如果数据量很大的话,就用Max函数去除每天的重复。我这是用的是distinct 简单行事。
select count(name) as count ,namefrom (
select distinct username,date(create_time ) as date from table where create_time BETWEEN '2019-02-01' and '2019-02-08'
)a GROUP BY name
)a where count=7这样就可以查出来了,如果数据量很大的话,就用Max函数去除每天的重复。我这是用的是distinct 简单行事。
(写错时间了,修改一下)