所以我让以下查询工作得很好,在"products_desc
"列中搜索"test
*"并在"prices
"表中获取其所有价格。
SELECT products.id, prices.price, products.product_desc FROM products
INNER JOIN prices
ON prices.product_id = products.id
WHERE
MATCH (products.product_desc) AGAINST ('test*' IN BOOLEAN MODE)
尽管"价格"表由每种产品的多个价格组成,但我只想为每个产品获取最低的价格。我之前使用
INNER JOIN (
SELECT min(price) as price, prices.product_number as product_number FROM prices
WHERE prices.product_number LIKE'".$q."%'
GROUP BY prices.product_number
) min_prices
on prices.price = min_prices.price
and prices.product_number = min_prices.product_number
但这是我在prices
表中使用 products_numbers 的时候(现在只有一个 product_id 列。
Products
id | product_desc
----------------------------------------
1 | product1
2 | product2
Prices
id | product_id | price
------------------------------------------------
1 | 1 | 312
2 | 1 | 219
3 | 2 | 312
4 | 2 | 111
此外,该表由 10+ 百万行组成,因此效率非常重要:)
编辑
如果我需要访问 min(price.price) 行上的列值怎么办?
SELECT products.id, MIN(prices.price) as prices_price, prices.id as prices_id, products.product_desc, products.product_number, prices.supplier_id, suppliers.name FROM products
INNER JOIN prices
ON prices.product_id = products.id
INNER JOIN suppliers
ON prices.supplier_id = suppliers.id
WHERE
MATCH (products.product_desc) AGAINST ('test*' IN BOOLEAN MODE)
GROUP BY prices.product_id
上面返回每个产品的最低价格,但其他列中的值也错误?
你可以使用 GROUP BY,使用 MIN() group by 函数。
GROUP BY 用于对列中的值进行分组,并对列执行计算。
在我们的例子中,我们希望按product_id对结果进行分组,因为它在第二个表中重复,并对第二个表的价格列执行计算(min())。
查询如下所示:
SELECT products.id, MIN(prices.price), products.product_desc FROM products
INNER JOIN prices
ON prices.product_id = products.id
WHERE
MATCH (products.product_desc) AGAINST ('test*' IN BOOLEAN MODE)
GROUP BY prices.product_id