mysql - 在 mysql 连接查询中需要帮助 - codeigniter

Question

我在 mysql 中有一个表，其中包含以下数据

[代码]表名测试

Assettype   Serial_No     Status            location    
Mouse        123456      In Stock           chennai
Mouse        98765       Allocated          chennai
Keyboard     23498       In Stock           bangalore
Keyboard     45646       Allocated          bangalore
Mouse        234234      Decommisioned      hyderabad

我正在寻找一个 mysql 查询，它将给出下面提到的输出

Assettype              In Stock    Allocated    Decommisioned       Location  
Mouse                   1            1             0                chennai          
Keyboard                1            1             0                bangalore
Mouse                   0            0             1                hyderabad

请帮忙

Answer 1

您可以对 Assets 类型和位置进行分组并查找如下计数:

select 
  assettype,
  sum(status = 'In Stock') In_stock,
  sum(status = 'Allocated') Allocated,
  sum(status = 'Decommisioned') Decommisioned,
  location
from test
group by assettype, location;