SQL报表查询案例-优快云博客

本文链接：https://blog.youkuaiyun.com/sz_bdqn/article/details/7228492

本文提供了一个复杂的SQL查询案例，展示了如何从多个表中提取并整合数据以生成详细的报表。涉及的内容包括联表查询、条件筛选、日期范围过滤及字段转换等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

只是感觉这样写法、排版挺过瘾，在这记录下。

select '汇报单号' = t2.FBillNo,
       '输入人员' = (select FName from t_User where FUserID = t2.FBillerID),
       '审核人员' = (select FName from t_User where FUserID = t2.FCheckerID),
       '汇报车间' = (select FName from t_Department where FItemID = t2.FWorkShop),
       '实际开工时间' = convert(varchar(10),t1.FWorkStartDate,120),
       '实际完工时间' = convert(varchar(10),t1.FWorkEndDate,120),
       '本次实作数量' = t1.FQtyFinish,
       '本次遗失数量' = t1.FQtyLoss,
       '本次合格数量' = t1.FQtyPass,
       '本次因工报废数' = t1.FQtyScrap,
       '本次因料报废数' = t1.FQtyForItem,
       '工单号' = t1.FSourceBillNo,
       '物料号码' = t3.FNumber,
       '物料名称' = t3.FName,
       '规格型号' = t3.FModel,
       '工单数量' = t4.FQty,
       '单位' = ( select FName from t_MeasureUnit where FMeasureUnitID = t4.FUnitID),
       '批次' = t4.FGMPBatchNo,
       '工单状态' = (case t4.FStatus when 0 then '计划' when 3 then '结案' when 5 then '确认'  else '下达' end),
       '派工车间' = (select FName from t_Department where FItemID = t4.FWorkShop),
       '计划开工时间' = convert(varchar(10),t4.FPlanCommitDate,120),
       '计划完工时间' = convert(varchar(10),t4.FPlanFinishDate,120),
       '累计实做数' = t4.FQtyFinish,
       '累计遗失数' = t4.FQtyLost,
       '累计因工报废数' = t4.FQtyScrap,
       '累计因料报废数' = t4.FQtyForItem,
       '累计合格数' = t4.FQtyPass,
       '累计入库数' = t4.FStockQty 
from IcmoRptEntry t1, IcmoRpt t2 ,t_ICItem t3,Icmo t4 
Where 
     t2.FCheckDate between '********'  and  '########' 
     and (t1.FSourceBillNo like '%@FSourceBillNo@%' or isnull('@FSourceBillNo@','')='') 
     and (t2.FWorkShop in (select FItemID from t_Department where FName like '%@DeptName@%')  or isnull('@DeptName@','') = '')
     and t1.FInterID = t2.FInterID 
     and t1.FSourceInterID=t4.FInterId
     and t3.FItemID = t4.FItemID
     and t1.FSourcetrantype=85 
     and t4.FTrantype=85  
     and t2.FStatus > 0
order by t2.FBillNo

一个SQL语句