MySQL:从每个组中检索最后一条记录，并与另一个表的数据组合

Question

有一个采购表，其中包含有关所购买产品的信息。这将在tStockMP表中为每个购买的单个产品生成一行或多行。

现在，我需要显示每个库存产品的表格信息。由于购买表包含更改的历史，因此当按tPurchases表中的purchase_id分组时，该信息处于最高的keyid中。

我已经提供了一个完整的脚本，这里有描述我的问题的示例数据。

DROP TABLE IF EXISTS tPurchases;
DROP TABLE IF EXISTS tStockMP;
-- The purchase table
CREATE TABLE tPurchases (
keyid                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
brand                        VARCHAR(255),
model                        VARCHAR(255),
purchase_id                  INT
);

INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Hp","note1",23);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Lg","IPSLED",45);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Hp","notE1",23);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Bx","BOX",56);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("LG","IPSLED",45);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("HP","NOTE1",23);

-- The Stock MP Table
CREATE TABLE tStockMP (
keyid                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
purchase_id                  INT,
status                       TINYINT
);
INSERT INTO tStockMP (purchase_id,status) VALUES (23,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (23,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (23,0);
INSERT INTO tStockMP (purchase_id,status) VALUES (45,0);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,0);
-- Example data
-- 
-- tPurchases table
-- keyid brand model   purchase_id
-- 0     Hp    note1   23
-- 1     Lg    IPSLED  45
-- 2     Hp    notE1   23
-- 3     Bx    BOX     56
-- 4     LG    IPSLED  45
-- 5     HP    NOTE1   23
-- 
-- 
-- tStockMP   table.
-- purchase_id  status
-- 23           1 
-- 23           1
-- 23           0
-- 45           0
-- 56           1
-- 56           1
-- 56           0
-- 
-- 
-- Expected result
-- 
-- purchase_id  status  brand  model  
-- 23           1       HP     NOTE1   
-- 23           1       HP     NOTE1
-- 23           0       HP     NOTE1
-- 45           0       LG     IPSLED
-- 56           1       Bx     BOX
-- 56           1       Bx     BOX
-- 56           0       Bx     BOX

SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP AS s, tPurchases AS p 
WHERE s.purchase_id = p.purchase_id;
-- +-------+-------------+--------+-------+--------+----------+
-- | keyid | purchase_id | status | brand | model  | purkeyid |
-- +-------+-------------+--------+-------+--------+----------+
-- |     1 |          23 |      1 | Hp    | note1  |        1 |
-- |     1 |          23 |      1 | Hp    | notE1  |        3 |
-- |     1 |          23 |      1 | HP    | NOTE1  |        6 |-> *
-- |     2 |          23 |      1 | Hp    | note1  |        1 |
-- |     2 |          23 |      1 | Hp    | notE1  |        3 |
-- |     2 |          23 |      1 | HP    | NOTE1  |        6 |-> *
-- |     3 |          23 |      0 | Hp    | note1  |        1 |
-- |     3 |          23 |      0 | Hp    | notE1  |        3 |
-- |     3 |          23 |      0 | HP    | NOTE1  |        6 |-> *
-- |     4 |          45 |      0 | Lg    | IPSLED |        2 |
-- |     4 |          45 |      0 | LG    | IPSLED |        5 |-> *
-- |     5 |          56 |      1 | Bx    | BOX    |        4 |-> *
-- |     6 |          56 |      1 | Bx    | BOX    |        4 |-> *
-- |     7 |          56 |      0 | Bx    | BOX    |        4 |-> *
-- +-------+-------------+--------+-------+--------+----------+

我需要"；过滤器"；结果，以便在最终查询中只保留*行，以免我需要手动执行。但我不知道如何修改我的查询并实现这一点。

Answer 1

Never在FROM子句中使用逗号。我的典型解决方案是使用相关的子查询：

SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP s JOIN
tPurchases p 
ON s.purchase_id = p.purchase_id
WHERE p.keyid = (SELECT MAX(p2.keyid)
FROM tPurchases p2
WHERE p2.purchase_id = p.purchase_id
);

对于tPurchases(purchase_id, keyid)上的索引，这通常具有最佳性能。

如果我用窗口函数来处理这个问题，我会把它说成：

SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP s JOIN
(SELECT p.*,
ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY keyid DESC) as seqnum
FROM tPurchases p 
) p
ON s.purchase_id = p.purchase_id
WHERE seqnum = 1;

GMB有另一种方法。如果您有大量的数据，比较这两种方法的性能会很有趣。我建议所有的比较都采用与上述相同的指数。

Answer 2

如果您的数据库支持窗口功能，您可以使用ROW_NUMBER()来识别每个组的"最新"记录，并使用这些信息来过滤数据集：

SELECT *
FROM (
SELECT 
s.keyid, 
s.purchase_id, 
s.status, 
p.brand, 
p.model, 
p.keyid AS purkeyid,
ROW_NUMBER() OVER(PARTITION BY s.keyid ORDER BY p.keyid DESC) rn
FROM tStockMP AS s
INNER JOIN tPurchases AS p ON p.purchase_id = s.purchase_id
) t
WHERE rn = 1

Answer 3

首先在tPurchases中使用NOT EXISTS只获取具有最大keyid的行，然后连接到tStockMP:

SELECT s.keyid, s.purchase_id, s.status, t.brand, t.model, t.keyid AS purkeyid 
FROM tStockMP AS s 
INNER JOIN (
SELECT p.* FROM tPurchases AS p
WHERE NOT EXISTS (
SELECT 1 FROM tPurchases
WHERE purchase_id = p.purchase_id AND keyid > p.keyid
)
) AS t ON t.purchase_id = s.purchase_id

请参阅演示
结果：

| keyid | purchase_id | status | brand | model  | purkeyid |
| ----- | ----------- | ------ | ----- | ------ | -------- |
| 1     | 23          | 1      | HP    | NOTE1  | 6        |
| 2     | 23          | 1      | HP    | NOTE1  | 6        |
| 3     | 23          | 0      | HP    | NOTE1  | 6        |
| 4     | 45          | 0      | LG    | IPSLED | 5        |
| 5     | 56          | 1      | Bx    | BOX    | 4        |
| 6     | 56          | 1      | Bx    | BOX    | 4        |
| 7     | 56          | 0      | Bx    | BOX    | 4        |