เทคนิคการหาราคาแบบ Last In First Out (LIFO)

CREATE TABLE `drug_catalog` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_catalog` (`drug_code`, `price`, `date_effect`) VALUES
('Albumin', 15, '2018-01-01'),
('Albumin', 20, '2018-02-15'),
('Albumin', 25, '2019-01-01'),
('Penicillin', 5, '2018-01-02'),
('Penicillin', 6, '2019-01-02'),
('Bromhexin', 11, '2018-02-01'),
('Bromhexin', 13, '2019-03-01'),
('Bromhexin', 15, '2019-04-01'),
('Bupropion', 50, '2019-02-05'),
('Bupropion', 40, '2019-03-06');

CREATE TABLE `drug_order` (
  `drug_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `hn` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `date_effect` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `drug_order` (`drug_code`, `hn`, `date_effect`) VALUES
('Albumin', '2482', '2018-02-15'),
('Albumin', '1526', '2019-01-01'),
('Penicillin', '2482', '2019-04-01'),
('Albumin', '4458', '2020-01-01'),
('Bromhexin', '8697', '2018-05-01'),
('Bupropion', '2482', '2020-01-01');

SELECT drug_code,hn,date_effect,price FROM (
SELECT O.drug_code,O.hn,O.date_effect,G.price,G.date_effect AS stock_date  
FROM drug_order AS O
INNER JOIN drug_catalog AS G ON O.drug_code=G.drug_code AND O.date_effect>=G.date_effect
ORDER BY G.drug_code,G.date_effect DESC
LIMIT 99999999
) AS Q
GROUP BY drug_code,hn