我有一个人员表、一个单位表和一个健康表(如下所述)。基本上,在我的工作中,我们必须检查人们,如果他们两天没露面,我们就会去找他们。。。我希望能够从健康表中选择所有超过两天的记录,但只能选择最新的记录(因为每个人每天可能会有多个条目,这就是我想要的,因为有了wellness.username
字段,你可以知道谁看到了谁,什么时候看到)。
People
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fname | varchar(32) | NO | | NULL | |
| lname | varchar(32) | NO | | NULL | |
| dob | date | NO | | 0000-00-00 | |
| license_no | varchar(24) | NO | | NULL | |
| date_added | timestamp | NO | | CURRENT_TIMESTAMP | |
| status | varchar(8) | NO | | Allow | |
+------------+-------------+------+-----+-------------------+----------------+
Units
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | varchar(3) | NO | | NULL | |
| resident | int(11) | NO | MUL | NULL | |
| type | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
+--------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+----------------+
| wellness_id | int(11) | NO | PRI | NULL | auto_increment |
| people_id | int(11) | NO | | NULL | |
| time_checked | timestamp | NO | | CURRENT_TIMESTAMP | |
| check_type | varchar(1) | NO | | NULL | |
| username | varchar(16) | NO | | jmd9qs | |
+--------------+-------------+------+-----+-------------------+----------------+
units
表很难看,我知道,但很快就会改变;目前resident
引用people.id
。
这是我的最小工作示例,它只给我一个结果,即使wellness
中有多个people
的time_checked
行超过2天。我得到的一个结果更像是4-1/2天。。。。
select w.wellness_id, p.id, p.lname, p.fname, u.number, u.type,
w.time_checked, w.check_type, w.username
from people p
left join units u on p.id = u.resident
right join wellness w on p.id = w.people_id
WHERE w.time_checked <= DATE_ADD(CURDATE(), INTERVAL -2 DAY)
order by w.time_checked asc;
我正在尝试获取超过两天的最新记录,但wellness
表中的每个people
只有1条。我加入了所有其他东西,因为我需要它来用PHP显示记录。
很抱歉我的问题太散漫了,希望问题足够清楚!
编辑-样本数据:
+------+------+--------+---------------------+------------+----------+
| w_id | id | number | time_checked | check_type | username |
+------+------+--------+---------------------+------------+----------+
| 100 | 2 | 425 | 2013-08-23 21:03:00 | s | jmd9qs |
| 101 | 2 | 425 | 2013-08-25 05:41:01 | s | jmd9qs |
| 91 | 2 | 425 | 2013-08-20 19:52:23 | s | jmd9qs |
| 83 | 4 | 416 | 2013-08-23 20:12:29 | s | jmd9qs |
| 76 | 5 | 408 | 2013-08-23 20:11:21 | s | jmd9qs |
| 62 | 6 | 327 | 2013-08-23 20:06:13 | s | jmd9qs |
| 18 | 7 | 204 | 2013-08-23 19:43:58 | s | jmd9qs |
| 31 | 8 | 219 | 2013-08-23 19:51:11 | s | jmd9qs |
| 97 | 9 | 432 | 2013-08-23 20:16:39 | o | jmd9qs |
| 44 | 10 | 309 | 2013-08-23 19:55:45 | s | jmd9qs |
+------+------+--------+---------------------+------------+----------+
IMHO RIGHT JOIN
从people
到wellness
是没有意义的,因为当wellness
中有一个人,但people
表中没有这个人时,不应该出现这种情况。但反之亦然。你有了一个新人,但还没有任何关于他的健康信息。
话虽如此,你的查询可能看起来像这个
SELECT z.wellness_id, p.id, z.time_checked, z.check_type, z.username
FROM people p JOIN units u
ON p.id = u.resident LEFT JOIN
(
SELECT w.*
FROM
(
SELECT people_id, MAX(time_checked) time_checked
FROM wellness
GROUP BY people_id
) q JOIN wellness w
ON q.people_id = w.people_id
AND q.time_checked = w.time_checked
) z
ON p.id = z.people_id
WHERE COALESCE(time_checked, 0) < CURDATE() - INTERVAL 2 DAY
这是SQLFiddle演示
下面是得到我想要的查询:
select p.id, u.number,
w.time_checked, w.check_type, w.username
from people p inner join units u on p.id = u.resident
left join wellness w on p.id = w.people_id
left outer join wellness as w2 ON w.people_id = w2.people_id
and w.time_checked < w2.time_checked
where w2.people_id is null and w.time_checked < (NOW() - INTERVAL 2 DAY)
order by w.time_checked asc