有一个购买表,其中包含有关产品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 |-> *
-- +-------+-------------+--------+-------+--------+----------+
我将需要“过滤”结果,以便仅*行保留在最终查询中,以免我需要手工完成。但是我不知道如何修改我的查询并使之成为现实。
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:Never use commas in the
FROM
clause. I typical solution is to use a correlated subquery:With an index on
tPurchases(purchase_id, keyid)
, this often has the best performane.