SQL从三个表中根据时间分别查询并汇总数量一行展示

叁歲伎倆 2024-03-22 23:39 34阅读 0赞

> 需求：如果您要从三个表中根据时间分别查询并汇总数量，然后将结果以时间和数量一行展示，可以使用子查询和条件聚合。

## 入库主表 ##

![在这里插入图片描述][5f4ae2de5a344b68889df6e9cddeefe9.png]

## 入库明细表 ##

![在这里插入图片描述][82b51d57f1404594a1d168f2ced2f9a1.png]

## 出库主表 ##

![在这里插入图片描述][be6d9fdfecac41c09de7242c331d54fa.png]

## 出库明细表 ##

![在这里插入图片描述][aa651c44dafd4ffd9bb2b26a332afa61.png]

## 退货主表 ##

![在这里插入图片描述][26999635b0914919b22e1b93087842ba.png]

## 退货明细表 ##

![在这里插入图片描述][120cda12186343fab6126b8e02665587.png]

## SQL代码 ##

SELECT time,sum(a.inQty) as inQty,sum(a.outQty) as outQty,sum(a.returnQty) as returnQty
    FROM (
        SELECT InTime as time,count(*) as inQty,0 as outQty,0 as returnQty
        FROM BS_In i left join BS_In_1 i1 on i.ID=i1.MID
       	where CONVERT(VARCHAR(10),InTime,120) >= '2023-07-25'
                 and CONVERT(VARCHAR(10),InTime,120) <='2023-07-31'
    			 Group by InTime
        UNION ALL
        select OutTime as time,0 as inQty,count(*) as outQty,0 as returnQty
            from BS_Out o left join BS_Out_1 o1 on o.ID=o1.MID 
    			where CONVERT(VARCHAR(10),OutTime,120) >= '2023-07-25'
                 and CONVERT(VARCHAR(10),OutTime,120) <='2023-07-31'
    			  Group by OutTime
        UNION ALL
         select ReturnTime as time,0 as inQty,0 as outQty,count(*) as returnQty
            from BS_Return r left join BS_Return_1 r1 on r.ID=r1.MID  
    				where CONVERT(VARCHAR(10),ReturnTime,120) >= '2023-07-25'
                 and CONVERT(VARCHAR(10),ReturnTime,120) <='2023-07-31'
    			   Group by ReturnTime
    ) AS a
    GROUP BY time

## 查询结果 ##

![在这里插入图片描述][da2f53627e2b453f9b576a7d7bace54a.png]

> 可以看到2023-07-31日，入库9个，出库0个，退货0个。

[5f4ae2de5a344b68889df6e9cddeefe9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/961b527eaf014de1acb8fc57feafd6dc.png
[82b51d57f1404594a1d168f2ced2f9a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b508ebba2a154d1fac112297911b1386.png
[be6d9fdfecac41c09de7242c331d54fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/4112d276b1b146b58990b8378118c944.png
[aa651c44dafd4ffd9bb2b26a332afa61.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/0e05b3b5b89e4dc7b7757763e3184c8a.png
[26999635b0914919b22e1b93087842ba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ce37b1e57194445595ad18d9fa2bb581.png
[120cda12186343fab6126b8e02665587.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/011910b68b134c94be0ba4f4439318b4.png
[da2f53627e2b453f9b576a7d7bace54a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a088a028c8944ff188152c6a7bb98261.png