Il y a un tableau d'achat contenant des informations sur les produits bougt. Cela générera une ou plusieurs lignes dans le tableau tStockMP pour chaque produit individuel acheté.
Maintenant, je dois afficher les informations du tableau pour chaque produit en stock. Puisque la table d'achat contient l'historique des modifications, ces informations sont dans le keyid le plus élevé lorsqu'elles sont regroupées par purchase_id dans la table tPurchases.
J'ai fourni un script complet, ici avec des exemples de données décrivant mon problème.
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 |-> *
-- +-------+-------------+--------+-------+--------+----------+
J'aurais besoin de "filtrer" les résultats pour que SEULES les lignes * restent dans la requête finale, de peur que je doive le faire à la main. Mais je ne sais pas comment modifier ma requête et y parvenir.
3 Réponses :
Si votre base de données prend en charge les fonctions de fenêtre, vous pouvez utiliser ROW_NUMBER () pour identifier le "dernier" enregistrement par groupe, et utiliser ces informations pour filtrer l'ensemble de données:
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
Comment puis-je savoir si ma base de données prend en charge ROW_NUMBER. J'ai deux dbs. Celui dans lequel je développe (mon ordinateur portable personnel) et celui du serveur où cette requête sera utilisée.
@aarelovich: quelle base de données utilisez-vous: mysql, oracle, sql-server ...?
Je viens de vérifier. RDS est configuré avec MySQL 5.7. Et apparemment, c'est une fonctionnalité depuis MySQL 8.0, donc je ne pense pas pouvoir l'utiliser.
N'utilisez jamais de virgule dans la clause FROM . Une solution typique consiste à utiliser une sous-requête corrélée:
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;
Avec un index sur tPurchases (purchase_id, keyid) , cela a souvent les meilleures performances.
Si j'abordais cela avec des fonctions de fenêtre, je le formulerais comme suit:
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
);
GMB a une approche alternative. Si vous avez beaucoup de données, il serait intéressant de comparer les performances des deux méthodes. Je conseillerais le même index que ci-dessus pour toutes les comparaisons.
Je veux savoir pourquoi ne pas utiliser, dans la clause from? Je les utilise tout le temps. Mais je suis très novice dans l'écriture de requêtes, alors j'apprécierais tout aperçu
Je vais aussi avec vos réponses, car je pense que la première question, je comprends le plus!
Commencez par utiliser Voir la démo . NOT EXISTS dans tPurchases pour obtenir uniquement les lignes avec le keyid maximum, puis joignez-vous au tStockMP code >: | 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 |
Résultats: 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
Merci! Je n'ai aucun doute, cela fonctionne et c'est une réponse valable. Cependant, la première option, issue de la question précédente, semble plus simple, spécialement pour un débutant comme moi. Salutations!