MySQL多表关联查询：获取带销售位置的产品记录并排序

我有一个存储产品记录和销售位置数据的数据库，需要返回整合不同表数据的数据集。尝试了以下SQL代码：

SELECT 't1' 
productdate, 
productcode, 
productdescription, 
productqty, 
producttotal 
FROM history_product WHERE productdate='2024/07/02'
UNION ALL
SELECT 't2' 
barcodenumber, 
salelocation 
FROM producttable WHERE barcodenumber='t1.productcode';

收到SQL错误（1222）：使用的SELECT语句列数不一致。

需求是通过history_product表的productcode关联producttable表的salelocation，将其添加到产品记录结果末尾，并最终按salelocation对结果排序。

原history_product表数据 #

目标结果（添加location列） #

排序后结果 #

错误原因及解决方案 #

错误原因

UNION ALL要求前后两个SELECT语句返回的列数完全一致，且对应列的数据类型兼容。你的第一个SELECT返回了6列（'t1' + 5个产品字段），第二个SELECT只返回了3列（'t2' + barcodenumber + salelocation），列数不匹配导致报错。此外，WHERE barcodenumber='t1.productcode'的写法错误，会把t1.productcode当作字符串常量，而非关联字段。

正确SQL代码

要实现关联表并添加位置列，应该用JOIN而非UNION ALL。如果需要保留所有产品记录（即使没有对应位置），用LEFT JOIN；如果只保留有位置的产品，用INNER JOIN：

SELECT 
    hp.productdate,
    hp.productcode,
    hp.productdescription,
    hp.productqty,
    hp.producttotal,
    pt.salelocation AS location
FROM history_product hp
LEFT JOIN producttable pt ON hp.productcode = pt.barcodenumber
WHERE hp.productdate = '2024/07/02'
ORDER BY pt.salelocation;

如果salelocation是字符串类型，排序会按字典序（符合示例结果）；如果是数值类型，会按数值大小排序，可根据实际字段类型调整。

内容的提问来源于stack exchange，提问作者Andrew Gable