mysql查询中的几个max

0md85ypi 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(246)

我遇到了一个我没能解决的问题。我有下表：

TABLE Sales (
  ClientCode varchar(20) NOT NULL,
  DocumentDate date NOT NULL,
  ItemCode varchar(20) DEFAULT NULL,
  ItemName varchar(500) DEFAULT NULL,
  Quantity decimal(10,2) DEFAULT NULL,
  Price decimal(12,2) DEFAULT NULL,
  InvoiceType varchar(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

所有的销售额都记录在这个表中。
有3种类型的发票（invoicetype）。我需要一个能给出如下结果的查询

ClientCode, ItemCode, ItemName, LastDateofSaleforInvoiceA, LastSalePriceforInvoiceA, LastDateofSaleforInvoiceB, LastSalePriceforInvoiceB, LastDateofSaleforInvoiceC, LastSalePriceforInvoiceC

我不知道我是否解释清楚这一点，但我需要一行，其中有客户代码，项目代码，项目名称和最后日期和最后价格为每一个发票类型，我们有。
源数据如下：

+------------+--------------+----------+----------+----------+-------+-------------+

| ClientCode | DocumentDate | ItemCode | ItemName | Quantity | Price | InvoiceType |

+------------+--------------+----------+----------+----------+-------+-------------+

|      00001 | 2018-10-01   |    00001 | WidgetA  |      500 | 5.00  | Internal    |

|      00002 | 2018-09-27   |    00005 | WidgetB  |      100 | 1.50  | External    |

|      00001 | 2017-09-23   |    00001 | WidgetA  |      150 | 2.25  | External    |

|      00002 | 2016-03-03   |    00005 | WidgetB  |      360 | 5.99  | Internal    |

|      00001 | 2013-03-03   |    00001 | WidgetA  |      600 | 0.99  | Export      |    
+------------+--------------+----------+----------+----------+-------+-------------+

我需要的是这样的东西：

+------------+----------+------------------+---------------+------------------+---------------+----------------+-------------+
| ClientCode | ItemCode | LastDateInternal | PriceInternal | LastDateExternal | PriceExternal | LastDateExport | PriceExport |
+------------+----------+------------------+---------------+------------------+---------------+----------------+-------------+
|      00001 |    00001 | 2018-10-01       | 5.00          | 2017-09-23       | 2.25          | 2013-03-03     | 0.99        |
|      00002 |    00005 | 2016-03-03       | 5.99          | 2018-09-27       | 1.50          |                |             |
+------------+----------+------------------+---------------+------------------+---------------+----------------+-------------+

感谢所有的帮助。

mysql

来源：https://stackoverflow.com/questions/52463260/mysql-several-max-in-query

2条答案

按热度按时间

dgtucam11#

我假设documentdate是datetime并且实际上是唯一的（一次一个销售），我还假设itemcode和itemname之间的关系是1:1
需要一个嵌套查询来获取最后一个日期，需要一个连接来获取相应的价格

SELECT
    last.ClientCode, last.ItemCode, last.ItemName, last.InvoiceType, MAX(If(InvoiceType = 'A', s.LastDate, 0)) as LastDateofSaleforInvoiceA, s.Price AS  LastSalePriceforInvoiceA
    # The same for B and C
    (
    SELECT
    ClientCode, ItemCode, ItemName, InvoiceType, Price, MAX(DocumentDate) as LastDate
    FROM
     sales
    GROUP BY
    ClientCode, ItemCode, InvoiceType
    ) last
    INNER JOIN sales s ON last.ClientCode = s.ClientCode AND last.ItemCode = s.ItemCode AND last.InvoiceType = s.InvoiceType AND last.LastDate = s.DocumentDate

GROUP BY
last.ClientCode, last.ItemCode, last.InvoiceType

赞(0）回复(0）举报 2021-06-18

3ks5zfa02#

您可以使用子查询来查找按客户和invocetype分组的max（documentdate），然后您可以将此结果与sales关联，每次3次，用于不同的invocetype

select a.ClientCode
    , a.ItemCode
    , a.ItemName
    , a.DocumentDate as LastDateofSaleforInvoiceA
    , a.Price as LastSalePriceforInvoiceA
    , b.DocumentDate as LastDateofSaleforInvoiceB
    , b.Price as  LastSalePriceforInvoiceB
    , b.DocumentDate as LastDateofSaleforInvoiceC
    , b.PriceC as LastSalePriceforInvoiceC 
from (
    select max(DocumentDate) max_date, InvoiceType, ClientCode
    from Sales 
    group by InvoiceType, cliendtCode
) t 
inner join sales a on t.ClientCode = a.ClientCode
    AND t.max_date = a.DocumentDate
        AND t.InvoiceType = a.InvoceType
inner join sales b on t.ClientCode = a.ClientCode
    AND t.max_date = b.DocumentDate
        AND t.InvoiceType = b.InvoceType
inner join sales c on t.ClientCode = a.ClientCode
    AND t.max_date = c.DocumentDate
        AND t.InvoiceType = c.InvoceType

赞(0）回复(0）举报 2021-06-18

我来回答

mysql查询中的几个max

2条答案

相关问题

热门标签

最新问答