执行SQL报错:The used SELECT statements have a different number of columns
以上翻译:使用的SELECT语句具有不同数量的列
原因:我们在 SQL 语句中使用了 UNION 连接两张表时,查询字段数量不一致导致
# 效果展示:
我们需要将数据展示如上图所示
# 错误案例:
SELECT a.quantity AS in_quantity, a.price AS in_price, (a.quantity * a.price) AS in_amount, 0 AS out_quantity, 0 AS out_price, 0 AS out_amount FROM store_in_detail a WHERE a.sku_id = 1345 UNION ALL SELECT b.quantity AS out_quantity, b.price AS out_price, (b.quantity * b.price) AS out_amount FROM store_out_detail b WHERE b.sku_id = 1345
我们通过入库表 连接 出库表,得出商品 id = 1345 的出入库情况
执行SQL报错:The used SELECT statements have a different number of columns (使用的SELECT语句具有不同数量的列)
# 原因分析:
我们在查询入库单,查询了四个字段:入库数量,入库单价,入库金额,出库数量(默认0),出库单价(默认0),出库金额(默认0)
而查询出库单,只查询了两个字段:出库数量,出库单价,出库金额
两次查询的字段数量不一致,导致 SQL 异常
# 正确实例:
SELECT a.quantity AS in_quantity, a.price AS in_price, (a.quantity * a.price) AS in_amount, 0 AS out_quantity, 0 AS out_price, 0 AS out_amount FROM store_in_detail a WHERE a.sku_id = 1345 UNION ALL SELECT 0 AS in_quantity, 0 AS in_price, 0 AS in_amount, b.quantity AS out_quantity, b.price AS out_price, (b.quantity * b.price) AS out_amount FROM store_out_detail b WHERE b.sku_id = 1345
SQL 执行成功