销售流向平调级数判断

NO66781

于 2022-11-15 18:36:07 发布

阅读量188

点赞数

分类专栏： KSOA 文章标签： sql 数据库

本文链接：https://blog.csdn.net/NO66781/article/details/127871221

版权

KSOA 专栏收录该内容

31 篇文章 9 订阅

订阅专栏

本文介绍了通过SQL语句实现的数据操作，包括数据录入、更新、分类与层级调整。通过示例展示了如何创建表、插入记录，并根据业务流程进行不同类型的交易（购入、销售、平调）标记。关键步骤包括设置默认值、更新层级状态和动态调整业务级别。最后，通过SQL查询显示了不同业务阶段的汇总数据。

摘要由CSDN通过智能技术生成

DECLARE @pici VARCHAR(40)
SET @pici = 'JHAA0000005381_2'

CREATE TABLE #spls(
	id INT NOT NULL IDENTITY(1,1)
   ,rq VARCHAR(10) NULL  DEFAULT ''
   ,djbh VARCHAR(15) NULL  DEFAULT ''
   ,spid VARCHAR(11) NULL  DEFAULT ''
   ,hw VARCHAR(11) NULL  DEFAULT ''
   ,pihao VARCHAR(50) NULL  DEFAULT ''
   ,pic VARCHAR(50) NULL DEFAULT ''
   ,rkshl INT NULL DEFAULT 0
   ,chkshl INT NULL DEFAULT 0
   ,jcshl INT NULL DEFAULT 0
   ,zhy VARCHAR(20) NULL DEFAULT ''
   ,sc_dwbh VARCHAR(11) NULL  DEFAULT ''
   ,sy_dwbh VARCHAR(11) NULL  DEFAULT ''
   ,level INT NULL DEFAULT -99
   ,pd_dwbh VARCHAR(11) NULL  DEFAULT ''
   ,zd_dwbh VARCHAR(11) NULL  DEFAULT ''   
)

INSERT INTO #spls (rq,djbh,spid,hw,pihao,pic,rkshl,chkshl,jcshl,zhy,sc_dwbh,sy_dwbh,level,pd_dwbh,zd_dwbh)
SELECT a.rq,a.djbh,b.spid,b.hw,b.pihao,b.pici,b.shl,0,0
      ,CASE WHEN a.djbs = 'JHA' THEN '购进入库'
            WHEN a.djbs = 'JHC' THEN '购进退出'
       END AS zhy
      ,a.dwbh_sy AS sc_dwbh,a.dwbh AS sy_dwbh,-98 AS level,'' AS pd_dwbh,'' AS zd_dwbh 
FROM jh_rkhz AS a(NOLOCK)
     INNER JOIN jh_rkmx AS b(NOLOCK) ON a.djbh = b.djbh 
WHERE b.pici = @pici

UNION ALL 
 
SELECT a.rq,a.djbh,b.spid,b.hw,b.pihao,b.pici,0,b.shl,0
      ,CASE WHEN a.djbs = 'XSA' THEN '销售出库'
            WHEN a.djbs = 'XSC' THEN '销售退回'
       END AS zhy
      ,'' AS sc_dwbh,a.dwbh_sy AS sy_dwbh,-99 AS level,'' AS pd_dwbh,a.dwbh AS zd_dwbh 
FROM pf_ckhz AS a(NOLOCK)
     INNER JOIN pf_ckmx AS b(NOLOCK) ON a.djbh = b.djbh
     INNER JOIN huoweizl AS c(NOLOCK) ON b.hw = c.hw
WHERE b.pici = @pici
  
UNION ALL 

SELECT a.rq,a.djbh,b.spid,b.dchw,b.pihao,b.pici,0,b.shl,0
      ,'平调移库' AS zhy
      ,'' AS sc_dwbh,c.dwbh AS sy_dwbh,0 AS level,d.dwbh AS pd_dwbh,'' AS zd_dwbh
FROM dbckhz AS a(NOLOCK)
     INNER JOIN dbckmx AS b(NOLOCK) ON a.djbh = b.djbh
     INNER JOIN huoweizl AS c(NOLOCK) ON a.dchw = c.hw
     INNER JOIN huoweizl AS d(NOLOCK) ON a.drhw = d.hw
WHERE b.pici = @pici
ORDER BY rq ASC 

DECLARE @sy_dwbh VARCHAR(11)
SELECT TOP 1 @sy_dwbh = a.sy_dwbh FROM #spls AS a WHERE a.level = -98
/*一级*/
UPDATE a
SET a.level = 1
FROM #spls AS a
WHERE a.pd_dwbh <> '' AND a.sy_dwbh = @sy_dwbh

/*一级退*/
UPDATE a
SET a.level = -1
FROM #spls AS a
WHERE a.pd_dwbh <> '' AND a.level < 2 AND a.sy_dwbh != @sy_dwbh AND a.pd_dwbh = @sy_dwbh

/*二级*/ 
UPDATE a
SET a.level = 2
FROM #spls AS a
WHERE a.pd_dwbh <> '' AND a.level = 0 AND a.pd_dwbh != @sy_dwbh
      AND EXISTS(
                 SELECT 1
                 FROM #spls AS b
                 WHERE b.pd_dwbh <> '' AND b.level = 1 AND b.sy_dwbh = @sy_dwbh
                       AND a.sy_dwbh = b.pd_dwbh
                )
 
/*二级退*/  
                            
UPDATE a
SET a.level = -2
FROM #spls AS a
WHERE a.pd_dwbh <> '' AND a.level = 2 AND a.sy_dwbh != @sy_dwbh AND a.pd_dwbh != @sy_dwbh
      AND EXISTS(
                 SELECT 1
                 FROM #spls AS b
                 WHERE b.pd_dwbh <> '' AND b.level = 2 AND b.sy_dwbh != @sy_dwbh AND b.pd_dwbh != @sy_dwbh
                       AND a.pd_dwbh = b.sy_dwbh
                       AND a.pd_dwbh != @sy_dwbh
                )
               
/*三级*/ 
UPDATE a
SET a.level = 2
FROM #spls AS a
WHERE a.pd_dwbh <> '' AND a.level = 0 
      AND EXISTS(
                 SELECT 1
                 FROM #spls AS b
                 WHERE b.pd_dwbh <> '' AND b.level = 2 AND b.sy_dwbh != @sy_dwbh
                       AND a.sy_dwbh = b.pd_dwbh
                )


SELECT * FROM #spls a WHERE a.pd_dwbh <> ''
--SELECT DISTINCT sy_dwbh,pd_dwbh FROM #spls a WHERE a.pd_dwbh <> '' ORDER BY pd_dwbh ASC 

DROP TABLE #spls