mysql - SQL - MAX 值不符合适当的值

Question

我需要根据最后的发票数据为每个用户提取数据。这是数据:

CREATE TABLE bite
    (`ACCOUNT` int, `ISSUEDATE` datetime, `REFERENCE` int, 
     `ID` int, `AMOUNT` decimal(10,2))
;

INSERT INTO bite
    (`ACCOUNT`, `ISSUEDATE`, `REFERENCE`, `ID`, `AMOUNT`)
VALUES
    (2947471, '2012-12-31 00:00:00', 0005632765, 11543487, 40.18),
    (2947471, '2016-12-30 00:00:00', 0017945914, 36672073, 34.75),
    (2947471, '2012-11-30 00:00:00', 0005448824, 11001690, 33.69),
    (2947471, '2013-03-31 00:00:00', 0006316596, 12759258, 33.44),
    (2956987, '2015-07-01 00:00:00', 0012754607, 26035486, 26.79),
    (2956987, '2015-12-30 00:00:00', 0014371958, 29254616, 25.14),
    (2947471, '2014-05-31 00:00:00', 0009478470, 19223669, 23.34),
    (2947471, '2013-03-02 00:00:00', 0006177725, 12352924, 21.17),
    (2956987, '2014-05-31 00:00:00', 0009504785, 19223632, 20.24),
    (2947471, '2014-01-31 00:00:00', 0008753563, 17845265, 19.97),
    (2947471, '2013-06-30 00:00:00', 0007174573, 14523560, 19.28),
    (2947471, '2014-06-30 00:00:00', 0010048671, 20171164, 19.22),
    (2947471, '2015-01-31 00:00:00', 0011793537, 23926223, 18.98),
    (2947471, '2014-03-31 00:00:00', 0009215609, 18762696, 18.26),
    (2947471, '2013-11-30 00:00:00', 0008394094, 16600223, 18.14),
    (2947471, '2013-04-30 00:00:00', 0006818798, 13568161, 18.08),
    (2956987, '2015-06-30 00:00:00', 0013061086, 26579781, 18.03),
    (2956987, '2014-10-31 00:00:00', 0010942060, 22376155, 18),
    (2947471, '2013-12-31 00:00:00', 0008440468, 17395187, 18),
    (2947471, '2013-10-31 00:00:00', 0007958174, 16156026, 17.94),
    (2947471, '2013-08-31 00:00:00', 0007585482, 15274737, 17.79),
    (2947471, '2012-11-30 00:00:00', 0005632765, 11543487, 40.18),
    (2947471, '2016-11-30 00:00:00', 0017945914, 36672073, 34.75),
    (2947471, '2012-10-31 00:00:00', 0005448824, 11001690, 33.69),
    (2947471, '2013-02-28 00:00:00', 0006316596, 12759258, 33.44),
    (2956987, '2015-05-31 00:00:00', 0012754607, 26035486, 26.79),
    (2956987, '2015-11-30 00:00:00', 0014371958, 29254616, 25.14)
;

这是查询:

select account, max(issuedate) as "LAST INVOICE DATE",
  reference,amount,round(avg(amount),2) as "AVERAGE AMOUNT" 
from bite 
group by account

(也可以从这里的 fiddle 中获得:http://sqlfiddle.com/#!9/5280b7/58/0)

您会看到我拥有与一切相关的一切，可以这么说，但没有最大日期，反之亦然。我需要列中的每条记录都与最大日期值相关。请告诉我我做错了什么。

Answer 1

希望代码能帮到你。

with withbite
as(
select row_number()over(partition by account order by issuedate desc) as line,
        account, 
       issuedate,
       reference,
       amount
from bite 
)
select account, 
       issuedate as 'LAST INVOICE DATE',
       reference,
       amount,
       round(avg(amount),2) as 'AVERAGE AMOUNT'
from withbite
where line=1
group by account, 
       issuedate,
       reference,
       amount