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

Question

这个问题已经有答案了:

Filter by COUNT(*)? (4 个回答)

已关闭 6 年前。

我正在尝试选择用户评级 (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);