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

由 发布于 MySQLsql
 收藏

有一个购买表，其中包含有关产品bougt的信息。这将在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 |-> *
-- +-------+-------------+--------+-------+--------+----------+

我将需要“过滤”结果，以便仅*行保留在最终查询中，以免我需要手工完成。但是我不知道如何修改我的查询并使之成为现实。

评论
满足ni
满足ni

If your database supports window functions, you can use ROW_NUMBER() to identify the "latest" record per group, and use that information to filter the dataset:

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
点赞
评论
木槿花*
木槿花*

Never use commas in the FROM clause. I typical solution is to use a correlated subquery:

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
                );

With an index on tPurchases(purchase_id, keyid), this often has the best performane.

点赞
评论