Files
old_jintiantongye_wms/db/数据库更新20170930.sql

327 lines
23 KiB
MySQL
Raw Permalink Normal View History

2025-09-12 17:30:18 +08:00
<EFBFBD><EFBFBD>/****** Object: StoredProcedure [dbo].[p_ub T<EFBFBD>]S<EFBFBD>YXb<EFBFBD>v<EFBFBD>mEmX[>e<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N] Script Date: 2017/9/30 fgmQ 13:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_ub T<EFBFBD>]S<EFBFBD>YXb<EFBFBD>v<EFBFBD>mEmX[>e<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N]
(
@<EFBFBD>d\O(u7b T varchar(20),
@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> VARCHAR(50),
@P<EFBFBD><EFBFBD>[X[>eR:S VARCHAR(50),
@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe INT,
@b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe INT OUTPUT,
@l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S INT OUTPUT -- ubYagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>e <EFBFBD>ԏ<EFBFBD>V,{Nagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S
)
AS
/**
* \R^(W NM<EFBFBD><EFBFBD>[<EFBFBD>v<EFBFBD>mEm'<EFBFBD>MOXb<EFBFBD>vR_v^0RN<>[<5B>mEm'<EFBFBD>MOX[>e
* l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD><EFBFBD>HN(W<EFBFBD>m'<EFBFBD>MO <0C><><EFBFBD>HN(WEm'<EFBFBD>MO0
* Created by jzx on 2015-9-16
* Used by CkczManager{|
* Modified by jzx on 2015-10-15 l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD>X<EFBFBD>R <EFBFBD>^MO*Npe W[<EFBFBD>k[{ <EFBFBD> <EFBFBD><EFBFBD>N<EFBFBD>S/f&T<EFBFBD><EFBFBD>*g<EFBFBD>[8h<EFBFBD>QeQ<EFBFBD>^USnc`S(u
* Modified by jzx on 2017-9-30 KNMR*g<EFBFBD>Q<EFBFBD>qysxW[<EFBFBD>k<P:Nnull<EFBFBD>v<EFBFBD>^MO
*/
BEGIN
IF LEN(@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>)>0
BEGIN
IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE ',%'
SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = ',' + @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>;
IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'
SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> + ',';
END;
SET NOCOUNT ON
DECLARE c CURSOR FOR
SELECT x.kwbh,x.hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,r.rksj,cpxx.cfkwFqsx,x.qysx,x.kfwpgd,x.kfwpzl
, '\R^(W NM<EFBFBD><EFBFBD>[<5B>v<EFBFBD>mEm'<EFBFBD>MOXb<EFBFBD>vR_v^0RN<EFBFBD>[<EFBFBD>mEm'<EFBFBD>MOX[>e' bz
FROM kwjbxx x join (select kwbh kw, max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls, min(rksj) rksj
from cpkcxx b where b.zlzt='2' and b.sl > 0 and isnumeric(b.cpbh)=1
group by kwbh
having count(*)=1 -- <EFBFBD>^MO
N<PX[>e<EFBFBD>NN<EFBFBD>y<EFBFBD>N<EFBFBD>T
)r on x.kwbh=r.kw
join cpxx on r.cpbh=cpxx.cpbh
WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0
AND (wckw IS NOT NULL OR nckw IS NOT NULL)
AND x.<EFBFBD>S8OMO<EFBFBD><EFBFBD> g'<EFBFBD> = 0
AND (@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> IS NULL OR @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'+CAST(hdh AS varchar(2))+',%')
AND (@P<EFBFBD><EFBFBD>[X[>eR:S IS NULL OR @P<EFBFBD><EFBFBD>[X[>eR:S=x.qysx)
AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO
ORDER BY storeDist desc;
DECLARE @zckw VARCHAR(20), @zrkw VARCHAR(20), @hdh TINYINT, @zlbh INT
, @cpbh type_cpbh, @lpxx type_ph, @zlzt char(1), @P<EFBFBD><EFBFBD>[X[>eR:S<P varchar(20)
, @kfwpgd int, @kfwpzl int, @match char(1), @cntRows INT, @zcyy VARCHAR(200)
, @rksj DATE, @<EFBFBD>^MOR:S<P varchar(20);
SET @cntRows = 0;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0;
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = NULL;
IF ISNULL(@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe, 0) < 1
SET @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe = 1;
OPEN c;
WHILE 1=1
BEGIN
FETCH c INTO @zckw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @P<EFBFBD><EFBFBD>[X[>eR:S<P, @<EFBFBD>^MOR:S<P, @kfwpgd, @kfwpzl, @zcyy;
IF @@FETCH_STATUS <> 0
BREAK;
SET @cntRows = @cntRows + 1;
-- M<EFBFBD><EFBFBD>S<EFBFBD>^MO@b(W<EFBFBD>]S<EFBFBD><EFBFBD>_ gX[>e g T<EFBFBD>N<EFBFBD>T<EFBFBD>vY*N<EFBFBD>^MO
IF NOT EXISTS(select count(*)jls
from cpkcxx b join kwjbxx k on b.kwbh=k.kwbh
where b.zlzt='2'
AND b.sl > 0 -- eQ<EFBFBD>^c<EFBFBD>N<EFBFBD>[bT <EFBFBD>eQ<EFBFBD>^US<EFBFBD>l g<EFBFBD>[8hMR <EFBFBD>O<EFBFBD>Q<EFBFBD>ssl=0<EFBFBD>v<EFBFBD>`b_
AND b.sl=k.zsl -- <EFBFBD>^MO
N<EFBFBD>SX[>eN<EFBFBD>y<EFBFBD>N<EFBFBD>T/fb<EFBFBD>z
AND (wckw IS NOT NULL OR nckw IS NOT NULL)
AND k.<EFBFBD>S8OMO<EFBFBD><EFBFBD> g'<EFBFBD> = 0
AND k.hdh=@hdh AND b.cpbh=@cpbh AND ISNULL(b.lpxx,'')=@lpxx AND b.zlzt=@zlzt
having count(*) > 1)
begin
PRINT '~b N0R<EFBFBD>T<EFBFBD>Q<EFBFBD>^<5E>^MO' + @zckw + '
NX[>e<EFBFBD>N<EFBFBD>T<EFBFBD>v T<EFBFBD>v<EFBFBD>^MO'
CONTINUE;
end;
SET @zrkw = NULL;
SET @zlbh = NULL;
SET @match = 0
SELECT TOP 1 @zrkw=kwbh
,@match = case when k.kw is not null and substring(kwbh,3,4)=substring(kw,3,4) AND hdh=@hdh and nckw is null then 4
when k.kw is not null and substring(kwbh,3,4)=substring(@zckw,3,4) AND hdh=@hdh and nckw is null then 3
when k.kw is not null then 2
when substring(kwbh,3,4)=substring(@zckw,3,4) then 1
else 0 end
FROM kwjbxx m left join (select distinct k.kwbh kw from cpkcxx k join kwjbxx r on k.kwbh=r.kwbh
-- Em'<EFBFBD>MO gHQeQ<65>^<5E>v TirD<72><44>m<EFBFBD>^MO <0C>OHQX[>e
where k.rksj < @rksj and cpbh=@cpbh and ISNULL(lpxx,'')=@lpxx and k.zlzt=@zlzt and nckw is not null
and ISNULL(r.qysx, @P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P and xnkw=0 and r.hdh=@hdh
)k
on m.wckw=k.kw
WHERE hdh=@hdh AND (ISNULL(qysx,@P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P) AND zsl=0 AND sfsd=0 AND xnkw=0
AND isnull(kfwpgd,0)>=isnull(@kfwpgd,0) AND isnull(kfwpzl,0)>=isnull(@kfwpzl,0)
AND EXISTS(SELECT 2 FROM cpkcxx x JOIN kwjbxx k ON x.kwbh=k.kwbh
WHERE x.kwbh IN(m.wckw,m.nckw) AND x.kwbh != @zckw
AND x.sl=k.zsl AND x.sl>0
AND k.sfsd = 0
AND x.cpbh=@cpbh AND ISNULL(x.lpxx,'')=@lpxx AND x.zlzt=@zlzt
)
ORDER BY 2 desc -- Em'<EFBFBD>MOX[>e TirD<72><44>^MOgOHQ <0C>vQ!k TRB\<5C>^MOOHQ
, case when wckw is null then 0 else 1 end -- Em'<EFBFBD>MOOHQ
, storeDist;
IF @zrkw IS NOT NULL
BEGIN
EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b T, null, @zcyy;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
print @zckw + '>>' + @zrkw;
END
SET @cntRows = @cntRows + 1;
--PRINT CAST(@cntRows AS CHAR(4)) + '. ' + @zckw + ' > ' + ISNULL(@zrkw, ' ') + ' M<><4D>[=' + @match + ',' + @zcyy + ', R:S9SM<53><1F>' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ' ' + @<EFBFBD>^MOR:S<P
-- + ',hdh=' + cast(@hdh as varchar(2)) + ',cffq=' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ',cpbh=' + @cpbh + ',lpxx=' + @lpxx + ',zlzt=' + @zlzt;
IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
BREAK;
END;
CLOSE c;
DEALLOCATE c;
END
go
/****** Object: StoredProcedure [dbo].[p_Rub g'<EFBFBD><EFBFBD>^MO<4D>vl<76><6C>^c<>N] Script Date: 2017/9/30 fgmQ 15:01:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_Rub g'<EFBFBD><EFBFBD>^MO<4D>vl<76><6C>^c<>N]
(
@<EFBFBD>d\O(u7b T varchar(20),
@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N INT, -- 1<EFBFBD><EFBFBD>mEm'<EFBFBD>MOX[>eirD<72> N T <0C>\<5C>m'<EFBFBD>MO'<EFBFBD>irl<EFBFBD>p<EFBFBD> <09>|2<08>Em'<EFBFBD>MO:Nzz<EFBFBD>^MO<EFBFBD>e <EFBFBD><EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD><EFBFBD>Q <09>|4<08>\R:S N9SM<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD> <09>
@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N VARCHAR(50),
@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> VARCHAR(50),
@P<EFBFBD><EFBFBD>[X[>eR:S VARCHAR(50),
@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe INT,
@b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe INT OUTPUT,
@l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S INT OUTPUT -- ubYagl<EFBFBD><EFBFBD>^c<EFBFBD>N/f <EFBFBD>ԏ<EFBFBD>V,{Nagl<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S
)
AS
/**
* \<EFBFBD>mEm'<EFBFBD>MO
NX[>e N T'<EFBFBD>ir<EFBFBD>v<EFBFBD>m'<EFBFBD>MObR:S N9SM<EFBFBD><EFBFBD>vEm'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>p<EFBFBD>
* Created by jzx on 2015-9-16
* Used by CkczManager{|
* Modified by jzx on 2015-10-15 l<EFBFBD><EFBFBD>Q<EFBFBD>^MO<EFBFBD>X<EFBFBD>R <EFBFBD>^MO*Npe W[<EFBFBD>k[{ <EFBFBD> <EFBFBD><EFBFBD>N<EFBFBD>S/f&T<EFBFBD><EFBFBD>*g<EFBFBD>[8h<EFBFBD>QeQ<EFBFBD>^USnc`S(u
* Modified by jzx on 2017-9-30 KNMR*g<EFBFBD>Q<EFBFBD>qysxW[<EFBFBD>k<P:Nnull<EFBFBD>v<EFBFBD>`<EFBFBD>Q<EFBFBD><EFBFBD>S<EFBFBD>X<EFBFBD>RN*N<EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>eQEm'<EFBFBD>MO<EFBFBD>vR/e
*/
BEGIN
IF LEN(@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>) > 0
BEGIN
IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE ',%'
SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = ',' + @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD>;
IF @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'
SET @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> = @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> + ',';
END;
SET NOCOUNT ON
DECLARE c CURSOR FOR
SELECT kwbh,hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,rksj,cpxx.cfkwFqsx,x.qysx,x.kfwpgd,x.kfwpzl
,CASE WHEN wckw IS NOT NULL AND (r.cpbh <> r2.cpbh OR ISNULL(r.lpxx,'')<>ISNULL(r2.lpxx,'') OR r.zlzt<>r2.zlzt) THEN '<00>mEm'<EFBFBD>MOX[>eirD<EFBFBD> N T <EFBFBD>\<EFBFBD>m'<EFBFBD>MO'<EFBFBD>irl<EFBFBD>p<EFBFBD>'
WHEN wckw IS NOT NULL AND r2.cpbh IS NULL AND r2.zsl=0 THEN 'Em'<EFBFBD>MO:Nzz<EFBFBD>^MO<EFBFBD>e <EFBFBD><EFBFBD>m'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD><EFBFBD>Q'
ELSE '\R:S N9SM<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD>' END bz
FROM kwjbxx x join (select kwbh kw, max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls, min(rksj) rksj
from cpkcxx b where zlzt='2' and sl > 0 and isnumeric(cpbh)=1
group by kwbh
having count(*)=1
)r
on x.kwbh=r.kw
LEFT join (select a.kwbh Em'<EFBFBD>MO, a.zsl, a.sfsd, max(b.cpbh)cpbh,max(b.lpxx)lpxx,max(b.zlzt)zlzt,count(*)jls
from kwjbxx a LEFT join cpkcxx b on a.kwbh = b.kwbh
where nckw is not null
group by a.kwbh, a.zsl, a.sfsd
)r2
on x.wckw=r2.Em'<EFBFBD>MO
join cpxx on r.cpbh=cpxx.cpbh
WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0
-- X[>e N TirD<EFBFBD><EFBFBD>vl<EFBFBD>p<EFBFBD>
AND ( (x.wckw IS NOT NULL AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&1) != 0) AND (r.cpbh <> isnull(r2.cpbh,'') OR r.lpxx<>isnull(r2.lpxx,'') OR r.zlzt<>isnull(r2.zlzt,3)))
-- Em'<EFBFBD>MO<EFBFBD>l'<EFBFBD>ir<EFBFBD>vl<EFBFBD>p<EFBFBD>
OR (x.wckw IS NOT NULL AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&2) != 0) AND r2.cpbh IS NULL AND r2.zsl=0 AND r2.sfsd=0)
-- X[>e TirD<EFBFBD> <EFBFBD>FOR:S N9SM<EFBFBD><EFBFBD>v_Nl<EFBFBD>p<EFBFBD>
OR (x.qysx<>cpxx.cfkwFqsx AND ((@l<EFBFBD><EFBFBD>Q<EFBFBD>^MOP<EFBFBD><EFBFBD>[ag<EFBFBD>N&4) != 0))
)
AND (@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N IS NULL OR r2.cpbh+'.'+ISNULL(r2.lpxx,'')=@l<EFBFBD><EFBFBD>Q<EFBFBD>N<EFBFBD>TP<EFBFBD><EFBFBD>[ag<EFBFBD>N)
AND (@<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> IS NULL OR @<EFBFBD>cd<EFBFBD><EFBFBD>]S<EFBFBD>Rh<EFBFBD> NOT LIKE '%,'+CAST(hdh AS varchar(2))+',%')
AND (@P<EFBFBD><EFBFBD>[X[>eR:S IS NULL OR @P<EFBFBD><EFBFBD>[X[>eR:S=ISNULL(x.qysx,@P<EFBFBD><EFBFBD>[X[>eR:S))
AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO
ORDER BY CASE WHEN (x.wckw IS NOT NULL AND r.cpbh <> r2.cpbh OR ISNULL(r.lpxx,'')<>ISNULL(r2.lpxx,'') OR r.zlzt<>r2.zlzt) THEN 0
WHEN ISNULL(x.qysx,'')<>ISNULL(cpxx.cfkwFqsx,'') THEN 1
WHEN (x.wckw IS NOT NULL AND r2.cpbh IS NULL AND r2.zsl=0) THEN 2
ELSE 3 END
, CASE WHEN wckw IS NOT NULL THEN 0 ELSE 1 END, storeDist desc;
DECLARE @zckw VARCHAR(20), @zrkw VARCHAR(20), @hdh TINYINT, @zlbh INT
, @cpbh type_cpbh, @lpxx type_ph, @zlzt char(1), @P<EFBFBD><EFBFBD>[X[>eR:S<P varchar(20)
, @kfwpgd int, @kfwpzl int, @match char(1), @cntRows INT, @zcyy VARCHAR(200)
, @rksj DATE, @<EFBFBD>^MOR:S<P varchar(20);
SET @cntRows = 0;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0;
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = NULL;
IF ISNULL(@l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe, 0) < 1
SET @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe = 1;
OPEN c;
WHILE 1=1
BEGIN
FETCH c INTO @zckw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @P<EFBFBD><EFBFBD>[X[>eR:S<P, @<EFBFBD>^MOR:S<P, @kfwpgd, @kfwpzl, @zcyy;
IF @@FETCH_STATUS <> 0
BREAK;
SET @cntRows = @cntRows + 1;
SET @zrkw = NULL;
SET @zlbh = NULL;
SET @match = 0
SELECT TOP 1 @zrkw=kwbh
,@match = case when k.kw is not null and substring(kwbh,3,4)=substring(kw,3,4) AND hdh=@hdh and nckw is null then 4
when k.kw is not null and substring(kwbh,3,4)=substring(@zckw,3,4) AND hdh=@hdh and nckw is null then 3
when k.kw is not null then 2
when substring(kwbh,3,4)=substring(@zckw,3,4) then 1
else 0 end
FROM kwjbxx m left join (select distinct k.kwbh kw from cpkcxx k join kwjbxx r on k.kwbh=r.kwbh
-- Em'<EFBFBD>MO gHQeQ<65>^<5E>v TirD<72><44>m<EFBFBD>^MO <0C>OHQX[>e
where k.rksj < @rksj and cpbh=@cpbh and ISNULL(lpxx,'')=@lpxx and k.zlzt=@zlzt and nckw is not null
and r.qysx=@P<EFBFBD><EFBFBD>[X[>eR:S<P and xnkw=0 and r.hdh=@hdh
)k
on m.wckw=k.kw
WHERE hdh>=@hdh AND (ISNULL(qysx,@P<EFBFBD><EFBFBD>[X[>eR:S<P)=@P<EFBFBD><EFBFBD>[X[>eR:S<P OR @P<EFBFBD><EFBFBD>[X[>eR:S<P IS NULL)
AND zsl=0 AND sfsd=0 AND xnkw=0
AND isnull(kfwpgd,0)>=isnull(@kfwpgd,0) AND isnull(kfwpzl,0)>=isnull(@kfwpzl,0)
AND NOT EXISTS(SELECT 2 FROM cpkcxx x WHERE x.kwbh IN(m.wckw,m.nckw)
AND (x.cpbh<>@cpbh OR x.lpxx<>@lpxx OR x.zlzt<>@zlzt))
ORDER BY case hdh when @hdh then 0 else 1 end
, 2 desc -- Em'<EFBFBD>MOX[>e TirD<72><44>^MOgOHQ <0C>vQ!k TRB\<5C>^MOOHQ
, case when wckw is null then 0 else 1 end -- Em'<EFBFBD>MOOHQ
, storeDist;
IF @zrkw IS NOT NULL
BEGIN
EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b T, null, @zcyy;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
PRINT @zckw + '>>' + @zrkw;
END
ELSE
PRINT @zckw + '>>NULL';
SET @cntRows = @cntRows + 1;
--PRINT CAST(@cntRows AS CHAR(4)) + '. ' + @zckw + ' > ' + ISNULL(@zrkw, ' ') + ' M<><4D>[=' + @match + ',' + @zcyy + ', R:S9SM<53><1F>' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ' ' + @<EFBFBD>^MOR:S<P
-- + ',hdh=' + cast(@hdh as varchar(2)) + ',cffq=' + @P<EFBFBD><EFBFBD>[X[>eR:S<P + ',cpbh=' + @cpbh + ',lpxx=' + @lpxx + ',zlzt=' + @zlzt;
IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
BREAK;
END;
CLOSE c;
DEALLOCATE c;
-- 2017-9-30 <EFBFBD>e<EFBFBD>XR/e<EFBFBD><EFBFBD>Y<EFBFBD>g<EFBFBD>l g<EFBFBD>v <EFBFBD>RꁨR\<EFBFBD>m'<EFBFBD>MO g'<EFBFBD> <EFBFBD>Em'<EFBFBD>MO<EFBFBD>l g'<EFBFBD><EFBFBD>vXb<EFBFBD>vl<EFBFBD>p<EFBFBD>
IF @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT x.kwbh, x.nckw,x.hdh,r.cpbh,ISNULL(r.lpxx, ''),r.zlzt,rksj,x.kfwpgd,x.kfwpzl, 'ꁨR\Em'<EFBFBD>MOXb<EFBFBD>vl<EFBFBD>eQ<EFBFBD>m'<EFBFBD>MO' AS bz
FROM kwjbxx x JOIN cpkcxx r ON x.kwbh=r.kwbh
WHERE x.zsl>0 AND x.sfsd=0 AND x.xnkw=0 AND x.nckw IS NOT NULL
AND x.<EFBFBD>S8OMO<EFBFBD><EFBFBD> g'<EFBFBD> = 0
AND x.Xb<EFBFBD>v*Npe>0 -- nx<EFBFBD>OXb<EFBFBD>v(W<EFBFBD>^MO
N
AND NOT EXISTS(SELECT 2 FROM v_*g<EFBFBD>[bc<EFBFBD>N v WHERE v.kwbh=x.kwbh) -- nx<EFBFBD>O<EFBFBD>l gvQ<EFBFBD>N*g<EFBFBD>[8hUSnc<EFBFBD>d\Odk<EFBFBD>^MO
AND NOT EXISTS(SELECT 2 FROM kwjbxx n WHERE n.kwbh=x.nckw AND n.zsl=0 AND n.sfsd=0 AND n.Xb<EFBFBD>v*Npe=0)
ORDER BY r.rksj DESC, storeDist desc;
OPEN c2;
WHILE 1=1
BEGIN
FETCH c2 INTO @zckw, @zrkw, @hdh, @cpbh, @lpxx, @zlzt, @rksj, @kfwpgd, @kfwpzl, @zcyy;
IF @@FETCH_STATUS <> 0
BREAK;
SET @zlbh = NULL;
EXEC p_ubteXb<EFBFBD>vl<EFBFBD><EFBFBD>^c<EFBFBD>N @zlbh output, @zckw, @zrkw, @<EFBFBD>d\O(u7b T, null, @zcyy;
SET @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe = @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe + 1;
IF @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S IS NULL
SET @l<EFBFBD><EFBFBD>^c<EFBFBD>N<EFBFBD>S = @zlbh;
PRINT @zckw + '>>>' + @zrkw;
SET @cntRows = @cntRows + 1;
IF @l<EFBFBD><EFBFBD>yc<EFBFBD>N*Npe <= @b<EFBFBD>Rl<EFBFBD><EFBFBD>^pe
BREAK;
END;
CLOSE c2;
DEALLOCATE c2;
END;
END
go