获取特定id的正确数据
获取特定id的正确数据
提问于 2018-05-31 18:05:30
我想知道每种产品在特定地点的价格。以下是我到目前为止所做的工作。
SELECT pl.id AS place_id,
pl.data_name AS place_name,
pp.data_price AS product_price,
pp.date_updated AS price_updated
FROM places AS pl
JOIN products AS pr
ON pl.id = pr.id_place
JOIN products_prices AS pp
WHERE pp.id_product = '30'
GROUP BY pl.id, pp.data_price, pp.date_updated
ORDER BY pp.data_price DESC, pp.date_updated DESC
从上图可以看出，product_price和price_updated都是一样的。place_name还显示了3个没有id为30的产品的地方。
下面是它应该是什么样子的(最便宜的，最新的产品购买在顶部)：
place_id place_name product_price price_updated
3 ICA Maxi 4.95 2018-05-16
1 ICA Supermarket 5.90 2018-05-27
26 ICA Skutan 6.50 2018-05-29下面是数据库结构：
CREATE TABLE IF NOT EXISTS `places` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_name` tinytext NOT NULL,
`data_address` text,
`data_address_city` tinytext NOT NULL,
`data_coordinates` varchar(50) NOT NULL,
UNIQUE KEY `id` (`id`)
);
INSERT INTO `places` (`id`, `data_name`, `data_address`, `data_address_city`, `data_coordinates`) VALUES
(1, 'ICA Supermarket', 'Björkhagsgatan 9', 'Skoghall', '59.324971,13.467291'),
(2, 'ICA Maxi', 'Bergviks Köpcentrum', 'Karlstad', '59.376563,13.428787'),
(3, 'ICA Kvantum', 'Bivägen 11', 'Hammarö', '59.343388,13.504583'),
(4, 'IKEA', 'Bergviksvägen 43', 'Karlstad', '59.379032,13.420646'),
(5, 'Karlstad Naprapatklinik', 'Västra Torggatan 15', 'Karlstad', '59.381379,13.501683'),
(9, 'Besök i Borgvik AB', '', 'Borgvik', '59.348261,12.954707'),
(23, 'Mariebergsskogen', '', 'Karlstad', '59.369403,13.486485'),
(24, 'Happy Price', 'Brehogsvägen 20', 'Tanumshede', '58.723730,11.344768'),
(25, 'Trekanten Kök & Bar', 'Parkvägen 2', 'Hamburgsund', '58.552733,11.270998'),
(26, 'ICA Skutan', 'Strandvägen', 'Hamburgsund', '58.552122,11.270898');
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_place` int(11) NOT NULL,
`data_barcode` text NOT NULL,
`data_name` text NOT NULL,
`data_weight` text NOT NULL,
`data_weight_type` text NOT NULL,
UNIQUE KEY `id` (`id`)
);
INSERT INTO `products` (`id`, `id_place`, `data_barcode`, `data_name`, `data_weight`, `data_weight_type`) VALUES
(7, 3, '7311070008494', 'Pågen Lantbröd', '650', 'g'),
(8, 3, '7310618084808', 'Grumme citronsåpa', '750', 'ml'),
(9, 3, '7318690079835', 'ICA Basic toalettpapper', '', ''),
(12, 1, '7318690134640', 'ICA Basic Milk & Caramel', '100', 'g'),
(18, 3, '7310380512103', 'ICA Home dishmatic refillsvamp', '', ''),
(19, 3, '7340109200684', 'Plastkasse', '', ''),
(20, 1, '7310751163903', 'ICA tonfisk filébitar i vatten', '185', 'g'),
(24, 1, '7310865001818', 'Arla mellanmjölk', '1.5', 'kg'),
(25, 1, '7318690079712', 'ICA kattsand (klumpbildande, ej parfym)', '6', 'kg'),
(29, 26, '3068320055008', 'Evian mineralvatten', '500', 'mL'),
(30, 26, '7318690134640', 'ICA Basic Milk & Caramel', '100', 'g'),
(33, 2, '0', 'ICA Basic Milk & Caramel', '100', 'g');
CREATE TABLE IF NOT EXISTS `products_prices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_product` int(11) NOT NULL,
`id_transaction` int(11) NOT NULL,
`data_price` decimal(10,2) NOT NULL,
`data_amount` tinytext NOT NULL,
`data_discount` tinytext,
`data_discount_amount` tinytext,
`data_discount_sum` tinytext,
`data_pant` tinytext,
`date_updated` date NOT NULL,
UNIQUE KEY `id` (`id`)
);
INSERT INTO `products_prices` (`id`, `id_product`, `id_transaction`, `data_price`, `data_amount`, `data_discount`, `data_discount_amount`, `data_discount_sum`, `data_pant`, `date_updated`) VALUES
(1, 1, 907, 18.90, '4', '', '2', '30', NULL, '2018-05-18'),
(2, 12, 907, 4.90, '4', '', '', '', NULL, '2018-05-18'),
(5, 4, 904, 18.90, '1', '', '', '', NULL, '2018-05-18'),
(6, 14, 904, 14.90, '1', '', '', '', NULL, '2018-05-18'),
(17, 19, 936, 2.00, '1', '', '', '', NULL, '2018-05-21'),
(18, 21, 947, 23.90, '1', '', '', '', NULL, '2018-05-23'),
(19, 22, 947, 10.50, '1', '', '', '', NULL, '2018-05-23'),
(20, 20, 947, 15.90, '1', '', '', '', NULL, '2018-05-23'),
(21, 13, 947, 49.90, '1', '', '', '', NULL, '2018-05-23'),
(22, 23, 948, 14.90, '1', '', '', '', NULL, '2018-05-24'),
(23, 24, 961, 14.90, '1', NULL, NULL, NULL, NULL, '2018-05-27'),
(24, 12, 961, 5.90, '4', NULL, NULL, NULL, NULL, '2018-05-27'),
(32, 28, 967, 8.50, '2', NULL, NULL, NULL, NULL, '2018-05-28'),
(33, 29, 972, 12.90, '1', NULL, NULL, NULL, '1', '2018-05-29'),
(34, 30, 973, 6.50, '2', NULL, NULL, NULL, NULL, '2018-05-29'),
(35, 31, 976, 10.00, '1', NULL, NULL, NULL, NULL, '2018-05-30'),
(36, 32, 976, 10.00, '1', NULL, NULL, NULL, NULL, '2018-05-30');回答 2
Stack Overflow用户
回答已采纳
发布于 2018-05-31 18:19:00
正如我在对您的请求的评论中所提到的:您忘记了将products_prices表链接到其他两个表的ON子句。
MariaDB直接将你的内连接转换为交叉连接，而不是像它应该的那样引发错误。这给你提供的是:选择产品30的所有价格，并将所有位置乘以其中的产品数量，而不是仅与产品的位置组合。
顺便说一句:我会把表products放在FROM子句的第一位，因为这是基表(您想要的是产品30)。然后把产品的价格和产品的位置联系起来。
没有理由对您的数据进行分组，因为您只想列出价格。您没有聚合任何内容。所以你会得到：
SELECT
pl.id AS place_id,
pl.data_name AS place_name,
pp.data_price AS product_price,
pp.date_updated AS price_updated
FROM products AS pr
JOIN products_prices AS pp ON pp.id_product = pr.id
JOIN places pl ON pl.id = pr.id_place
WHERE pr.id = 30
ORDER BY pp.data_price DESC, pp.date_updated DESC;Stack Overflow用户
发布于 2018-05-31 18:12:04
试试这个：
SELECT PL.id AS place_id,
PL.data_name AS place_name,
PP.data_price AS product_price,
PP.date_updated AS price_updated
FROM products_prices PP
JOIN products PR
ON PR.id = PP.id_product
JOIN places PL
ON PL.id = PR.id_place
WHERE PR.id = '30'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50621716
复制相关文章
相似问题