SQL 语句 UNION 连接，查询字段数量必须一致

灰太狼 2024-02-17 09:51 21阅读 0赞

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 连接两张表时，查询字段数量不一致导致

## 效果展示： ##

![在这里插入图片描述][3547f3f9f720440c9f42aa556067f301.png]  
我们需要将数据展示如上图所示

## 错误案例： ##

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

[3547f3f9f720440c9f42aa556067f301.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/7fd34eab8f9144f4862692abdcb977e0.png