表事件视图
ID + EVENT_ID + VIEW_DATE + MEMBER_ID
表member_city_access
ID + MEMBER_ID + CITY_ID + PRIMARY(not PK) + VIEWING
表城市
ID + TITLE
我正试图从某个城市的会员那里获得某个活动的浏览量。
这就是我的
SELECT COUNT(`event_views`.`id`) c FROM `event_views`
LEFT JOIN `member_city_access` city ON city.`member_id` = `event_views`.`member_id`
LEFT JOIN `cities` on cities.id = city.city_id WHERE (city.city_id = '92' AND city.`primary` = 'on')
AND `event_views`.`view_date` >= '20131103'
AND `event_views`.`view_date` < '20131110'
我想知道我是否使用了正确的"JOIN"进行计数，因为数字与事件不一致，所以在我的报告中显示（1个城市）
EVENTS ADDED = 0 , EVENTS VIEWED = 3
该查询返回所有与where子句匹配的视图。如果你想按事件将它们分开，你必须按事件分组：
SELECT `event_views`.`id`,COUNT(`event_views`.`id`) c
FROM `event_views`
LEFT JOIN `member_city_access` city
ON city.`member_id` = `event_views`.`member_id`
LEFT JOIN `cities`
ON cities.id = city.city_id
WHERE city.city_id = '92'
AND city.`primary` = 'on'
AND `event_views`.`view_date` >= '20131103'
AND `event_views`.`view_date` < '20131110'
GROUP BY `event_views`.`id`