php - 显示采集的、通过的、失败的和释放的血液成分的计数

Question

我正在尝试显示已收集、通过、失败和释放的同名血液表条目的计数。

我有一张这样的 table

Blood Collection
--------------------------------------
id   BloodComponent      Remarks
1    Whole Blood         Passed
2    Whole Blood         Failed
3    Whole Blood         Released
4    Platelet            Passed
5    Platelet            Released
6    Plasma              Failed
7    Plasma              Released

我希望输出如下:

Name         Collected      Passed      Failed      Released
-------------------------------------------------------------
Whole Blood      3             1           1           1
Platelet         2             1           0           1
Plasma           2             0           1           1

我使用 Laravel 作为框架...

Answer 1

您可以通过pivot表查询来做到这一点:

SELECT  
  BloodComponent AS name, count(*) AS Collected,  
  sum(if(Remarks= 'Passed', 1, 0)) AS 'Passed', 
  sum(if(Remarks= 'Failed', 1, 0)) AS 'Failed', 
  sum(if(Remarks= 'Released', 1, 0)) AS 'Released' 
FROM bloodcollection 
GROUP BY BloodComponent ; 

您可以在 Laravel 中使用 selectRaw 编写自定义选择语句，如下所示:

DB::table('bloodcollection')
->selectRaw("SELECT  
BloodComponent AS name, count(*) AS Collected,  
sum(if(Remarks= 'Passed', 1, 0)) AS 'Passed', 
sum(if(Remarks= 'Failed', 1, 0)) AS 'Failed', 
sum(if(Remarks= 'Released', 1, 0)) AS 'Released'")
->groupBy("BloodComponent")
->get();