mysql - SELECT SQL 语句中的多个过滤器

Question

我正在编写一个 SQL 语句来获取 Recordset 中的一些值，我将使用它来将结果传输到 Excel 中窗体上的文本框中。涉及的表有:

客户 -> CustomerId、FirstName、LastName、TelNumber

发票 -> InvoiceId、CustomerId、CarModel、CarColor、CarPlate

维修 -> RepairId、InvoiceId、TypeOfRepair、PartOfCar、价格

服务 -> ServiceId、InvoiceId、日期、状态

当客户来到车库时，会创建一张与该客户关联的发票。一张发票可以有很多维修。客户不修车就走，发票却在。如果客户决定修理汽车，则会创建一个服务，该服务的状态为“working on it...”。服务完成后，状态更改为“等待结帐...”

我想使用 SQL 语句检索特定 InvoiceId 的以下值(列):

CarModel、Color、Plate、CustomerName (FirstName LastName)、PaintingTotalValue(其中“Painting”是“Type”列中的一种类型)、OtherTotalValue(此发票中所有其他类型维修的总价)、总价(总价，即绘画+其他)。

我写了以下代码来获取值，但我不知道如何获取 PaintingTotalValue 和 OtherTotalVAlue。

SELECT i.CarModel, i.Color, i.Plate, CONCAT(c.FirstName,' ',c.LastName) AS Name, FORMAT(SUM(r.Price),2) AS TotalPrice 
FROM Services AS s INNER JOIN Invoices AS i ON s.invoiceId=i.invoiceId
INNER JOIN Repairs AS r ON s.invoiceId=r.invoiceId 
INNER JOIN Customers AS c ON i.customerId=c.customerId
WHERE s.invoiceId = 15

Answer 1

在 SELECT 子句中使用 CASE WHEN 来选择以类型为条件的值:

SELECT
   ...
   CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice,
   CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice,
FROM ...

这是一回事。

另一件事是您没有从 Services 表中选择任何内容，这使您的查询比需要的复杂得多。

If you can modify the schema, remove the ServiceId primary key field, and use Services.InvoiceId as a primary key instead: that will enforce the 1:1 relationship naturally.

FROM Repairs r
INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
INNER JOIN Customers c ON i.CustomerId = c.CustomerId

您要聚合的数据粒度为 Repairs，因此您选择 FROM，然后通过外键向上移动到 Customers.

SELECT
    i.CarModel
   ,i.Color
   ,i.Plate
   ,CONCAT(c.FirstName,' ',c.LastName) Name
   ,CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice
   ,CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice
   ,r.Price
FROM Repairs r
INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
INNER JOIN Customers c ON i.CustomerId = c.CustomerId

这还没有汇总:每个有发票的客户下的每一次维修、每张发票都有一条记录。那部分是子查询。如果您有参数，那就是您使用它的地方。

WHERE i.InvoiceId = pInvoiceId

如果您只是对 ID 进行硬编码，也可以这样做。

现在在 WHERE 子句下键入 SELECT q.* FROM ( 和 ) q，然后替换 q.* 使用您不聚合的字段 - 并聚合其他字段。结果应该是这样的:

SELECT
     q.CarModel
    ,q.Color
    ,q.Plate
    ,q.Name
    ,SUM(q.PaintWorkPrice) PaintAmount
    ,SUM(q.OtherWorkPrice) OtherAmount
    ,SUM(q.Price) TotalAmount
FROM (
    SELECT
        i.CarModel
       ,i.Color
       ,i.Plate
       ,CONCAT(c.FirstName,' ',c.LastName) Name
       ,CASE WHEN r.Type = 'Painting' THEN r.Price ELSE 0 END PaintWorkPrice
       ,CASE WHEN r.Type <> 'Painting' THEN r.Price ELSE 0 END OtherWorkPrice
       ,r.Price
    FROM Repairs r
    INNER JOIN Invoices i ON r.InvoiceId = i.InvoiceId
    INNER JOIN Customers c ON i.CustomerId = c.CustomerId
    WHERE i.InvoiceId = 15
) q
GROUP BY
     q.CarModel
    ,q.Color
    ,q.Plate
    ,q.Name