我想运行sql查询,这使我能给我带来2个日期之间不吃蛋糕的孩子。2个表:
* Child
- id, name
* Cake
- child_id, eat_timestamp
一个孩子(安迪(吃了2个蛋糕:10.01.2019和11.01.2019
我想在2019年1月1日 - 2019年10月10日之间进行搜索。因此,安迪不应该出现在列表中,而是由于左加入,它将显示他,因为它与第二个日期(11.01.2019(匹配(11.01.2019(
select * from children c LEFT JOIN
public.cake cake
ON ((cake.child_id = c.id)
AND
(
0 =
(
SELECT
COUNT(cake2.id)
FROM
public.customer t13,
public.cake cake2
WHERE
(
((cake2.child_id = t13.id)
AND
(
cake2.time_planned >= '2019-01-09 00:00:00.0'
)
)
AND
(
cake2.time_planned <= '2019-01-10 23:59:59.999'
)
)
SELECT c.*
FROM child c
INNER JOIN (
SELECT DISTINCT child_id
FROM cake c
WHERE eat_timestamp NOT BETWEEN '2019-01-09' AND '2019-01-11'
) ck ON c.id = ck.child_id
;
您可以使用不存在的情况:
select c.* from children c
where not exists (
select 1 from cake
where
child_id = c.id
and
eat_timestamp between '2019-01-09 00:00:00.0' and '2019-01-10 00:00:00.0'
)