MySQL：The used SELECT statements have a different number of columns数据库你我翻滚过的榻榻米味道熟悉-

执行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 执行成功

如您在阅读中发现不足，欢迎留言！！！