mySQL 中的矩阵/交叉表/枢轴查询 [英] matrix/crosstab/pivot query in mySQL

问题描述

我有一张像这样的 Bill 表

I have a Bill table like this

BillNo - 1，ServiceCode -1，ServiceName - 'Lab test'，ItemAmount -30

BillNo - 1, ServiceCode -1, ServiceName - 'Lab test' , ItemAmount -30

BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70

BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70

我需要这样的输出

Bill No -1, Total Bill Amount-100, Lab Test-30, Consultation-70, Drugs-0, Misc-0

Bill No -1, Total Bill Amount-100 , Lab Test-30, Consultation-70, Drugs-0, Misc-0

所以基本上我需要账单金额，它是按 BillNo 分组的所有 ItemAmount 的总和.然后在同一行中，我需要将此账单金额分配给 4 个服务名称.因此 100 份分配为 30 份用于实验室测试，70 份用于咨询.

So basically i need the Bill Amount which is the SUM of all ItemAmount Grouped by BillNo. Then in the same row i need to distribute this Bill Amount to the 4 ServiceNames. So 100 gets distributed as 30 for Lab test and 70 for Consultation.

这给了我我想要的但不是最佳的.

This gave me what i want but it is not optimal.

SELECT
    BD.BILLNO AS BN,
    SUM(BD.ITEMAMOUNT) AS "Bill Amount",
    (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
        WHERE SERVICENAME LIKE '%Lab%'
        AND BD.BILLNO = BN ) AS "Lab",
    (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
        WHERE SERVICENAME LIKE '%Consult%'
        AND BD.BILLNO = BN ) AS "Consultation",
    (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
        WHERE SERVICENAME LIKE '%Procedure%'
        AND BD.BILLNO = BN ) AS "Procedures",
    (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
        WHERE SERVICENAME LIKE '%Drugs%'
        AND BD.BILLNO = BN ) AS "Drugs",
    (SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
        WHERE SERVICENAME NOT LIKE '%Lab%'
        AND SERVICENAME NOT LIKE 'Consult%'
        AND SERVICENAME NOT LIKE '%Procedure%'
        AND SERVICENAME NOT LIKE '%Drugs%'
        AND BD.BILLNO = BN )AS "Miscellaneous"
FROM BILLDETAILS BD
GROUP BY BD.BILLNO

我该如何改进这个 mySQL 查询?

How can i improve this mySQL query ?

谢谢.查克.

推荐答案

试试这个:

SELECT
  BillNo,
  LabTest,
  Consultation,
  Drugs,
  Misc,
  LabTest + Consultation + Drugs + Misc AS BillAmount
FROM (
  SELECT
    BillNo,
    IFNULL(GROUP_CONCAT(IF(ServiceCode=1, ItemAmount, NULL)), 0) AS LabTest,
    IFNULL(GROUP_CONCAT(IF(ServiceCode=2, ItemAmount, NULL)), 0) AS Consultation,
    IFNULL(GROUP_CONCAT(IF(ServiceCode=3, ItemAmount, NULL)), 0) AS Drugs,
    IFNULL(GROUP_CONCAT(IF(ServiceCode=4, ItemAmount, NULL)), 0) AS Misc
  FROM bill
  GROUP BY BillNo
) AS services;

我已经测试了 ServiceCode 而不是 ServiceName，因为我假设两者是相关的.如果它们不相关，只需根据需要更改比较.

I have tested ServiceCode rather than ServiceName, as I have assumed that the two are related. If they are not related, just change the comparison as necessary.

测试数据:

INSERT INTO bill (BillNo, ServiceCode, ServiceName, ItemAmount) VALUES
(1, 1, 'Lab Test', 30),
(1, 2, 'Consultation', 70),
(2, 1, 'Lab Test', 40),
(2, 2, 'Consultation', 20),
(2, 3, 'Drugs', 15),
(2, 4, 'Misc', 25);

结果:

+--------+---------+--------------+-------+------+------------+
| BillNo | LabTest | Consultation | Drugs | Misc | BillAmount |
+--------+---------+--------------+-------+------+------------+
|      1 | 30      | 70           | 0     | 0    |        100 |
|      2 | 40      | 20           | 15    | 25   |        100 |
+--------+---------+--------------+-------+------+------------+

这篇关于mySQL 中的矩阵/交叉表/枢轴查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！