在我的MySQL数据库中,我有三个表:
CREATE TABLE favorites (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
location_id int(11) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE locations (
id int(20) NOT NULL,
`name` varchar(150) NOT NULL,
pos_lat float NOT NULL,
pos_lon float NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE ratings (
id int(11) NOT NULL AUTO_INCREMENT,
location_id int(11) NOT NULL,
user_id int(11) NOT NULL
stars int(11) NOT NULL,
review text,
PRIMARY KEY (id)
);
现在我想选择一些地点,并以一种有效的方式计算收视率、平均星级数和最受欢迎的数量。
我的方法是这样的,但它给了我完全错误的计数值。
SELECT l.id AS location_id,
COUNT(DISTINCT r.id), AVG(r.stars), COUNT(DISTINCT f.id)
FROM locations l, ratings r, favorites f
WHERE (l.id=r.location_id OR l.id=f.location_id)
AND l.id IN (7960,23713,...,18045,24247)
GROUP BY l.id
你能帮我吗?
问题与使用OR:的联接条件有关
WHERE (l.id=r.location_id OR l.id=f.location_id)
当它在l.id = r.location_id
中找到一条记录时,由于OR
,f中的所有行都为true。类似地,当它找到一个具有l.id = f.location_id
的记录时,您将匹配r中的所有行。
相反,为r
和f
:中的每一个使用LEFT JOIN
SELECT l.id AS location_id,
COUNT(DISTINCT r.id), AVG(r.stars), COUNT(DISTINCT f.id)
FROM locations l
LEFT JOIN ratings r ON (l.id = r.location_id)
LEFT JOIN favorites f ON (l.id = f.location_id)
WHERE l.id IN (7960,23713,...,18045,24247)
GROUP BY l.id