收集、传递、失败和释放血液成分的显示计数

Question

我正在尝试显示相同名称的收集、传递、失败和释放的血表条目的计数。

我有一张这样的桌子

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

我用拉拉作为框架..。

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 ; 

您可以使用selectRaw编写如下所示的自定义select语句，在laravel中这样做：

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