获取相同产品id的6张表的最低价格

Question

这是表的结构：

Table1

product_code - product_price

100001 - 100

Table2

product_code - product_price

100001 - 200

Table3

product_code - product_price

100001 - 300

Table4

product_code - product_price

100001 - 400

Table5

product_code - product_price

100001 - 500

Table6

product_code - product_price

100001 - 600

这个查询显然没问题

$price = DB::queryFirstRow("

select * from Table1, Table2, Table3, Table4, Table5, Table6

where Table1.product_code = Table2.product_code AND Table1.product_code = 100001");

根据数据库内的顺序最后一个表的价格

echo $price['product_price']; // 600 the price of the last table according to the order within the db

我尝试按最低价格订购以获得 100 个结果

$price = DB::queryFirstRow("

select * from Table1, Table2, Table3, Table4, Table5, Table6

where Table1.product_code = Table2.product_code AND Table1.product_code = 100001 ORDER BY product_price");

我收到这个错误

// ERROR: Column 'product_price' in order clause is ambiguous

任何如何解决它的想法，非常感谢您的帮助！

Answer 1

首先，当你使用SELECT *mysql时，会加载from和join中所有表的所有字段。MySQL表中的同名字段会互相覆盖，那么mysql将只返回最后一个表的字段（即您查询中的Table6）。如果您执行该查询：SELECT * FROM `Table1` WHERE `product_code` = 100001UNION SELECT * FROM `Table2` WHERE `product_code` = 100001UNION SELECT * FROM `Table3` WHERE `product_code` = 100001UNION SELECT * FROM `Table4` WHERE `product_code` = 100001UNION SELECT * FROM `Table5` WHERE `product_code` = 100001UNION SELECT * FROM `Table5` WHERE `product_code` = 100001Mysql 将返回（不完全按照该顺序）：product_code - product_price100001 - 100100001 - 200100001 - 300100001 - 400100001 - 500100001 - 600如果需要，您可以添加ORDER BY product_price ASC LIMIT 1;或。DESC但仅当您正在学习时才这样做，在实际应用程序中，不建议在不同表的字段中使用相同的名称。

Answer 2

我认为你想使用 UNION 语句来获取所有价格，然后你可以排序并将第一个结果作为最低价格：SELECT `product_price` FROM `Table1` WHERE `product_code` = 100001UNION SELECT `product_price` FROM `Table2` WHERE `product_code` = 100001UNION SELECT `product_price` FROM `Table3` WHERE `product_code` = 100001UNION SELECT `product_price` FROM `Table4` WHERE `product_code` = 100001UNION SELECT `product_price` FROM `Table5` WHERE `product_code` = 100001UNION SELECT `product_price` FROM `Table5` WHERE `product_code` = 100001ORDER BY 1 ASC LIMIT 1;