mysql - 多重连接的 SUM 记录错误

Question

我有 3 个表请求、计划、级别。

这是表格请求:

 id  plan_id response_code client_id
  1  1        200           1          
  2  1        200           1
  3  1        400           1
  4  1        500           1

这是表格计划:

 id       client_id
  1             1         
  2             1
  3             1
  4             1

这是表价:

     id  plan_id
      1  1               
      2  2       
      3  3       
      4  4     

我想要连接 3 个表并在表请求中计数，response_code 为 200、400 和 500 的记录有多少。但我得到了错误的值。这是我的查询

        SELECT 
      requests.id,
      SELECT SUM(case 
      when requests.response_code = 200 then 
      1 else 0
       end) as resquest200,
      SELECT SUM(case 
      when requests.response_code = 500 then 
      1 else 0
       end) as resquest500,
      response_code,
    FROM requests
    JOIN plan
    ON requests.plan_id = plan.id
    JOIN price
    ON plan.id = price.plan_id
    GROUP BY request.id

我尝试过:

 SELECT 
 request.id,
 (
SELECT SUM(case 
when request.response_code = 200 then 
1 else 0
end) FROM request) as resquest200,
response_code,
FROM request
JOIN plan
ON requests.plan_id = plan.id
JOIN price
ON plan.id = price.plan_id
GROUP BY request.id

并且它的总和正确。如何优化我的查询？请帮助我

Answer 1

如果您想确定请求总数，那么 COUNT() 是比 SUM() 更好的选择

SELECT COUNT(*), response_code FROM requests
GROUP BY response_code

您为什么要加入计划和价格？您需要从这些表中返回一些数据吗？