sql server group by 分组跟着查询出对应的详细信息
select
PickOrgId,zzjgfnumber,zzjgfname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bmfnumber,bmfname,ckid,ckfnumber,ckname
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wlfnumber,wlfname,dwfuntid,dwfnumber,dwfname,chlbfnumber,chfname,max(FPRICE)as FPRICE,sum(amonut) as amount,row_number() over(order by wlfnumber) as FIDENTITYID
,(
SELECT '面积分摊' as method, F_TXBE_AREA
FROM (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY fnumber ORDER BY fnumber DESC) AS row_num
FROM T_BD_DEPARTMENT
) t
WHERE row_num = 1 and FNUMBER=bmfnumber
)
from
(
-- 领用组织id 领用组织编码 领用组织名称
select qtch.FPICKORGID as PickOrgId,zzjg.fnumber as zzjgfnumber,zzjgl.fname as zzjgfname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bm.FNUMBER as bmfnumber,bml.FNAME as bmfname,ck.FStockId as ckid,ck.FNUMBER as ckfnumber,ckl.FNAME as ckname
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wl.FMASTERID,wl.FNUMBER as wlfnumber,wll.FNAME as wlfname,dw.FUNITID as dwfuntid,dw.FNUMBER as dwfnumber,dwl.FNAME as dwfname,
chlb.FNUMBER as chlbfnumber,chlbl.FNAME as chfname,sum(isnull(FQty,0)) as FQty
,case when max(qtchmx.FPRICE)>0 then max(qtchmx.FPRICE) else max(wldj.FPRICE) end as FPRICE
,case when max(qtchmx.FPRICE)>0 then sum(isnull(FQty,0))*max(qtchmx.FPRICE) else sum(isnull(FQty,0))*max(wldj.FPRICE)end as amonut
--其他出库单
from T_STK_MISDELIVERY as qtch
left join T_STK_MISDELIVERYENTRY as qtchmx
on qtch.fid=qtchmx.fid
left join T_ORG_Organizations as zzjg
on qtch.FPICKORGID=zzjg.FOrgID
left join T_ORG_Organizations_L as zzjgl
on zzjg.FOrgID=zzjgl.FOrgID
left join T_BD_DEPARTMENT as bm
on qtch.FDeptId=bm.fdeptid
inner join T_BD_DEPARTMENT_L as bml on bm.FDEPTID=bml.FDEPTID
-- 仓库
left join t_BD_Stock as ck
on qtchmx.FStockId=ck.FStockId
left join T_BD_STOCK_L as ckl
on ck.FSTOCKID=ckl.FSTOCKID
-- 物料
left join T_BD_MATERIAL as wl
on qtchmx.FMaterialId=wl.FMATERIALID
left join T_BD_MATERIAL_L as wll
on wl.FMASTERID=wll.FMATERIALID
left join t_BD_MaterialBase wljb
on wl.FMATERIALID=wljb.FMATERIALID
-- 单位
left join T_BD_UNIT as dw
on qtchmx.FUnitID=dw.FUNITID
left join T_BD_UNIT_L as dwl
on dw.FUNITID=dwl.FUNITID
left join T_BD_MATERIALCATEGORY as chlb
on wljb.FCATEGORYID=chlb.FCATEGORYID
left join T_BD_MATERIALCATEGORY_L as chlbl
on chlb.FCATEGORYID=chlbl.FCATEGORYID
left join
(
select FMATERIALID,max(FPRICE) as FPRICE
from
(SELECT FMATERIALID,ckf.FPRICE, ROW_NUMBER() OVER (PARTITION BY FMATERIALID ORDER BY fdate DESC) AS rn
FROM t_STK_InStock as ck
left join
T_STK_INSTOCKENTRY ckmx
on ck.FID=ckmx.FID
left join
T_STK_INSTOCKENTRY_F ckf
on ck.FID=ckf.FID
) as ckdj
where ckdj.rn=1
group by FMATERIALID
)as wldj
on qtchmx.FMATERIALID=wldj.FMATERIALID
where chlbl.FNAME in(
'原材料-肥料'
,'原材料-农药'
,'原材料-五金辅料'
,'原材料-保温材料'
,'原材料-包材'
)and qtch.FPICKORGID IN (105887, 105891, 105900, 105901, 105902, 105903, 105904, 105906, 105908, 105909, 105910, 305795, 621474, 702911)
and qtch.FDOCUMENTSTATUS='C'
and qtch.FDate>='2024-06-01' and qtch.FDate<='2024-07-16' and zzjgl.FLOCALEID=2052 and bml.FLOCALEID=2052 and ckl.FLOCALEID=2052 and wll.FLOCALEID=2052
and dwl.FLOCALEID=2052 and chlbl.FLOCALEID=2052
group by qtch.FPICKORGID,zzjg.fnumber ,zzjgl.fname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bm.FNUMBER ,bml.FNAME ,ck.FStockId ,ck.FNUMBER ,ckl.FNAME
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wl.FMASTERID,wl.FNUMBER ,wll.FNAME ,dw.FUNITID,dw.FNUMBER ,dwl.FNAME,
chlb.FNUMBER ,chlbl.FNAME
union all
-- 发料组织id 发料组织编码 发料组织名称
select qtch.FPRDORGID as PickOrgId,zzjg.fnumber as zzjgfnumber,zzjgl.fname as zzjgfname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bm.FNUMBER as bmfnumber,bml.FNAME as bmfname,ck.FStockId as ckid,ck.FNUMBER as ckfnumber,ckl.FNAME as ckname
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wl.FMASTERID,wl.FNUMBER as wlfnumber,wll.FNAME as wlfname,dw.FUNITID as dwfuntid,dw.FNUMBER as dwfnumber,dwl.FNAME as dwfname,
chlb.FNUMBER as chlbfnumber,chlbl.FNAME as chfname,sum(isnull(FACTUALQTY,0)) as FQty
,case when max(qtchmx.FPRICE)>0 then max(qtchmx.FPRICE) else max(wldj.FPRICE) end as FPRICE
,case when max(qtchmx.FPRICE)>0 then sum(isnull(FACTUALQTY,0))*max(qtchmx.FPRICE) else sum(isnull(FACTUALQTY,0))*max(wldj.FPRICE)end as amonut
--简单生产领料单
from T_SP_PICKMTRL as qtch
left join T_SP_PICKMTRLDATA as qtchmx
on qtch.fid=qtchmx.fid
left join T_ORG_Organizations as zzjg
on qtch.FPRDORGID=zzjg.FOrgID
left join T_ORG_Organizations_L as zzjgl
on zzjg.FOrgID=zzjgl.FOrgID
left join T_BD_DEPARTMENT as bm
on qtch.FWORKSHOPID=bm.fdeptid
inner join T_BD_DEPARTMENT_L as bml on bm.FDEPTID=bml.FDEPTID
-- 仓库
left join t_BD_Stock as ck
on qtchmx.FStockId=ck.FStockId
left join T_BD_STOCK_L as ckl
on ck.FSTOCKID=ckl.FSTOCKID
-- 物料
left join T_BD_MATERIAL as wl
on qtchmx.FMaterialId=wl.FMATERIALID
left join T_BD_MATERIAL_L as wll
on wl.FMASTERID=wll.FMATERIALID
left join t_BD_MaterialBase wljb
on wl.FMATERIALID=wljb.FMATERIALID
-- 单位
left join T_BD_UNIT as dw
on qtchmx.FUnitID=dw.FUNITID
left join T_BD_UNIT_L as dwl
on dw.FUNITID=dwl.FUNITID
left join T_BD_MATERIALCATEGORY as chlb
on wljb.FCATEGORYID=chlb.FCATEGORYID
left join T_BD_MATERIALCATEGORY_L as chlbl
on chlb.FCATEGORYID=chlbl.FCATEGORYID
left join
(
select FMATERIALID,max(FPRICE) as FPRICE
from
(SELECT FMATERIALID,ckf.FPRICE, ROW_NUMBER() OVER (PARTITION BY FMATERIALID ORDER BY fdate DESC) AS rn
FROM t_STK_InStock as ck
left join
T_STK_INSTOCKENTRY ckmx
on ck.FID=ckmx.FID
left join
T_STK_INSTOCKENTRY_F ckf
on ck.FID=ckf.FID
) as ckdj
where ckdj.rn=1
group by FMATERIALID
)as wldj
on qtchmx.FMATERIALID=wldj.FMATERIALID
where chlbl.FNAME in(
'原材料-包材'
)and qtch.FWORKSHOPID IN (105887, 105891, 105900, 105901, 105902, 105903, 105904, 105906, 105908, 105909, 105910, 305795, 621474, 702911)
and qtch.FDOCUMENTSTATUS='C'
and qtch.FDate>='2024-06-01' and qtch.FDate<='2024-07-16' and zzjgl.FLOCALEID=2052 and bml.FLOCALEID=2052 and ckl.FLOCALEID=2052 and wll.FLOCALEID=2052
and dwl.FLOCALEID=2052 and chlbl.FLOCALEID=2052
group by qtch.FPRDORGID,zzjg.fnumber ,zzjgl.fname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bm.FNUMBER ,bml.FNAME ,ck.FStockId ,ck.FNUMBER ,ckl.FNAME
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wl.FMASTERID,wl.FNUMBER ,wll.FNAME ,dw.FUNITID,dw.FNUMBER ,dwl.FNAME,
chlb.FNUMBER ,chlbl.FNAME ) as total
where 1=1
group by PickOrgId,zzjgfnumber,zzjgfname
-- 部门编码 部门名称 仓库id 仓库编码 仓库名称
,bmfnumber,bmfname,ckid,ckfnumber,ckname
--物料id 物料编码 物料名称 单位id 单位编码 单位名称
,wlfnumber,wlfname,dwfuntid,dwfnumber,dwfname,chlbfnumber,chfname
