select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when B.Lid is null then 'A作业中' else 'AB作业结束' end as wk
wk
from (
select Lid,Did,Tname,sum(pnum) as pnum,sum(Snum) as Snum,wk
from a
group by Lid,Did,Tname,wk
) as t left join B
on t.Lid=B.Lid
and t.Did=B.Did
and t.Tname=B.Tname
and t.wk=b.wk
case when B.Lid is null then 'A作业中' else 'AB作业结束' end as wk
wk
from (
select Lid,Did,Tname,sum(pnum) as pnum,sum(Snum) as Snum,wk
from a
group by Lid,Did,Tname,wk
) as t left join B
on t.Lid=B.Lid
and t.Did=B.Did
and t.Tname=B.Tname
and t.wk=b.wk
insert @a select
11,5,'BP店',4,2,3 union select
11,5,'BP店',5,2,3 union select
11,6,'美店',6,6,3 union select
13,6,'美店',7,7,3 union select
13,6,'美店',8,8,1 union select
14,6,'美店',9,9,3 union select
14,6,'美店',10,10,3 declare @B table(Lid int,Did int,Tname varchar(10),wk int)
insert @b select
11,5,'BP店',3 union select
11,5,'BP店',3 union select
11,6,'美店',3 select a.lid,a.did,max(a.tname),sum(pnum),sum(snum),
case when count(1) = (select count(1) from @a where lid = a.lid and did = a.did and wk = 3) then 'AB作业结束'
when not exists(select 1 from @b where lid = a.lid and did = a.did) and exists(select count(1) from @a where lid = a.lid and did = a.did and wk = 1) then 'AB作业中' else '' end
from @a a
left join @b b on a.lid = b.lid and a.did = b.did
group by a.lid,a.did
/*lid did
----------- ----------- ---------- ----------- ----------- ----------
11 5 BP店 9 4 AB作业结束
11 6 美店 6 6 AB作业结束
13 6 美店 15 15 AB作业中
14 6 美店 19 19 AB作业结束(所影响的行数为 4 行)
*/
--上面的错了,重新写select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did
select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did
[code=SQL]
select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did[/code]
select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did
select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did
[code=SQL]
select t.Lid,t.Did,t.Tname,t.pnum,t.Snum,
case when t.wk =3 and t1.wk=3 then 'AB作业结束'
when t.wk =3 and isnull(t1.wk,1)<>3 then 'A作业结束'
when t.wk =1 and isnull(t1.wk,1)<>3 then 'A作业中'
else '不知道'
end as wk
from (
select Lid,Did,min(Tname) as Tname,sum(pnum) as pnum,sum(Snum) as Snum,min(wk) as wk
from a
group by Lid,Did
) as t left join (
select Lid,Did,min(wk) as wk
from B
group by Lid,Did
) as t1
on t.Lid=t1.Lid
and t.Did=t1.Did
[/code]
MIN()和Max()函数,觉得这个地方不太好啊,不过处理这个是没有问题的
alter procedure dbo.UP_DisplayDPSMng_Select_GetAllDpsDetail
(
@Para_ShipReserveTime char(10), --出荷引当日区分
@Para_DPSWrkSpace smallint
)
as
begin
select smss.DPSLaneCD
,wdps.DemandCD
,mstb.BranchName
,sum(isnull(ShipReserveQty,0)) as ShipReserveQty
,Cast(sum(isnull(PickingQty,0)) as int) as PickingQty
,Cast(sum(isnull(ReqQty,0)) as int) as ReqQty
,(case(avg(isnull(wdps.WORKSTATUS,0)))
when 0 then '未作業'
when 3 then (case(max(isnull(tbst.workstatus,0)))
when 0 then 'ピッキング完了'
when 3 then '検品完了'
else '検品中'
end)
--作業状態区分します
else 'ピッキング中'
end) Allworkstatus
from
DBCenterMaster.dbo.MST_BRANCHES mstb
,(select LocSignCD,DPSLaneCD from dbo.SMST_DPSMACHINEADDRESS where DPSWrkSpace=@Para_DPSWrkSpace) smss
,dbo.TB_WRKDPSDIVISION as wdps
left join dbo.TB_SHIPINSPRESULT tbst
on tbst.DemandCD=wdps.DemandCD
and tbst.LocSignCD=wdps.LocSignCD
and tbst.LOT=wdps.LOT
and tbst.ShipReserveCD=wdps.ShipReserveCD
and tbst.ProductCD=wdps.ProductCD
where ShipReserveTime between (@Para_ShipReserveTime + ' 00:00:00.000') and (@Para_ShipReserveTime + ' 23:59:59.998')--出荷引当日区分
and wdps.DemandCD= mstb.BRANCHCD and smss.LocSignCD=wdps.LocSignCD
group by smss.DPSLaneCD,wdps.DemandCD,mstb.BranchName
order by Allworkstatus desc,smss.DPSLaneCD asc,wdps.DemandCD asc
end
呵呵,一些情况没有呢
alter procedure dbo.UP_DisplayDPSMng_Select_GetAllDpsDetail
(
@Para_ShipReserveTime char(10), --出荷引当日区分
@Para_DPSWrkSpace smallint
)
as
begin
select smss.DPSLaneCD
,wdps.DemandCD
,mstb.BranchName
,isnull(sum(ShipReserveQty),0) as ShipReserveQty
,isnull(sum(PickingQty),0) as PickingQty
,isnull(sum(ReqQty),0) as ReqQty
,(case avg(isnull(wdps.WORKSTATUS,0))
when 0 then '未作業'
when 3 then (case isnull(max(tbst.workstatus),0)
when 0 then 'ピッキング完了'
when 3 then '検品完了'
else '検品中'
end)
--作業状態区分します
else 'ピッキング中'
end) Allworkstatus
from
DBCenterMaster.dbo.MST_BRANCHES mstb
,(select LocSignCD,DPSLaneCD from dbo.SMST_DPSMACHINEADDRESS where DPSWrkSpace=@Para_DPSWrkSpace) smss
,dbo.TB_WRKDPSDIVISION as wdps
left join dbo.TB_SHIPINSPRESULT tbst
on tbst.DemandCD=wdps.DemandCD
and tbst.LocSignCD=wdps.LocSignCD
and tbst.LOT=wdps.LOT
and tbst.ShipReserveCD=wdps.ShipReserveCD
and tbst.ProductCD=wdps.ProductCD
where datediff(d, ShipReserveTime, @Para_ShipReserveTime)=0 --ShipReserveTime between (@Para_ShipReserveTime + ' 00:00:00.000') and (@Para_ShipReserveTime + ' 23:59:59.998')--出荷引当日区分
and wdps.DemandCD= mstb.BRANCHCD and smss.LocSignCD=wdps.LocSignCD
group by smss.DPSLaneCD,wdps.DemandCD,mstb.BranchName
order by Allworkstatus desc,smss.DPSLaneCD asc,wdps.DemandCD asc
end