在where子句中使用该计数值。

I'm trying to select if a user rating (user.rating) is greater then 6 or if the user has more then 100 transactions (transaction table count). Basically count how many transactions the user has then where (transaction count >= 100 OR user rating >= 6).

我正在尝试选择一个用户等级(user.rating)是否大于6，或者如果用户有更多的100个事务(事务表计数)。基本上计算用户有多少事务(事务数>= 100或用户等级>= 6)。

SELECT *
FROM   `user`
JOIN   (SELECT COUNT(*) 
        FROM   transaction 
        WHERE  transaction.user_id=user.id 
        AND type='L' 
        AND status='S') AS tcount 
WHERE  (user.rating >= '6' OR tcount >= '100')

3 个解决方案

#1

Just another possible answer. I've created simplified schemas to test it, please try it and let me know the result.

另一个可能的答案。我已经创建了简化的模式来测试它，请尝试一下，并让我知道结果。

SELECT * 
FROM user
WHERE user.rating >= 6 OR (SELECT COUNT(*) FROM transaction WHERE user_id = user.id and type = 'L' and status = 'S') >= 100;

#2

Use an alias on COUNT(*)

在COUNT(*)上使用别名

SELECT *
FROM   `user`
JOIN   (SELECT user_id, COUNT(*) cnt
        FROM   transaction 
        WHERE type='L' 
        AND status='S'
        GROUP BY user_id) AS tcount 
ON      user.id = tcount.user_id
WHERE  (user.rating >= '6' OR tcount.cnt >= '100')

#3

You can write that without the subquery, like this

没有子查询，可以这样写。

SELECT  u.id
FROM `user` u
JOIN `transaction` t
ON t.user_id=u.id 
WHERE t.type = 'L' AND t.status = 'S'
GROUP BY u.id
HAVING sum(case when u.rating >= 6 then 1 end) > 0 OR count(*) >= 100

#1