在一个教程中,我看到了这个SQL查询:
SELECT a.id as a_id, a.info as a_info,
b.id as b_id, b.info as b_info
FROM stats AS a
JOIN stats as b
ON a.id != b.id
LIMIT 25
查询运行良好,但现在我需要添加WHERE条件。我有users
表。表stats
包含列user_id
。我试图从表stats
中获取所有用户,如果是users.city=1
(例如),但我仍然找不到方法,如何实现。。。
我的问题是,我目前不知道如何在查询中放入另一个JOIN(用于表users
)。
我将感谢您的每一个建议,谢谢
当您在查询中使用stats
表两次时,实际上users
表也需要两个联接。(用于统计数据A和统计数据B)
SELECT a.id as a_id, a.info as a_info,
b.id as b_id, b.info as b_info
FROM stats AS a
JOIN stats AS b ON a.id != b.id
JOIN users AS a_users ON a.user_id = a_users.id
JOIN users AS b_users ON b.user_id = b_users.id
WHERE a_users.city = 1
OR b_users.city = 1
LIMIT 25
所以这就是你所拥有的。。。
这是您返回的
SELECT a.id as a_id, a.info as a_info, b.id as b_id, b.info as b_info
这就是的来源
FROM stats AS a
JOIN stats as b on a.id != b.id
这就是你想要的数量
LIMIT 25
因此,如果您想添加users表,请在其他加入之后添加此表
JOIN users ON a.user_id = users.id
要过滤它,请在加入后添加它
WHERE users.city=1
提供
SELECT a.id as a_id, a.info as a_info, b.id as b_id, b.info as b_info
FROM stats AS a
JOIN stats as b on a.id != b.id
JOIN users ON a.user_id = users.id
WHERE users.city=1
LIMIT 25
这里有一种方法:
SELECT a.id as a_id, a.info as a_info,
b.id as b_id, b.info as b_info
FROM stats AS a
JOIN stats as b
ON a.id != b.id
where a.user_id in (select user_id from users where users.city = 1)
LIMIT 25
您也可以将其表示为联接(MySQL中的联接效率更高):
SELECT a.id as a_id, a.info as a_info,
b.id as b_id, b.info as b_info
FROM stats AS a
JOIN stats AS b
ON a.id != b.id
JOIN users AS u
ON a.user_id = u.user_id
AND u.city_id = 1
LIMIT 25
为了方便起见,我重新格式化了您的原始SQL语句,使其可读。
SELECT a.id AS a_id
, a.info AS a_info
, b.id AS b_id
, b.info AS b_info
FROM stats a
JOIN stats b
ON a.id != b.id
LIMIT 25
您当前的查询几乎返回笛卡尔乘积。统计数据中的每一行都与统计数据中每一行相匹配,除了匹配本身。(假设stats.id
是主密钥或唯一密钥。)
要向用户表添加联接,限制从a
返回的行,例如:
SELECT a.id AS a_id
, a.info AS a_info
, b.id AS b_id
, b.info AS b_info
FROM stats a
JOIN users au ON au.id = a.user_id AND au.city=1
JOIN stats b ON a.id != b.id
LIMIT 25
如果要限制为a
和b
返回的行,请在users
表中添加另一个联接:
SELECT a.id AS a_id
, a.info AS a_info
, b.id AS b_id
, b.info AS b_info
FROM stats a
JOIN users au ON au.id = a.user_id AND au.city=1
JOIN stats b ON a.id != b.id
JOIN users bu ON bu.id = b.user_id AND bu.city=1
LIMIT 25
这并不是实现这一目标的唯一途径。例如,可以使用a.user_id IN (subquery)
或EXISTS (subquery)
谓词。
(如果您以可读的方式格式化SQL,那么它更容易使用。)