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

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

入库主表

入库明细表

出库主表

出库明细表

退货主表

退货明细表

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 returnQtyFROM BS_In i left join BS_In_1 i1 on i.ID=i1.MIDwhere CONVERT(VARCHAR(10),InTime,120) >= '2023-07-25'and CONVERT(VARCHAR(10),InTime,120) <='2023-07-31'Group by InTimeUNION ALLselect OutTime as time,0 as inQty,count(*) as outQty,0 as returnQtyfrom 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 OutTimeUNION ALLselect ReturnTime as time,0 as inQty,0 as outQty,count(*) as returnQtyfrom 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

查询结果

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