B表有2条以上记录时,如何统计表中的所有记录

你可以这样写:

Select count(*)
from (
Select UserId, Count(UserId) from Order group by UserId having Count(UserId)>2
) Temp

尝试此操作以获取用户的订单计数

SELECT usr.id, Count(ord.id)
FROM USER usr
INNER JOIN order ord ON usr.id = ord.user_id 
GROUP BY usr.id
Having Count(ord.id)>2

如果你想计算下超过2个订单的用户数量

SELECT count(distinct usr.id)
FROM USER usr
INNER JOIN order ord ON usr.id = ord.user_id 
GROUP BY usr.id
Having Count(ord.id)>2

我只是试图修正你的查询,因为我发现你在投影上做了错误,所以我使用了子查询

SELECT * from 
   (    
    SELECT usr.id, Count(*) AS cnt 
    FROM   order ord 
    inner join USER usr
    on usr.id = ord.user_id
    group by usr.id   
    HAVING cnt >2
) as T

试试这个:

SELECT Count(*) FROM   USER usr
WHERE EXISTS(SELECT 1 FROM order
             WHERE user_id = usr.id
             GROUP BY user_id
             HAVING COUNT(*) >= 2)

让我们将查询分成两部分:

1. 所有订购超过2次的用户:

   SELECT user_id, COUNT(*) AS count FROM orders  
   GROUP BY user_id  
   HAVING COUNT(*) > 2;
   

2. 从以上结果中统计所有用户:

   SELECT COUNT(*) AS total FROM (
       SELECT user_id FROM orders  
       GROUP BY user_id  
       HAVING COUNT(*) > 2
   ) required_users;