获取计数并显示要显示的订单号 - Get count and show order number to display

Get count and show order number to display

本文关键字：显示订单号获取 | 更新日期: 2023-09-27

我有一个表拉这个:

 $nurse_query = "SELECT * FROM patientinfo WHERE SCHDT = '$currentdate'
 AND SURGEON NOT LIKE 'NOT ON FILE' OR SCHDT = '$currentdate' AND TASK
 LIKE 'CLINIC%'  Order By COLBY_Arrival_time_1,SCHTM ASC";

它显示了我们一天的所有手术，按到达时间和手术时间排序。工作得很好。现在他们想让我根据外科医生和手术时间获得病人的订单号。例如:

如果前六个手术是:

Patient       Surgery Time        Surgeon
Smith          0800                Lees
Johnson        0815                Kaiser
Minnie         0800                Pappy
Niehaus        0900                Pappy
Kurle          0930                Lees
Kusiek         1000                Kaiser
Johnson        1000                Pappy

我会像这样显示

Surgery Time       Patient        Surgeon      Order
0800               Smith           Lees          1
0800               Minnie          Pappy         1
0815               Johnson         Kaiser        1
0900               Niehaus         Pappy         2
0930               Kurle           Lees          2
1000               Kusiek          Kaiser        2
1000               Johnson         Pappy         3

新增患者时，应自动重新排序医嘱号。

也许这是你想显示的表格数据

Patient Surgery     Time Surgeon
Smith Lees          0800 
Johnson Kaiser      0815 
Minnie Pappy        0800 
Niehaus Pappy       0900 
Kurle Lees          0930 
Kusiek Kaiser       1000 
Johnson Pappy       1000 

Surgery Time    Patient         Surgeon Order 
0800            Smith Lees      1 
0800            Minnie  Pappy   1 
0815            Johnson Kaiser  1 
0900            Niehaus Pappy   2 
0930            Kurle Lees      2
1000            Kusiek Kaiser   2 
1000            Johnson Pappy   3

要像这样使用

试试这个:

select surgery_time, patient, surgeon, count(surgeon) as ord from patientinfo group by surgeon,surgery_time order by surgery_time,ord
+--------------+---------+---------+-----+
| surgery_time | patient | surgeon | ord |
+--------------+---------+---------+-----+
| 0800         | smith   | lees    |   1 |
| 0800         | Minnie  | Pappy   |   1 |
| 0815         | johnson | kaiser  |   1 |
| 0900         | Niehaus | Pappy   |   1 |
| 0930         | Kurle   | lees    |   1 |
| 1000         | kusiek  | kaiser  |   1 |
| 1000         | Johnson | Pappy   |   1 |
+--------------+---------+---------+-----+

唯一没有显示的是每次计数

不可能知道表名/列名，但我假设您需要从两个不同的表中获取信息，在这种情况下，这可能是一个很好的指导方针。

连接，在本例中是INNER JOIN，允许您基于与当前表中的当前行相似的字段从另一个表中拉出一行。

SELECT 
    *
FROM 
    patientinfo
LEFT JOIN otherTable ON patientinfo.SCHDT  = otherTable.SCHDT AND patientinfo.SURGEON = otherTable.SURGEON
WHERE
    (patientinfo.SCHDT = '$currentdate' AND patientinfo.SURGEON NOT LIKE 'NOT ON FILE')
    OR
    (patientinfo.SCHDT = '$currentdate' AND patientinfo.TASK LIKE 'CLINIC%')
ORDER BY 
    COLBY_Arrival_time_1, SCHTM ASC";

编辑

谢谢你的澄清，但你真的应该仍然张贴列名，以供我们使用。

无论如何，我能够得到我想要的另一张桌子。这有点牵强，也许有人会提出更好的方法，但这就是我现在得到的。(这将需要您进行分析，以便为您的特定表工作)

SELECT 
    info.*,
    COUNT(j.userFirstName) + 1
FROM 
    patientinfo info
LEFT JOIN(
    SELECT * FROM patientinfo ORDER BY COLBY_Arrival_time_1, SCHTM ASC
) joininfo ON info.SURGEON = joininfo.SURGEON AND info.SCHDT = joininfo.SCHDT AND info.COLBY_Arrival_time_1 > joininfo.COLBY_Arrival_time_1
WHERE
    (info.SCHDT = '$currentdate' AND info.SURGEON NOT LIKE 'NOT ON FILE')
    OR
    (info.SCHDT = '$currentdate' AND info.TASK LIKE 'CLINIC%')
ORDER BY 
    COLBY_Arrival_time_1, SCHTM ASC
GROUP BY 
    info.SURGEON, 
    info.COLBY_Arrival_time_1