我有两个表
Products
id, name, last_updated
1, Computer table, 2014-07-20 09:00:00
2, Fruits basket, 2014-07-21 09:00:00
3, Hard Disk, 2014-07-22 09:00:00
Products_prices
id, product_id, min_price, max_price, description
1, 1, 10.00, 20.00, glass top
2, 1, 40.00, 80.00, hard wood
3, 1, 5.00, 10.00, pvc
4, 2, 15.00, 30.00, Mangoes only
5, 3, 30.00, 60.00, 1 TB
6, 3, 45.00, 90.00, 2 TB
7, 3, 20.00, 40.00, 500 GB
我需要检索如下信息
product.id, product.name, product_prices.min_price, product_prices.max_price, price_diff_percentage ( calculated as round((max_price-min_price)/max_price*100) )
如果我做一个左JOIN，例如
SELECT *, round((max_price-min_price)/max_price*100) price_diff_pct
FROM products p LEFT JOIN Product_prices pp on pp.product_id = p.id
然后我得到包含所有产品价格行的产品列表，例如
1, computer table, 10.00, 20.00, 50, glass top
1, computer table, 40.00, 80.00, 50, hard wood
1, computer table, 5.00, 10.00, 50, pvc
2, fruits basket, 15.00, 30.00, mangoes only
3, hard disk, 30.00, 60.00, 50 1 TB
3, hard disk, 45.00, 90.00, 50 2 TB
3, hard disk, 20.00, 40.00, 50 500 GB
我希望它被更改为返回一个具有最低最小价格和price_diff_pct的产品行，例如，而不是上面的返回集，它返回下面的
1, computer table, 5.00, 10.00, 50, pvc
2, fruits basket, 15.00, 30.00, mangoes only
3, hard disk, 20.00, 40.00, 50 500 GB
我需要按last_updated日期订购。
我该怎么做?
更新:create table products (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) NOT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO products (1, 'Computer table', '2014-07-20 09:00:00');
INSERT INTO products (2, 'Fruits basket', '2014-07-21 09:00:00');
INSERT INTO products (3, 'Hard Disk', '2014-07-22 09:00:00');
CREATE TABLE IF NOT EXISTS `product_prices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`min_price` float(10,2) NOT NULL,
`max_price` float(10,2) NOT NULL,
`description` varchar(300) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO product_prices (1, 1, '10.00', '20.00', 'glass top');
INSERT INTO product_prices (2, 1, '40.00', '80.00', 'hard wood');
INSERT INTO product_prices (3, 1, '5.00', '10.00', 'pvc');
INSERT INTO product_prices (4, 2, '15.00', '30.00', 'Mangoes only');
INSERT INTO product_prices (5, 3, '30.00', '60.00', '1 TB');
INSERT INTO product_prices (6, 3, '45.00', '90.00', '2 TB');
INSERT INTO product_prices (7, 3, '20.00', '40.00', '500 GB');
使用子查询获取每个产品的最低min_price，并将其与产品价格连接以获取该产品价格的其余详细信息。
假设min_price对于一个产品是唯一的:-
SELECT p.*, pp.*, ROUND((pp.max_price-pp.min_price)/pp.max_price*100) AS price_diff_pct
FROM products p
LEFT OUTER JOIN
(
SELECT product_id, MIN(min_price) AS min_min_price
FROM Products_Prices
GROUP BY product_id
) sub0
ON sub0.product_id = p.id
LEFT JOIN Product_prices pp
ON pp.product_id = sub0.product_id
AND pp.min_price = sub0.min_min_price
如果你想避免子查询，你可以使用2个LEFT OUTER join。
获取产品，将其连接到产品价格，然后再次连接到第二个产品价格小于第一个产品价格的产品价格，然后使用where子句排除找到第二个价格的任何地方(即检查第二个价格为空)。
SELECT p.*, pp1.*, ROUND((pp1.max_price-pp1.min_price)/pp1.max_price*100) AS price_diff_pct
FROM products p
LEFT OUTER JOIN Products_Prices pp1
ON pp1.product_id = p.id
LEFT OUTER JOIN Products_Prices pp2
ON pp2.product_id = pp1.product_id
AND pp2.min_price < pp1.min_price
WHERE pp2.product_id IS NULL
您可以为表Products_prices使用另一个字段(is_active)。当您插入新行时，然后使is_active = 'N'，您可以相应地更改连接查询