在mysqlselect查询中使用多个字段求和问题

Question

我有两张桌子出租和出售。存在字段状态。如果状态=1为取消发布，则状态=2为发布，状态=3为挂起。

我想为代理商找到所有已发布、未发布和待出租和销售的总和。

这是我尝试过的，但它给了我错误的数据

select sum(publish1) publish, sum(unpublish1) unpublish, sum(pending1) pending, agent_id,status from ( 
select agent_id,status, count(*) as publish1, 0 unpublish1, 0 pending1 from rentals where status = 2  GROUP BY agent_id 
union all select agent_id,status, 0 publish1, count(*) as unpublish1, 0 pending1 from rentals where status = 1  GROUP BY agent_id 
union all select agent_id,status, 0 publish1, 0 pending1, count(*) as pending1 from rentals where status = 3 GROUP BY agent_id 
union all select agent_id,status, count(*) as publish1, 0 unpublish1, 0 pending1 from sales where status = 2  GROUP BY agent_id 
union all select agent_id,status, 0 publish1, count(*) as unpublish1, 0 pending1 from sales where status = 1 GROUP BY agent_id 
union all select agent_id,status, 0 publish1, 0 pending1, count(*) as pending1 from sales where status = 3  GROUP BY agent_id ) s GROUP BY agent_id

Answer 1

根据您对问题的描述，您的查询看起来是正确的。这是编写查询的另一种方法。它只是在union:之前进行聚合

select agent_id,
       sum(case when status = 2 then val else 0 end) as publish,
       sum(case when status = 1 then val else 0 end) as unpublish,
       sum(case when status = 3 then val end) as pending
from ((select status, agent_id, status, count(*) as val
       from rentals
       where status in (2, 1, 3)
       group by status, agent_id 
      ) union all
      (select status, agent_id, count(*) as val
       from sales
       where status in (2, 1, 3)
       group by status, agent_id
      )
     ) t
group by agent_id;