显示收集、通过、失败和释放的血液成分计数 - Display Count of Collected, Passed, Failed and Released Blood Components

Display Count of Collected, Passed, Failed and Released Blood Components

本文关键字：释放通过失败显示 | 更新日期: 2023-09-27

我正在尝试显示具有相同名称的收集，通过，失败和释放的血表条目的计数。

我有一张这样的桌子

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

我正在使用拉拉维尔作为框架...

您可以通过

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 编写自定义 Select 语句，如下所示：

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();