declare @T_ServerInOut table ([IONUM] nvarchar(40),[CRFLAG] int) Insert into @T_ServerInOut select 200805261457241718,1 union all select 200805261457340937,1 union all select 200805261458295781,1 union all select 200805261649365156,0 union all select 200805261650293906,0 union all select 200805261651421250,0 union all select 200805261440492968,0 union all select 200805261441001250,0 union all select 200805261443347968,0 union all select 200805261444438593,1 union all select 200805261445551562,1
declare @T_ServerInOutDetail table ([IONUM] nvarchar(40),PID int,MC nvarchar(20),SL nvarchar(20),QCSL int,QMSL int) Insert into @T_ServerInOutDetail select 200805261440492968,103500008,'塑料钉','142',50,192 union all select 200805261441001250,103500008,'塑料钉','192',50,242 union all select 200805261443347968,103500008,'塑料钉','242',-50,192 union all select 200805261444438593,103500008,'塑料钉','0',-50,-50 union all select 200805261445551562,103500008,'塑料钉','-50',10,-40 union all select 200805261445551562,103500008,'塑料钉','-40',-2,-42 union all select 200805261457241718,103028003,'装饰盖(与30-4通用)',0,-2,-2 union all select 200805261457340937,103028003,'装饰盖(与30-4通用)',-2,-2,-4 union all select 200805261458295781,103028003,'装饰盖(与30-4通用)',-4,4,0 union all select 200805261649365156,105002018,'面壳',9,20,29 union all select 200805261650293906,105002018,'面壳',29,10,39 union all select 200805261651421250,105002018,'面壳',39,-30,9
declare @T_PartMonthAccount table ([PID] int,[SL] decimal(18,1)) Insert into @T_PartMonthAccount select 105002018,4.0 union all select 105002018,4.0 union all select 103028003,23.0 union all select 103028003,23.0 union all select 103500008,233.0 union all select 103500008,233.0
declare @T_inputdypt table ([id] int,[inputvalue] nvarchar(4)) Insert into @T_inputdypt select 0,'新配件库' union all select 1,'退货兑换'--Select * from @T_ServerInOut --Select * from @T_ServerInOutDetail --Select * from @T_PartMonthAccount --Select * from @T_inputdyptselect d.PID,d.MC, sum(case when ((s.[CRFLAG] = 0) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL, --d.QCSL, sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL2 ,--d.QCSL, max(d.QCSL) as QCSL, max(p.SL) as SL, max(d.QMSL) as QMSL from @T_ServerInOutDetail d left join @T_ServerInOut s on s.[IONUM] = d.[IONUM] left join @T_PartMonthAccount p on p.[PID] = d.[PID] left join @T_inputdypt t on t.id = s.[CRFLAG] group by d.PID,d.MC /* PID MC QCSL QCSL2 QCSL SL QMSL ----------- -------------------- ----------- ----------- ----------- --------------------------------------- ----------- 105002018 面壳 0 0 20 4.0 39 103500008 塑料钉 100 -84 50 233.0 242 103028003 装饰盖(与30-4通用) 0 0 4 23.0 0(3 row(s) affected) */
改了下QMSL, select d.PID,d.MC, sum(case when ((s.[CRFLAG] = 0) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL, --d.QCSL, sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL2 ,--d.QCSL, max(p.SL) as SL, sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QMSL else 0 end) as QMSL from @T_ServerInOutDetail d left join @T_ServerInOut s on s.[IONUM] = d.[IONUM] left join @T_PartMonthAccount p on p.[PID] = d.[PID] left join @T_inputdypt t on t.id = s.[CRFLAG] group by d.PID,d.MC /* PID MC QCSL QCSL2 SL QMSL ----------- -------------------- ----------- ----------- --------------------------------------- ----------- 105002018 面壳 0 0 4.0 0 103500008 塑料钉 100 -84 233.0 -264 103028003 装饰盖(与30-4通用) 0 0 23.0 -12(3 row(s) affected) */
这句话的意思就是说,在T_ServerInOut.CRFLG这个字段,有0和1,而T_inputdypt.ID也有0和1,可以这么理解,T_inputdypt.ID是主键,而T_ServerInOut.CRFLG是外键
--> 测试数据
declare @T_ServerInOut table ([IONUM] nvarchar(40),[CRFLAG] int)
Insert into @T_ServerInOut
select 200805261457241718,1 union all
select 200805261457340937,1 union all
select 200805261458295781,1 union all
select 200805261649365156,0 union all
select 200805261650293906,0 union all
select 200805261651421250,0 union all
select 200805261440492968,0 union all
select 200805261441001250,0 union all
select 200805261443347968,0 union all
select 200805261444438593,1 union all
select 200805261445551562,1
declare @T_ServerInOutDetail table ([IONUM] nvarchar(40),PID int,MC nvarchar(20),SL nvarchar(20),QCSL int,QMSL int)
Insert into @T_ServerInOutDetail
select 200805261440492968,103500008,'塑料钉','142',50,192 union all
select 200805261441001250,103500008,'塑料钉','192',50,242 union all
select 200805261443347968,103500008,'塑料钉','242',-50,192 union all
select 200805261444438593,103500008,'塑料钉','0',-50,-50 union all
select 200805261445551562,103500008,'塑料钉','-50',10,-40 union all
select 200805261445551562,103500008,'塑料钉','-40',-2,-42 union all
select 200805261457241718,103028003,'装饰盖(与30-4通用)',0,-2,-2 union all
select 200805261457340937,103028003,'装饰盖(与30-4通用)',-2,-2,-4 union all
select 200805261458295781,103028003,'装饰盖(与30-4通用)',-4,4,0 union all
select 200805261649365156,105002018,'面壳',9,20,29 union all
select 200805261650293906,105002018,'面壳',29,10,39 union all
select 200805261651421250,105002018,'面壳',39,-30,9
declare @T_PartMonthAccount table ([PID] int,[SL] decimal(18,1))
Insert into @T_PartMonthAccount
select 105002018,4.0 union all
select 105002018,4.0 union all
select 103028003,23.0 union all
select 103028003,23.0 union all
select 103500008,233.0 union all
select 103500008,233.0
declare @T_inputdypt table ([id] int,[inputvalue] nvarchar(4))
Insert into @T_inputdypt
select 0,'新配件库' union all
select 1,'退货兑换'--Select * from @T_ServerInOut
--Select * from @T_ServerInOutDetail
--Select * from @T_PartMonthAccount
--Select * from @T_inputdyptselect
d.PID,d.MC,
sum(case when ((s.[CRFLAG] = 0) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL, --d.QCSL,
sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL2 ,--d.QCSL,
max(d.QCSL) as QCSL,
max(p.SL) as SL,
max(d.QMSL) as QMSL
from @T_ServerInOutDetail d
left join @T_ServerInOut s on s.[IONUM] = d.[IONUM]
left join @T_PartMonthAccount p on p.[PID] = d.[PID]
left join @T_inputdypt t on t.id = s.[CRFLAG]
group by d.PID,d.MC
/*
PID MC QCSL QCSL2 QCSL SL QMSL
----------- -------------------- ----------- ----------- ----------- --------------------------------------- -----------
105002018 面壳 0 0 20 4.0 39
103500008 塑料钉 100 -84 50 233.0 242
103028003 装饰盖(与30-4通用) 0 0 4 23.0 0(3 row(s) affected)
*/
select
d.PID,d.MC,
sum(case when ((s.[CRFLAG] = 0) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL, --d.QCSL,
sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QCSL else 0 end) as QCSL2 ,--d.QCSL,
max(p.SL) as SL,
sum(case when ((s.[CRFLAG] = 1) and (s.[IONUM] = d.[IONUM])) then d.QMSL else 0 end) as QMSL
from @T_ServerInOutDetail d
left join @T_ServerInOut s on s.[IONUM] = d.[IONUM]
left join @T_PartMonthAccount p on p.[PID] = d.[PID]
left join @T_inputdypt t on t.id = s.[CRFLAG]
group by d.PID,d.MC
/*
PID MC QCSL QCSL2 SL QMSL
----------- -------------------- ----------- ----------- --------------------------------------- -----------
105002018 面壳 0 0 4.0 0
103500008 塑料钉 100 -84 233.0 -264
103028003 装饰盖(与30-4通用) 0 0 23.0 -12(3 row(s) affected)
*/