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

这篇具有很好参考价值的文章主要介绍了SQL从三个表中根据时间分别查询并汇总数量一行展示。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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

入库主表

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

入库明细表

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

出库主表

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

出库明细表

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

退货主表

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

退货明细表

SQL从三个表中根据时间分别查询并汇总数量一行展示,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 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