我有一个country
带桌子id
,country_uuid
,visited
,arrival
列。
例如:
CREATE TABLE `country` (
`id` serial primary key,
`country_uuid` varchar(191) NOT NULL,
`visited` varchar(5) DEFAULT NULL,
`arrival` DATE NOT NULL
);
INSERT INTO country (country_uuid, visited, arrival) VALUES
('abc', null, "2019.01.01"),
('abc', 'yes',"2019.01.02"),
('cba', 'no', "2019.01.03"),
('def', 'yes',"2019.01.04"),
('def', 'yes',"2019.01.04"),
('cba', 'yes',"2019.01.04"),
('abc', 'yes',"2019.01.05");
我需要一个mySQL查询,例如对于从2019.01.03到2019.01.06的时间段将返回1(基于)visited = yes
,但未列出visited = yes
2019.01.03之前的时间段。因此,返回1是因为仅一次访问了cba,但是即使也一次访问了该时间段的abc,它还是在2019.01.02的2019.01.03之前被访问了。
def不被计数,因为它在该时间段内被访问过两次。
最后,正如我提到的,我需要看到以下内容:
unique_country_uuid_count
1
我什至不知道该怎么缠头。我在这段时间里使用了distinct,但是我不知道如何根据以前的数据进行排除。
我什至不确定我编写的代码是否正确,但是我尝试了以下操作:
SELECT DISTINCT
con.country_uuid,
con.visited AS visited,
con.arrival AS arrival
FROM country con
WHERE
(arrival BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()) AND
visited IS NOT NULL AND
visited='yes' ;
链接到sqlfiddle
解:
首先,我最终使用了@Alberto Moro解决方案,但随后采用了@Strawberry方式,因为它效率更高:
SELECT COUNT(*)
FROM country con1
LEFT JOIN country con2 ON con1.country_uuid = con2.country_uuid
AND con2.arrival < con1.arrival
AND con2.visited IN ('yes', 'no')
WHERE con1.visited = 'yes'
AND con1.arrival BETWEEN '2019-01-03' AND '2019-01-06'
AND con2.id IS NULL;
感谢您的帮助或提前提示。
SELECT x.country_uuid
, COUNT(*) total
FROM country x
LEFT
JOIN country y
ON y.country_uuid = x.country_uuid
AND y.visited = 'yes'
AND y.arrival < '2019-01-03'
WHERE x.visited = 'yes'
AND x.arrival BETWEEN '2019-01-03' AND '2019-01-06'
AND y.id IS NULL
GROUP
BY x.country_uuid;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句