利用coalsece、ifnull、full join查询结果集案例

最新推荐文章于 2022-10-21 10:17:55 发布

橘子hhh

最新推荐文章于 2022-10-21 10:17:55 发布

阅读量434

点赞数

分类专栏： SQL每日一题文章标签： sql

本文链接：https://blog.csdn.net/qq_31591237/article/details/121243923

版权

SQL每日一题专栏收录该内容

1 篇文章 0 订阅

订阅专栏

题目

有如下三张表T1110A（仓库期初)

T1110B（仓库发出）

T1110C（仓库收入）

要求得到如下结果：

该如何写这个SQL？

测试语句

CREATE TABLE T1110A
(WAREHOUSE VARCHAR(10),
ITEM VARCHAR(10),
QTY INT);

INSERT INTO T1110A VALUES ('A','P001',100);
INSERT INTO T1110A VALUES ('A','P002',200);
INSERT INTO T1110A VALUES ('B','P001',120 );

CREATE TABLE T1110B
(WAREHOUSE VARCHAR(10),
ITEM VARCHAR(10),
QTY INT
);

INSERT INTO T1110B VALUES ('A','P001',50);
INSERT INTO T1110B VALUES ('B','P001',30);

CREATE TABLE T1110C
(WAREHOUSE VARCHAR(10),
ITEM VARCHAR(10),
QTY INT
);

INSERT INTO T1110C VALUES ('A','P001',10);
INSERT INTO T1110C VALUES ('A','P002',20);
INSERT INTO T1110C VALUES ('C','P001',15);
INSERT INTO T1110C VALUES ('C','P003',10);

题解方案，参考资料：

SQL——coalesce函数详解_yilulvxing的博客-CSDN博客 coalsece函数

SQL:mysql中如何使用isnull函数_lch_2016的博客-CSDN博客 isnull、ifnull函数

mysql 全外连接报错的原因_b_bunana的博客-CSDN博客_全连接报错

SQL代码：

方案一：

SELECT
   COALESCE( a.WAREHOUSE, b.WAREHOUSE, c.WAREHOUSE ) AS 仓库,
   COALESCE ( a.ITEM, b.ITEM, c.ITEM ) AS 产品,
   IFNULL( a.QTY, 0 ) AS 初期,
   IFNULL( b.QTY, 0 ) AS 发出,
   IfNULL( c.QTY, 0 ) AS 收入,
   IFNULL( a.QTY, 0 )- IFNULL( b.QTY, 0 )+ IFNULL( c.QTY, 0 ) AS 结存
FROM
   t1110a a
   FULL JOIN ( SELECT WAREHOUSE, ITEM, SUM(QTY) as QTY FROM t1110b GROUP BY WAREHOUSE, ITEM ) AS b
   ON a.ITEM = b.ITEM AND a.WAREHOUSE = b.WAREHOUSE
   FULL JOIN ( SELECT WAREHOUSE, ITEM, SUM(QTY) as QTY FROM t1110c GROUP BY WAREHOUSE, ITEM ) AS c ON IFNULL(a.WAREHOUSE,b.WAREHOUSE) = c.WAREHOUSE
   AND IFNULL(a.ITEM,b.ITEM) = c.ITEM;

方案二：由于我mysql是个人版，无法使用FULL JOIN全表查询，则使用UNION代替

select COALESCE(a.WAREHOUSE,c.WAREHOUSE) 仓库,COALESCE(a.ITEM,c.ITEM) 产品 ,IFNULL(a.QTY,0) 期初,IFNULL(a.QTY2,0) 发出,IFNULL(c.QTY,0) as 收入 from
(select a.WAREHOUSE,a.ITEM,a.QTY,b.QTY as QTY2 from t1110a a LEFT JOIN t1110b b on a.WAREHOUSE=b.WAREHOUSE and a.ITEM=b.ITEM
union
select a.WAREHOUSE,a.ITEM,a.QTY,b.QTY as QTY2 from t1110a a RIGHT JOIN t1110b b on a.WAREHOUSE=b.WAREHOUSE and a.ITEM=b.ITEM)
as a LEFT JOIN t1110c c on a.ITEM = c.ITEM and a.WAREHOUSE=c.WAREHOUSE
union
select COALESCE(a.WAREHOUSE,c.WAREHOUSE) 仓库,COALESCE(a.ITEM,c.ITEM) 产品 ,IFNULL(a.QTY,0) 期初,IFNULL(a.QTY2,0) 发出,IFNULL(c.QTY,0) as 收入 from
(select a.WAREHOUSE,a.ITEM,a.QTY,b.QTY as QTY2 from t1110a a LEFT JOIN t1110b b on a.WAREHOUSE=b.WAREHOUSE and a.ITEM=b.ITEM
union
select a.WAREHOUSE,a.ITEM,a.QTY,b.QTY as QTY2 from t1110a a RIGHT JOIN t1110b b on a.WAREHOUSE=b.WAREHOUSE and a.ITEM=b.ITEM)
as a RIGHT JOIN t1110c c on a.ITEM = c.ITEM and a.WAREHOUSE=c.WAREHOUSE

运行结果（方案二）：