矩阵/交叉/枢轴查询mySQL中
问题描述:
我有一个比尔表这样矩阵/交叉/枢轴查询mySQL中
BillNo - 1,-1 ServiceCode,服务名称 - '实验室测试',ItemAmount -30
BillNo -1,ServiceCode - 2,服务名称-'Consultation”,ItemAmount -70
我需要这样的
比尔没有-1,总帐单金额-100,实验室测试-30,咨询-70,药物-0,Misc-输出0
因此,基本上我需要的是BillNo分组的所有ItemAmount的总和。然后在同一行中,我需要将这个帐单金额分配给4个ServiceNames。所以100分配为实验室测试30和咨询70。
这给了我我想要的,但它不是最优的。
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查询?
谢谢。 Chak。
答
试试这个:
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
,正如我认为这两个是相关的。如果它们不相关,则根据需要更改比较。
测试数据:
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 |
+--------+---------+--------------+-------+------+------------+
答
你可以试试这个,虽然我havnt测试它 -
SELECT BillNo,count(ItemAmount) as "Total Bill Amount",
(CASE ServiceName WHEN 'Lab test' THEN count(ServiceName) as "Lab Test"
WHEN 'Consultation' THEN count(ServiceName) as Consultation
WHEN 'Drugs' THEN count(ServiceName) as Drugs
ELSE count(ServiceName) as Misc END CASE)
from Bill group by BillNo;
我想我需要这样的东西老龄化报告的查询 - 如果它是30到60天之间谈到60日列下等等。 – Chakra 2010-07-23 05:29:56
'在30和60之间时,则“60天”为列名 – Sadat 2010-07-23 08:21:37