mysql 检查用户是否具有多个 x 个事务或来自不同表的 x 评级，并使用 where OR

Question

我正在尝试选择用户评分(user.rating(是否大于6，或者用户是否有超过100个事务(事务表计数(。基本上计算用户有多少笔交易，然后在哪里(交易计数>= 100 或用户评分>= 6(。

尝试#1：

$user_ach_list = $db->execute("SELECT user.*, "
 . "(SELECT COUNT(*) c FROM transaction WHERE transaction.user_id=user.id AND transaction.type='L' AND transaction.status='S') AS record_count "
 . "FROM user WHERE (user.rating >= '6' OR trans >= '100') AND user.country = 'US' AND (user.can_borrow = '1' OR user.can_lend = '1')");

尝试#2

$user_ach_list = $db->execute("SELECT user.*, COUNT(transaction.id) as record_count FROM `user`, `transaction` WHERE transaction.user_id=user.id AND transaction.type='L' AND transaction.status='S' AND (user.rating >= '6' OR record_count >= '100') AND user.country = 'US' AND (user.can_borrow = '1' OR user.can_lend = '1')");

Answer 1

你可能在得到这样的COUNT()后做一个JOIN

SELECT user.*, 
xxx.record_count 
FROM `user`
JOIN ( select user_id, count(*) as record_count from `transaction`
where transaction.type='L' AND transaction.status='S' 
group by user_id) xxx
 ON xxx.user_id = user.id 
 WHERE (user.rating >= '6' OR xxx.record_count >= '100') 
 AND user.country = 'US' 
AND 1 IN (user.can_borrow , user.can_lend);