获取MYSQL奇异表中每个组中的最后一条记录，其条件忽略某些组

I tried the solution presented here by Mr. Karwin
Retrieving the last record in each group

我尝试了Karwin先生在这里提出的解决方案。检索每组中的最后一条记录

I have a singular table which contains all the records I need with the FF records.

我有一个单数表,其中包含FF记录所需的所有记录。

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   3        |    4       |    4    |  30000     |
 |   2        |    4       |    4    |  10000     |
 |   1        |    4       |    4    |  10000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

The desired output is:

所需的输出是:

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   3        |    4       |    4    |  30000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

What I have tried is this:

我试过的是这样的:

  Select keynum, PaymentID, BuyerId, LatestBill From  Sales where LatestBill != 0 group by PaymentID, BuyerID order by keynum  Desc;

and this

 SELECT max(keynum), PaymentID, BuyerID, NewInstallmentBal
 FROM aliissales.tblmovedactual
 GROUP BY PaymentID, BuyerID DESC;

However, what I get is this:

但是,我得到的是:

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   1        |    4       |    4    |  10000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

What I need is the last record of each group, with each record to be group by PaymentID and BuyerID.

我需要的是每个组的最后一条记录,每条记录由PaymentID和BuyerID组成。

if I use Max(keynum), it gets indeed the max keynum for each group, but not the corresponding records that go with it. is a simpler way to have the records go with the Keynum??

如果我使用Max(keynum),它确实会获得每个组的最大keynum,但不会获得与之相关的相应记录。是一个更简单的方法让记录与Keynum?

Also, if the LatestBill = 0, the entire gorup is now ignored.

此外,如果LatestBill = 0,则现在忽略整个gorup。

ie if

keynum 3 LatestBill = 0, group BuyerID 4 , PaymentID 4 is now ignored.    

2 个解决方案

#1

SELECT  a.*
FROM    aliissales.tblmovedactual a
        INNER JOIN
        (
            SELECT  max(keynum) xx, PaymentID, BuyerID
            FROM    aliissales.tblmovedactual
            GROUP   BY PaymentID, BuyerID
        ) b ON  a.PaymentID = b.PaymentID   AND
                a.BuyerID = b.BuyerID AND
                a.keynum = b.xx

• SQLFiddle Demo

#2

SELECT keynum, PaymentID, BuyerID, NewInstallmentBal FROM aliissales.tblmovedactual WHERE keynum IN ( SELECT max(keynum) FROM aliissales.tblmovedactual GROUP BY PaymentID )

SELECT keynum,PaymentID,BuyerID,NewInstallmentBal FROM aliissales.tblmovedactual WHERE keynum IN(SELECT max(keynum)FROM aliissales.tblmovedactual GROUP BY PaymentID)