bs3000+ 常用sql语句 2011-09-03更新_select n'testing connection...-CSDN博客

本文链接：https://blog.csdn.net/cyuyan112233/article/details/6745230

exec sp_executesql N'EXEC Pro_StorageFix_Inventory @vWHList=@P1, @vInvList=@P2', N'@P1 varchar(8000),@P2 varchar(8000)', '', ''

SELECT N'Testing Connection...'

--查询商品进货订单
SELECT * FROM JORDER
SELECT * FROM JORDERMX

--盘点单状态
SELECT * FROM CKPDDMX
SELECT * FROM CKPDD
UPDATE CKPDD SET YS='0',JS='0',SP='0' WHERE DJBH='SPD000020';
DELETE FROM CKPDDMX WHERE DJBH='SPD000004';
DELETE FROM CKPDD WHERE DJBH='SPD000004';

SELECT [DJBH], [MIBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL], [SL_1], [SL_2], [SL_3], [BZSL], [CKJ], [ZK], [DJ], [DJ_1], [DJ_2], [DJ_3], [JE], [JE_1], [JE_2], [JE_3], [BZJE], [BZS], [HH], [DJH], [MIH], [MXH], [DJH_1], [MIH_1], [MXH_1], [BZ], [BYZD1], [BYZD2], [BYZD3], [BYZD4], [BYZD5], [BYZD6], [BYZD7], [BYZD8], [BYZD9], [BYZD10], [BYZD11], [BYZD12], [BYZD13], [BYZD14], [BYZD15] FROM [BS3000+_002_2010].[dbo].[CKPDDMX]
SELECT * FROM CKPDDMX WHERE DJBH='SPD000010';
INSERT INTO CKPDDMX (DJBH,MXBH,SPDM,GG1DM,GG2DM,SL) VALUES('SPD000010','2','Q122320507','83','12','162');
INSERT INTO CKPDDMX (DJBH,MXBH,SPDM,GG1DM,GG2DM,SL)VALUES('SPD000010','3','Q121110480','66','100','7');
INSERT INTO [BS3000+_002_2010].[dbo].[CKPDDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SPD000010','26','Q122320507','83','12','162');
INSERT INTO [BS3000+_000_2010].[dbo].[CKPDDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SPD000010','2','Q121110480','66','100','7');

INSERT INTO [BS3000+_000_2010].[dbo].[CKPDDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SPD000010','1','Q121110480','66','100','7');
INSERT INTO [BS3000+_000_2010].[dbo].[CKPDDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SPD000010','2','Q121110480','66','110','19');
INSERT INTO [BS3000+_000_2010].[dbo].[CKPDDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SPD000010','3','Q121110480','66','120','55');

服务器: 消息 547，级别 16，状态 1，行 1
INSERT 语句与 COLUMN FOREIGN KEY 约束 'FK_GuiGe2_CKPDDMX' 冲突。该冲突发生于数据库 'BS3000+_000_2010'，表 'GuiGe2', column 'GGDM'。

UPDATE CKPDDMX SET CKJ='11',ZK='1',DJ='11',JE='11',BZJE='11',HH='1' WHERE DJBH='SPD000010' AND SPDM='Q122320507';

UPDATE CKPDDMX SET HH='1' WHERE DJBH='SPD000010'

SELECT * FROM GuiGe2

SELECT * FROM PHJRDMX WHERE DJBH='SD1001261';
INSERT INTO [BS3000+_000_2010].[dbo].[PHJRDMX] ([DJBH], [MXBH], [SPDM], [GG1DM], [GG2DM], [SL]) VALUES('SD1001261','4','Q121110480','66','13','40');

--库存盈亏单
SELECT * FROM CKYKDMX
SELECT * FROM CKYKD
DELETE FROM CKYKDMX WHERE DJBH='SYK000024';
DELETE FROM CKYKD WHERE DJBH='SYK000024';

--查询库存调整单
SELECT * FROM CKTZDMX
DELETE FROM CKTZDMX WHERE DJBH='STZ000365';
DELETE FROM CKTZD WHERE DJBH='STZ003365';
UPDATE CKTZD SET YS='0',JZ='0' WHERE DJBH='STZ000275';

-----------------------------------------------------》
--商品进货订单状态更改
--SELECT 23 AND 25 LIST DIFF!
SELECT * FROM JORDER WHERE DJBH='JH1000023' OR DJBH='JH1000025';
--商品进货订单未知
UPDATE JORDER SET YS = null WHERE DJBH='JH1000023';
--商品进货订单审核状态！
UPDATE JORDER SET JZ='0' WHERE DJBH='JH1000013';
--商品进货订单执行状态
UPDATE JORDER SET JS='1' WHERE DJBH='JH1000012';
--商品进货订单终止状态
UPDATE JORDER SET SP='0' WHERE DJBH='JH1000024';

--cd1-82
--商品进货订单未知DBJRD
UPDATE DBJRD SET YS = null WHERE DJBH='CD1000082';
--商品进货订单审核状态！
UPDATE DBJRD SET JZ='0' WHERE DJBH='CD1000004';
--商品进货订单执行状态
UPDATE DBJRD SET JS='0' WHERE DJBH='CD1000004';
--商品进货订单终止状态
UPDATE DBJRD SET SP='0' WHERE DJBH='CD1000082';

--查询渠道调拨订单
SELECT * FROM DBJRD
--商品进货订单执行状态
UPDATE DBJRD SET JS='0' WHERE DJBH='CD1000075';
--商品进货订单终止状态
UPDATE DBJRD SET SP='0' WHERE DJBH='CD1000009';
UPDATE JORDER SET YS = null WHERE DJBH='JH1000023';

--渠道调拨-通知单-出库单-通知单-订单类型
SELECT * FROM PSEND
UPDATE PSEND SET DM4='001' WHERE DJBH='SD2000082';
--
UPDATE PSEND SET YS = null WHERE DJBH='SD2000082';
--渠道调拨-通知单-审核状态！
UPDATE PSEND SET JZ='1' WHERE DJBH='SD2000092';
--渠道调拨-通知单-执行状态
UPDATE PSEND SET JS='1' WHERE DJBH='SD2000092';
--渠道调拨-通知单-终止状态
UPDATE PSEND SET SP='0' WHERE DJBH='SD2000082';
SELECT * FROM PSEND WHERE YS='0'

--渠道调拨-通知单-出库单-通知单-订单类型
SELECT * FROM DSEND
UPDATE DSEND SET DM4='001' WHERE DJBH='CD2000001';
--
UPDATE DSEND SET YS = null WHERE DJBH='CD2000098';
--渠道调拨-通知单-审核状态！
UPDATE DSEND SET JZ='1' WHERE DJBH='CD2000112';
--渠道调拨-通知单-执行状态
UPDATE DSEND SET JS='1' WHERE DJBH='CD2000112';
--渠道调拨-通知单-终止状态
UPDATE DSEND SET SP='0' WHERE DJBH='CD2000098';

--商店配货出库单-入库-语句
update SDPHD set SH='1', SHRQ='2011-01-13', SHR='系统管理员' where DJBH='SD3000016'
--商店配货出库单-记账-语句
update SDPHD set JZ='1', JZRQ='2011-01-13', JZR='系统管理员' where DJBH='SD3000016'

--出库单-发货单-订单类型
SELECT * FROM QDDBD
UPDATE QDDBD SET DM4='001' WHERE DJBH='CD3000001';
UPDATE QDDBD SET YS='0' WHERE DJBH='CD3000164';
-----------------------------------------------------《
--删除商品进货订单的顺序
DELETE FROM JORDERMX WHERE DJBH='JH1000089';
DELETE FROM JORDER WHERE DJBH='JH1000089';

-----------------------------------------------------》
--商品进货订单ADD!
SELECT * FROM JORDER WHERE DJBH='JH1000004' OR DJBH='JH1000016'

-- 修改定价
UPDATE JORDER SET BYZD1='3' WHERE DJBH='JH1000004'
UPDATE JORDER SET BYZD5='3' WHERE DJBH='JH1000004'

--SELECT 23 AND 25 LIST DIFF!
SELECT * FROM JORDER WHERE DJBH='JH1000026' OR DJBH='JH1000025';
--商品进货订单未知
UPDATE JORDER SET YS = null WHERE DJBH='JH1000023';
--商品进货订单审核状态！
UPDATE JORDER SET JZ='0' WHERE DJBH='JH1000013';
--商品进货订单执行状态
UPDATE JORDER SET JS='1' WHERE DJBH='JH1000023';
--商品进货订单终止状态
UPDATE JORDER SET SP='1' WHERE DJBH='JH1000023';

--商品进货订单审核状态！
UPDATE JORDER SET JZ='0' WHERE DJBH='JH1000017';
--商品进货订单执行状态
UPDATE JORDER SET JS='0' WHERE DJBH='JH1000004';
--商品进货订单终止状态
UPDATE JORDER SET SP='0' WHERE DJBH='JH1000004';

SELECT * FROM JORDER WHERE DJBH='JH1000220' OR DJBH='JH1000221'

UPDATE JORDER SET DM4='004' WHERE DJBH='JH1000220';

-----------------------------------------------------《

--查找生产通知单的审核和执行
--查询通知单
SELECT * FROM JSEND
SELECT * FROM JSENDMX

SELECT * FROM JSENDMX WHERE DJBH='JH2000022';

UPDATE JSEND SET YS='0' WHERE DJBH='JH2000214';
UPDATE JSEND SET JZ='0' WHERE DJBH='JH2000214';
UPDATE JSEND SET JS='0' WHERE DJBH='JH2000214';
UPDATE JSEND SET SP='0' WHERE DJBH='JH2000214';

--删除生产通知单的顺序
DELETE FROM JSENDMX WHERE DJBH='JH2000165';
DELETE FROM JSEND WHERE DJBH='JH2000165';

生产3步
--生产入库单
SELECT * FROM SPJHD
SELECT * FROM SPJHDMX
-- 配货出库单
SELECT * FROM SDPHD WHERE DJBH='SD3000186';
--删除配货出货发货单
DELETE FROM SDPHDMX WHERE DJBH='SD3001810';
DELETE FROM SDPHD WHERE DJBH='SD3001810';

--折扣
BYZD12
SELECT * FROM SDPHD
UPDATE SDPHD SET BYZD12='0.35' WHERE DJBH='SD3000148';

-- 修改配货出库单的仓库
UPDATE SDPHD SET DM2='101' WHERE DJBH='SD3000003';

SELECT * FROM SPJHD WHERE DJBH='JH3000153' OR DJBH='JH3000154';
--修改生产入库单的入库验收状态
UPDATE SPJHD SET YS='0',JZ='0' WHERE DJBH='JH3001152';
--删除生产入库单的顺序
DELETE FROM SPJHDMX WHERE DJBH='JH3000955';
DELETE FROM SPJHD WHERE DJBH='JH3000955';
DELETE FROM SPJHDMX WHERE DJBH='JH3000028';
DELETE FROM SPJHD WHERE DJBH='JH3000028';

SELECT * FROM VW_SDPHD WHERE

SELECT * FROM VW_SDTHD WHERE
SELECT * FROM SPKCB WHERE SPDM='105M389000';
SELECT * FROM SPKCB ORDER by SPDM, GG1DM,GG2DM;
--查出发货相同的款号不同的数据与现有库存对不上的数据
SELECT A.* FROM SPKCB AS A,SPKBCB AS B WHERE A.SPDM=B.SPDM AND A.SL!=B.SL AND A.SL!=B.SL_2;
UPDATE SPKCB SET SL_2=10 WHERE SPDM='105M389000' AND CKDM='872002';
SELECT * FROM VW_LSXHMX

-------------------------------------------------------------------------------------------
SELECT * FROM SPJHDMX WHERE SPDM='Q132711380'

SELECT * FROM SPJHDMX WHERE DJBH='JH3001023'

-------------------------------------------------------------------------------------------

INSERT INTO [BS3000+_002_2010].[dbo].[DBJRDMX] ([DJBH],)VALUES();

select * from JORDER

--删除生产入库单的顺序
DELETE FROM SPJHDMX WHERE DJBH='JH3000017';
DELETE FROM SPJHD WHERE DJBH='JH3000017';
DELETE FROM SPJHDMX WHERE DJBH='JH3000028';
DELETE FROM SPJHD WHERE DJBH='JH3000028';

UPDATE SPJHD SET YS = null WHERE DJBH='JH3000028';
--渠道调拨-通知单-审核状态！
UPDATE SPJHD SET JZ='1' WHERE DJBH='JH3000028';
--渠道调拨-通知单-执行状态
UPDATE SPJHD SET JS='1' WHERE DJBH='JH3000028';
--渠道调拨-通知单-终止状态
UPDATE SPJHD SET SP='0' WHERE DJBH='JH3000028';
UPDATE SPJHD SET YS = null,JZ='0',JS='0',SP='0' WHERE DJBH='JH3001038';
UPDATE SPJHD SET YS = '1',JZ='1',JS='1',SP='1' WHERE DJBH='JH3001038';

--查询生产入库单入库类型，入错仓的单号。
SELECT * FROM SPJHD WHERE DJBH='JH3000417' DM2='000';
UPDATE SPJHD SET DM2='001' WHERE DJBH='JH3001061'
SELECT * FROM SPJHDMX WHERE DJBH='JH3000944';
SELECT * FROM SPJHD WHERE DM2='001';
--JH3000045--
--JH3000054
--JH3000056
--JH3000090
--JH3000094
--JH3000097
--JH3000098
--查询生产入库单和生产通知单相等的单号。
SELECT A.DJBH FROM SPJHD AS A,JSEND AS B WHERE A.BYZD3=B.DJBH;

SELECT * FROM SPJHD WHERE DJBH='JH3000067' OR DJBH='JH3000048'
101 m
001 q
UPDATE SPJHD SET DM2='101' WHERE DJBH='JH3000097'

SELECT * FROM JSEND;
--删除生产通知单的顺序
DELETE FROM JSENDMX WHERE DJBH='JH2000181';
DELETE FROM JSEND WHERE DJBH='JH2000181';
DELETE FROM JSENDMX WHERE DJBH='JH2000049';
DELETE FROM JSEND WHERE DJBH='JH2000049';

--删除生产入库单的顺序
DELETE FROM SPJHDMX WHERE DJBH='JH3000111';
DELETE FROM SPJHD WHERE DJBH='JH3000111';

--删除生产通知单的顺序
DELETE FROM JSENDMX WHERE DJBH='JH2000021';
DELETE FROM JSEND WHERE DJBH='JH2000021';

select 1 from DBJRD with (nolock) where DJBH='CD1000003'

SELECT * FROM PKMVDMX

--查询渠道调拨订单
SELECT * FROM DBJRD
SELECT * FROM DBJRDMX
--删除渠道调拨订单顺序
DELETE FROM DBJRDMX WHERE DJBH='CD1000619';
DELETE FROM DBJRD WHERE DJBH='CD1000619';
--更改仓库
UPDATE DBJRD SET DM2='101' WHERE DJBH='CD1000215';

UPDATE DBJRD SET YS = null,JZ='0',JS='0',SP='0' WHERE DJBH='CD1000625';
UPDATE DBJRD SET YS = '1',JZ='1',JS='1',SP='1' WHERE DJBH='CD1000441';

--新增单号
--更改渠道调拨订单-订单类型
SELECT * FROM DBJRD WHERE DJBH='CD1000022' OR DJBH='CD1000023';

-- 修改生产入库单的仓库
SELECT * FROM SPJHD WHERE DM2 ='000';
SELECT * FROM SPJHD WHERE DJBH='JH3000175';
UPDATE SPJHD SET DM2='001' WHERE DJBH='JH3000399';

--渠道调拨通知单
SET INT @INT_LXDJ_NUM=30;
SELECT * FROM DSEND WHERE LXDJ> @INT_LXDJ_NUM
SELECT * FROM DSEND WHERE DJBH='CD2000025';
--删除渠道调拨通知单顺序
DELETE FROM DSENDMX WHERE DJBH='CD2001312';
DELETE FROM DSEND WHERE DJBH='CD2001312';
SELECT * FROM DSEND WHERE LXDJ='CD1000711' AND ;
--
SELECT * FROM DSEND WHERE YS='1' AND JZ='1' AND JS ='0';
UPDATE DSEND SET YS = null WHERE DJBH='CD2000098';
--渠道调拨-通知单-审核状态！
update DSEND set YS='1', YSRQ='2011-02-18', YSR='系统管理员' where DJBH='CD2000130'
--渠道调拨-通知单-记账状态！
update DSEND set JZ='1', JZRQ='2011-02-18', JZR='系统管理员' where DJBH='CD2000130'
--渠道调拨-通知单-执行状态
UPDATE DSEND SET JS='0' WHERE DJBH='CD2000098';
--渠道调拨-通知单-终止状态
UPDATE DSEND SET SP='0' WHERE DJBH='CD2000098';

UPDATE DSEND SET YS = null,JZ='0',JS='0',SP='0' WHERE DJBH='CD2000708';
UPDATE DSEND SET YS ='1',JZ='1',JS='1',SP='1' WHERE DJBH='CD2000129';
UPDATE DSEND SET YS = '0' WHERE DJBH='CD2000633';
UPDATE DSEND SET JZ = '1' WHERE DJBH='CD2000119';

--更改调拨单-通知单-单据关联
SELECT * FROM DSEND
UPDATE DSEND SET LXDJ='CD1000005',YDJH=NULL WHERE DJBH='CD2000113'
--更改调拨单-发货单-单据关联
SELECT * FROM QDDBD WHERE DJBH='CD3000091'
UPDATE QDDBD SET YDJH = NULL,LXDJ='CD1000005',BYZD3='CD2000113' WHERE DJBH='CD3000090'
--UPDATE DSEND SET YDJH=NULL WHERE DJBH='CD2000123'

SELECT * FROM DSEND WHERE YS='1' AND JZ='1' AND JS ='0';
UPDATE DSEND SET YS = '0' WHERE DJBH='CD2000130';
UPDATE DSEND SET JZ = '0' WHERE DJBH='CD2000130';

--商品定价单
SELECT * FROM JGTZDMX WHERE SPDM='Q132211216';
BD1000113
BD1000156
BD1000177

SELECT * FROM JGTZD WHERE DJBH='BD1000113' OR DJBH='BD1000156' OR DJBH='BD1000177';

DELETE FROM JGTZDMX WHERE DJBH='BD1000108';
DELETE FROM JGTZD WHERE DJBH='BD1000108';

--渠道调拨发货单CD2001320
SELECT * FROM QDDBD WHERE BYZD3='CD2001340'

SELECT * FROM QDDBD WHERE DJBH='CD3000963';
--删除渠道调拨发货单顺序
DELETE FROM QDDBDMX WHERE DJBH='CD3001037';
DELETE FROM QDDBD WHERE DJBH='CD3001037';
SELECT * FROM QDDBD WHERE DJBH='CD3000004' OR DJBH='CD3000011'

SELECT * FROM QDDBD WHERE LXDJ='CD1000006';
SELECT * FROM QDDBD WHERE BYZD3='CD2000860';
--
SELECT * FROM QDDBD WHERE DJBH='CD3000755';
UPDATE QDDBD SET BYZD12='1' WHERE DJBH='CD3000786';
--出货类型
--补货出货：002 代卖出货：004 换货出货：003
UPDATE QDDBD SET DM4='003' WHERE DJBH='CD3000816';

UPDATE QDDBD SET JZ='0',SH='0',YS='0',JS='0' WHERE DJBH='CD3001041';
UPDATE QDDBD SET JZ='1',SH='1',YS='1',JS='1' WHERE DJBH='CD3001041';

--
SELECT * FROM QDTHD WHERE DJBH='CT3000044';
DELETE FROM QDTHDMX WHERE DJBH='CT3000101';
DELETE FROM QDTHD WHERE DJBH='CT3000101';
UPDATE QDTHD SET DM2='101' WHERE DJBH='CT3000113';
--出货类型
UPDATE QDTHD SET DM4='021' WHERE DJBH='CT3000046';
UPDATE QDTHD SET JZ='0',SH='0',YS='0',JS='0' WHERE DJBH='CT3000137';
UPDATE QDTHD SET JZ='1',SH='1',YS='1',JS='1' WHERE DJBH='CT3000137';

--调拨单-发货单-状态！
SELECT * FROM QDDBD
--调拨单-退货入库单的-的类型
SELECT * FROM QDTHD

--更改调拨单-发货单-单据关联
SELECT * FROM QDDBD WHERE DJBH='CD3000091'
UPDATE QDDBD SET YDJH = NULL,LXDJ='CD1000005',BYZD3='CD2000113' WHERE DJBH='CD3000090'
UPDATE QDDBD SET YDJH = NULL WHERE DJBH='CD3000091'

-- 修改渠道调拨发货单的调出仓库
UPDATE QDDBD SET DM2='101' WHERE DJBH='CD3000616';

--修改生产入库单的入库验收状态
UPDATE SPJHD SET YS='0' WHERE DJBH='JH3000333';

SELECT * FROM QDDBD WHERE

--1配货订单
SELECT * FROM PHJRDMX WHERE DJBH='SD1000117' AND SPDM='111MF10983';

--更改折扣
UPDATE PHJRDMX SET ZK='0.35' WHERE DJBH='SD1000117' AND SPDM='111MF10983' AND GG1DM='57' AND GG2DM='01';

SD1000030
--删除配货订单
DELETE FROM PHJRDMX WHERE DJBH='SD1001280';
DELETE FROM PHJRD WHERE DJBH='SD1001280';
UPDATE PHJRD SET YS = null,JZ='0',JS='0',SP='0' WHERE DJBH='SD1000877';
SELECT * FROM PHJRD WHERE LXDJ='SD1001249';

--删除退货订单
DELETE FROM PTSQDMX WHERE DJBH='ST1000005';
DELETE FROM PTSQD WHERE DJBH='ST1000005';
SELECT * FROM PHJRD
UPDATE PHJRD SET DM2='001' WHERE DJBH='SD1000115';
UPDATE PHJRD SET JZ = '0' WHERE DJBH='SD1000109';

--2配货通知单
SELECT * FROM PSEND
--删除配货通知单
DELETE FROM PSENDMX WHERE DJBH='SD2002243';
DELETE FROM PSEND WHERE DJBH='SD2002243';
UPDATE PSEND SET JZ='0',SH='0',YS='0',JS='0',SP='0' WHERE DJBH='SD2001824';
SELECT * FROM PSEND WHERE DJBH='SD2001249';

SELECT * FROM PSEND WHERE YS='1' AND JZ='1' AND JS ='0';
UPDATE PSEND SET YS = '1' WHERE DJBH='SD2000091';
UPDATE PSEND SET JZ = '1' WHERE DJBH='SD2000091';
--更改配货单-通知单-单据关联
SELECT * FROM DSEND
UPDATE PSEND SET LXDJ='SD1000004',YDJH=NULL WHERE DJBH='SD2000083'
--更改配货单-发货单-单据关联
SELECT * FROM QDDBD WHERE DJBH='CD3000091'
UPDATE SDPHD SET YDJH = NULL,LXDJ='SD1000004',BYZD3='SD2000083' WHERE DJBH='SD3000064'

--删除配货退货通知单
DELETE FROM PTSNDMX WHERE DJBH='ST2000002';
DELETE FROM PTSND WHERE DJBH='ST2000002';
SELECT * FROM PSEND
UPDATE PSEND SET DM2='501' WHERE DJBH='SD2000043';

-- 3配货出库单
SELECT * FROM SDPHD
SELECT * FROM SDPHD WHERE DJBH='SD3001800';
--删除配货出货发货单
DELETE FROM SDPHDMX WHERE DJBH='SD3001922';
DELETE FROM SDPHD WHERE DJBH='SD3001922';
SELECT * FROM SDPHD WHERE DJBH='SD3001365';
UPDATE SDPHD SET DM2='601' WHERE DJBH='SD3001800';
--储藏类型
UPDATE SDPHD SET DM4='004' WHERE DJBH='SD3001647';
UPDATE SDPHD SET DM1='769003' WHERE DJBH='SD3000458';
SELECT * FROM SDPHD WHERE BYZD3='SD2001526';
SELECT * FROM SDPHD WHERE LXDJ='SD1001180';

SELECT * FROM SDPHD
UPDATE SDPHD SET YDJH='出货类型选择已修正' WHERE DJBH='SD3000380'

SELECT * FROM SDPHD WHERE DJBH='SD3001220';
UPDATE SDPHD SET BYZD12='0.35' WHERE DJBH='SD3001220';

UPDATE SDPHD SET JZ='0',SH='0',YS='0',JS='0' WHERE DJBH='SD3001903';
UPDATE SDPHD SET JZ='1',SH='1',YS='1',JS='1' WHERE DJBH='SD3001903';

SELECT * FROM SDPHD WHERE DJBH='SD3000237' OR DJBH='SD3000238' OR DJBH='SD3001647' ;
--dm4字段是出仓类型
UPDATE SDPHD SET DM4='030' WHERE DJBH='SD3001647';

SELECT * FROM SDPHD

SELECT * FROM SDPHD
UPDATE SDPHD SET DJBH='SD3000159' WHERE DJBH='SD3000046';
SELECT * FROM SDPHDMX
UPDATE SDPHDMX SET DJBH='SD3000059' WHERE DJBH='SD3000070';

--删除配货退货入库单
DELETE FROM SDTHDMX WHERE DJBH='ST3000322';
DELETE FROM SDTHD WHERE DJBH='ST3000322';
SELECT * FROM SDTHDMX WHERE DJBH='ST3000005';
SELECT * FROM SDTHD WHERE DJBH='ST3000335';
UPDATE SDTHD SET DM2='601' WHERE DJBH='ST3000335';
UPDATE SDTHD SET DM4='021' WHERE DJBH='ST3000160';
UPDATE SDTHD SET DM1='076901' WHERE DJBH='ST3000338';
--折扣
UPDATE SDTHD SET BYZD12='1' WHERE DJBH='ST3000009'
--操作员
UPDATE SDTHD SET YGDM='002' WHERE DJBH='ST3000009'
--更改供货价
UPDATE SDTHD SET BYZD1='4' WHERE DJBH='ST3000009'

SELECT * FROM SDTHD WHERE DJBH='ST3000009' OR DJBH='ST3000022' OR DJBH='ST3000023';
UPDATE SDTHD SET YS='0',JZ='0',SH='0' WHERE DJBH='ST3000292'
UPDATE SDTHD SET YS='1',JZ='1',SH='1' WHERE DJBH='ST3000177'

SELECT A.DJBH , B.BYZD3 FROM PSEND AS A,SDPHD AS B WHERE A.DJBH=B.BYZD3;
SELECT A.DHBH, FROM PSEND

--删除订单：通知单占用？发货单占用
SELECT A.DJBH FROM PHJRD AS A,PSEND AS B , SDPHD AS C WHERE A.DJBH='SD1000044' AND A.DJBH = C.LXDJ OR A.DJBH = B.LXDJ ;

SD3000012
SD2000013
SD1000035

--查询童装汇总库存统计001,002,003
SELECT SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC ,SUM(CASE GUIGE2.GGWZ2 WHEN 1 THEN VW_CKJXCMX.SL END) AS WZ1,SUM(CASE GUIGE2.GGWZ2 WHEN 2 THEN VW_CKJXCMX.SL END) AS WZ2,SUM(CASE GUIGE2.GGWZ2 WHEN 3 THEN VW_CKJXCMX.SL END) AS WZ3,SUM(CASE GUIGE2.GGWZ2 WHEN 4 THEN VW_CKJXCMX.SL END) AS WZ4,SUM(CASE GUIGE2.GGWZ2 WHEN 5 THEN VW_CKJXCMX.SL END) AS WZ5,SUM(CASE GUIGE2.GGWZ2 WHEN 6 THEN VW_CKJXCMX.SL END) AS WZ6,SUM(CASE GUIGE2.GGWZ2 WHEN 7 THEN VW_CKJXCMX.SL END) AS WZ7,SUM(CASE GUIGE2.GGWZ2 WHEN 8 THEN VW_CKJXCMX.SL END) AS WZ8,SUM(CASE GUIGE2.GGWZ2 WHEN 9 THEN VW_CKJXCMX.SL END) AS WZ9,SUM(CASE GUIGE2.GGWZ2 WHEN 10 THEN VW_CKJXCMX.SL END) AS WZ10,SUM(VW_CKJXCMX.SL) AS SL_S,CAST((CASE WHEN SHANGPIN.SPDM IN (SELECT DISTINCT SPDM FROM QDTJDMP,QDTJD WHERE QDTJD.DJBH=QDTJDMP.DJBH AND QDTJD.JZ='1') THEN 1 ELSE 0 END) AS BIT) AS IsAdjust FROM (SELECT '0' AS LX,CKDM,QR,RQ,QRRQ,SPDM,GG1DM,GG2DM,SL FROM VW_CKJXCMX WITH (NOLOCK) UNION ALL SELECT '1' AS LX,DM2 AS CKDM,YS AS QR,RQ,RQ AS QRRQ,SPDM,GG1DM,GG2DM,-SL AS SL FROM VW_SPPKD WITH (NOLOCK) WHERE LL IN ('0','1') )VW_CKJXCMX, SHANGPIN, CANGKU, DANWEI ,GUIGE2 WITH (NOLOCK) WHERE VW_CKJXCMX.SPDM=SHANGPIN.SPDM AND VW_CKJXCMX.CKDM=CANGKU.CKDM AND SHANGPIN.DWMC = DANWEI.DWDM AND VW_CKJXCMX.GG2DM=GUIGE2.GGDM AND (VW_CKJXCMX.RQ <= '2011-01-06') AND (VW_CKJXCMX.LX = '0') AND (CANGKU.CKDM in ('001','002','003')) GROUP BY SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC

--查询女装汇总库存统计101,102,103
SELECT SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC ,SUM(CASE GUIGE2.GGWZ2 WHEN 1 THEN VW_CKJXCMX.SL END) AS WZ1,SUM(CASE GUIGE2.GGWZ2 WHEN 2 THEN VW_CKJXCMX.SL END) AS WZ2,SUM(CASE GUIGE2.GGWZ2 WHEN 3 THEN VW_CKJXCMX.SL END) AS WZ3,SUM(CASE GUIGE2.GGWZ2 WHEN 4 THEN VW_CKJXCMX.SL END) AS WZ4,SUM(CASE GUIGE2.GGWZ2 WHEN 5 THEN VW_CKJXCMX.SL END) AS WZ5,SUM(CASE GUIGE2.GGWZ2 WHEN 6 THEN VW_CKJXCMX.SL END) AS WZ6,SUM(CASE GUIGE2.GGWZ2 WHEN 7 THEN VW_CKJXCMX.SL END) AS WZ7,SUM(CASE GUIGE2.GGWZ2 WHEN 8 THEN VW_CKJXCMX.SL END) AS WZ8,SUM(CASE GUIGE2.GGWZ2 WHEN 9 THEN VW_CKJXCMX.SL END) AS WZ9,SUM(CASE GUIGE2.GGWZ2 WHEN 10 THEN VW_CKJXCMX.SL END) AS WZ10,SUM(VW_CKJXCMX.SL) AS SL_S,CAST((CASE WHEN SHANGPIN.SPDM IN (SELECT DISTINCT SPDM FROM QDTJDMP,QDTJD WHERE QDTJD.DJBH=QDTJDMP.DJBH AND QDTJD.JZ='1') THEN 1 ELSE 0 END) AS BIT) AS IsAdjust FROM (SELECT '0' AS LX,CKDM,QR,RQ,QRRQ,SPDM,GG1DM,GG2DM,SL FROM VW_CKJXCMX WITH (NOLOCK) UNION ALL SELECT '1' AS LX,DM2 AS CKDM,YS AS QR,RQ,RQ AS QRRQ,SPDM,GG1DM,GG2DM,-SL AS SL FROM VW_SPPKD WITH (NOLOCK) WHERE LL IN ('0','1') )VW_CKJXCMX, SHANGPIN, CANGKU, DANWEI ,GUIGE2 WITH (NOLOCK) WHERE VW_CKJXCMX.SPDM=SHANGPIN.SPDM AND VW_CKJXCMX.CKDM=CANGKU.CKDM AND SHANGPIN.DWMC = DANWEI.DWDM AND VW_CKJXCMX.GG2DM=GUIGE2.GGDM AND (VW_CKJXCMX.RQ <= '2011-01-06') AND (VW_CKJXCMX.LX = '0') AND (CANGKU.CKDM in ('101','102','103')) GROUP BY SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC

SELECT SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC ,SUM(CASE GUIGE2.GGWZ2 WHEN 1 THEN VW_CKJXCMX.SL END) AS WZ1,SUM(CASE GUIGE2.GGWZ2 WHEN 2 THEN VW_CKJXCMX.SL END) AS WZ2,SUM(CASE GUIGE2.GGWZ2 WHEN 3 THEN VW_CKJXCMX.SL END) AS WZ3,SUM(CASE GUIGE2.GGWZ2 WHEN 4 THEN VW_CKJXCMX.SL END) AS WZ4,SUM(CASE GUIGE2.GGWZ2 WHEN 5 THEN VW_CKJXCMX.SL END) AS WZ5,SUM(CASE GUIGE2.GGWZ2 WHEN 6 THEN VW_CKJXCMX.SL END) AS WZ6,SUM(CASE GUIGE2.GGWZ2 WHEN 7 THEN VW_CKJXCMX.SL END) AS WZ7,SUM(CASE GUIGE2.GGWZ2 WHEN 8 THEN VW_CKJXCMX.SL END) AS WZ8,SUM(CASE GUIGE2.GGWZ2 WHEN 9 THEN VW_CKJXCMX.SL END) AS WZ9,SUM(CASE GUIGE2.GGWZ2 WHEN 10 THEN VW_CKJXCMX.SL END) AS WZ10,SUM(VW_CKJXCMX.SL) AS SL_S,CAST((CASE WHEN SHANGPIN.SPDM IN (SELECT DISTINCT SPDM FROM QDTJDMP,QDTJD WHERE QDTJD.DJBH=QDTJDMP.DJBH AND QDTJD.JZ='1') THEN 1 ELSE 0 END) AS BIT) AS IsAdjust FROM (SELECT '0' AS LX,CKDM,QR,RQ,QRRQ,SPDM,GG1DM,GG2DM,SL FROM VW_CKJXCMX WITH (NOLOCK) UNION ALL SELECT '1' AS LX,DM2 AS CKDM,YS AS QR,RQ,RQ AS QRRQ,SPDM,GG1DM,GG2DM,-SL AS SL FROM VW_SPPKD WITH (NOLOCK) WHERE LL IN ('0','1') )VW_CKJXCMX, SHANGPIN, CANGKU, DANWEI ,GUIGE2 WITH (NOLOCK) WHERE VW_CKJXCMX.SPDM=SHANGPIN.SPDM AND VW_CKJXCMX.CKDM=CANGKU.CKDM AND SHANGPIN.DWMC = DANWEI.DWDM AND VW_CKJXCMX.GG2DM=GUIGE2.GGDM AND (VW_CKJXCMX.RQ <= '2011-01-07') AND (VW_CKJXCMX.LX = '0') AND (CANGKU.CKDM in ('1')) GROUP BY SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC

--查询商品汇总统计-款汇总
SELECT SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC ,SUM(VW_CKJXCMX.SL) AS SL_S,CAST((CASE WHEN SHANGPIN.SPDM IN (SELECT DISTINCT SPDM FROM QDTJDMP,QDTJD WHERE QDTJD.DJBH=QDTJDMP.DJBH AND QDTJD.JZ='1') THEN 1 ELSE 0 END) AS BIT) AS IsAdjust FROM (SELECT '0' AS LX,CKDM,QR,RQ,QRRQ,SPDM,GG1DM,GG2DM,SL FROM VW_CKJXCMX WITH (NOLOCK) UNION ALL SELECT '1' AS LX,DM2 AS CKDM,YS AS QR,RQ,RQ AS QRRQ,SPDM,GG1DM,GG2DM,-SL AS SL FROM VW_SPPKD WITH (NOLOCK) WHERE LL IN ('0','1') )VW_CKJXCMX, SHANGPIN, CANGKU, DANWEI WITH (NOLOCK) WHERE VW_CKJXCMX.SPDM=SHANGPIN.SPDM AND VW_CKJXCMX.CKDM=CANGKU.CKDM AND SHANGPIN.DWMC = DANWEI.DWDM AND (SHANGPIN.SPDM in ('106WG60380')) AND (VW_CKJXCMX.RQ <= '2011-01-07') AND (VW_CKJXCMX.LX = '0') GROUP BY SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC

--查询商品汇总统计-款汇总-码-色
SELECT SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC ,SUM(CASE GUIGE2.GGWZ2 WHEN 1 THEN VW_CKJXCMX.SL END) AS WZ1,SUM(CASE GUIGE2.GGWZ2 WHEN 2 THEN VW_CKJXCMX.SL END) AS WZ2,SUM(CASE GUIGE2.GGWZ2 WHEN 3 THEN VW_CKJXCMX.SL END) AS WZ3,SUM(CASE GUIGE2.GGWZ2 WHEN 4 THEN VW_CKJXCMX.SL END) AS WZ4,SUM(CASE GUIGE2.GGWZ2 WHEN 5 THEN VW_CKJXCMX.SL END) AS WZ5,SUM(CASE GUIGE2.GGWZ2 WHEN 6 THEN VW_CKJXCMX.SL END) AS WZ6,SUM(CASE GUIGE2.GGWZ2 WHEN 7 THEN VW_CKJXCMX.SL END) AS WZ7,SUM(CASE GUIGE2.GGWZ2 WHEN 8 THEN VW_CKJXCMX.SL END) AS WZ8,SUM(CASE GUIGE2.GGWZ2 WHEN 9 THEN VW_CKJXCMX.SL END) AS WZ9,SUM(CASE GUIGE2.GGWZ2 WHEN 10 THEN VW_CKJXCMX.SL END) AS WZ10,SUM(VW_CKJXCMX.SL) AS SL_S,CAST((CASE WHEN SHANGPIN.SPDM IN (SELECT DISTINCT SPDM FROM QDTJDMP,QDTJD WHERE QDTJD.DJBH=QDTJDMP.DJBH AND QDTJD.JZ='1') THEN 1 ELSE 0 END) AS BIT) AS IsAdjust FROM (SELECT '0' AS LX,CKDM,QR,RQ,QRRQ,SPDM,GG1DM,GG2DM,SL FROM VW_CKJXCMX WITH (NOLOCK) UNION ALL SELECT '1' AS LX,DM2 AS CKDM,YS AS QR,RQ,RQ AS QRRQ,SPDM,GG1DM,GG2DM,-SL AS SL FROM VW_SPPKD WITH (NOLOCK) WHERE LL IN ('0','1') )VW_CKJXCMX, SHANGPIN, CANGKU, DANWEI ,GUIGE2 WITH (NOLOCK) WHERE VW_CKJXCMX.SPDM=SHANGPIN.SPDM AND VW_CKJXCMX.CKDM=CANGKU.CKDM AND SHANGPIN.DWMC = DANWEI.DWDM AND VW_CKJXCMX.GG2DM=GUIGE2.GGDM AND (SHANGPIN.SPDM in ('106WG60380')) AND (VW_CKJXCMX.RQ <= '2011-01-07') AND (VW_CKJXCMX.LX = '0') GROUP BY SHANGPIN.SPDM,SHANGPIN.SPMC,SHANGPIN.SPBM,SHANGPIN.ZJF,DANWEI.DWMC

--查看3商品进货入库单的表
select SPJHD.YS, SPJHD.JZ, SPJHD.JS, SPJHD.DJBH, SPJHD.RQ, SPJHD.YDJH, SPJHD.QDDM, QUDAO.QDDM as QDDM_SUB, QUDAO.QDMC as QDMC, SPJHD.DM1, GONGHUOSHANG.GHSDM as GHSDM, GONGHUOSHANG.GHSMC as GHSMC, SPJHD.DM2, CANGKU.CKDM as CKDM, CANGKU.CKMC as CKMC, SPJHD.BYZD1, SPJHD.BYZD5, SPJHD.BYZD12, SPJHD.YGDM, SPJHD.YGDM as YGDM_SUB, YUANGONG.YGMC as YGMC, SPJHD.DM4, RKLX.LXDM as LXDM, RKLX.LXMC as LXMC, SPJHD.FPLX, FPLX.FPDM as FPDM_SUB, FPLX.FPMC as FPMC, SPJHD.SL, SPJHD.JE, SPJHD.BZJE, SPJHD.LXDJ, SPJHD.BYZD3, SPJHD.BYZD13, SPJHD.LL, SPJHD.ZDR, SPJHD.RQ_4, SPJHD.BYZD4, SPJHD.RQ_3, SPJHD.YSR, SPJHD.YSRQ, SPJHD.JZR, SPJHD.JZRQ, SPJHD.BYZD6, SPJHD.BYZD9, SPJHD.BYZD10, SPJHD.BYZD14, SPJHD.JE_3, SPJHD.JE_2, SPJHD.ZSR, SPJHD.ZSR as FSDM, SFKFS.FSMC as FSMC, SPJHD.LLR, SPJHD.LLR as ZHDM, YHZH.ZHMC as ZHMC, SPJHD.BYZD8, SPJHD.BZ from SPJHD, QUDAO as QUDAO, GONGHUOSHANG as GONGHUOSHANG, CANGKU as CANGKU, YUANGONG as YUANGONG, RKLX as RKLX, FPLX as FPLX, SFKFS as SFKFS, YHZH as YHZH where SPJHD.QDDM = QUDAO.QDDM and SPJHD.DM1 = GONGHUOSHANG.GHSDM and SPJHD.DM2 = CANGKU.CKDM and SPJHD.YGDM *= YUANGONG.YGDM and SPJHD.DM4 = RKLX.LXDM and SPJHD.FPLX = FPLX.FPDM and SPJHD.ZSR *= SFKFS.FSDM and SPJHD.LLR *= YHZH.ZHDM

SPJHD, QUDAO as QUDAO, GONGHUOSHANG as GONGHUOSHANG, CANGKU as CANGKU, YUANGONG as YUANGONG, RKLX as RKLX, FPLX as FPLX, SFKFS as SFKFS, YHZH as YHZH
--a-商品进货入库单表
select * from SPJHD
--b-公司信息表
select * from GONGHUOSHANG
--c-仓库信息表
select * from CANGKU
--d-业务员表
select * from YUANGONG
--e-入库类型表
select * from RKLX

FPLX, SFKFS as SFKFS, YHZH as YHZH

select * from FPLX

select * from SFKFS

select * from YHZH
--以上是入库的信息表

--查看调拨通知单的信息
select 1 as ReturnValue from DSENDMX as A where DJBH='CD2000065' and SPDM= 'Q122320088' and (not exists(select 1 from DBJRDMX where DJBH='CD1000013' and SPDM=A.SPDM and GG1DM=A.GG1DM and GG2DM=A.GG2DM) or exists (select 1 from (select SPDM, GG1DM, GG2DM, sum(SL) as SL, sum(SL_2) as SL_2, sum(SL_3) as SL_3, sum(SL_4) as SL_4 from (select SPDM, GG1DM, GG2DM, isnull(SL, 0) as SL, isnull(SL_2, 0) as SL_2, isnull(SL_3, 0) as SL_3,0 AS SL_4 from DBJRDMX where DJBH='CD1000013' union all select SPDM, GG1DM, GG2DM, 0, 0, 0,isnull(D.SL, 0) from DSEND as M inner join DSENDMX as D on M.DJBH=D.DJBH where M.LXDJ='CD1000013' and (M.JS=1 AND M.DJBH>='CD2000065' ) union all select SPDM, GG1DM, GG2DM, 0, isnull(D.SL, 0), 0,0 from DSEND as M inner join DSENDMX as D on M.DJBH=D.DJBH where M.LXDJ='CD1000013' and (isnull(M.SP, 0)=0 and isnull(M.YS, 0)=1 AND M.DJBH<'CD2000065') ) as B group by SPDM, GG1DM, GG2DM ) as C where SPDM=A.SPDM and GG1DM=A.GG1DM and GG2DM=A.GG2DM and SL-isnull(SL_2, 0)-isnull(SL_3, 0)+isnull(SL_4, 0)<isnull(A.SL, 0)))
--查看颜色
SELECT GUIGE1.GGDM,GUIGE1.GGMC FROM SPGG1,GUIGE1 WHERE SPGG1.GGDM=GUIGE1.GGDM AND SPGG1.SPDM='Q122320088' ORDER BY GUIGE1.GGDM
--查看款对应的尺码
SELECT GUIGE2.GGDM,GUIGE2.GGMC,GUIGE2.GGWZ2 FROM SPGG2,GUIGE2 WHERE SPGG2.GGDM=GUIGE2.GGDM AND SPGG2.SPDM='Q122320088' ORDER BY GUIGE2.GGWZ2

select vUserCode,vFuncID,vTabName,bVisible,iIndex,iFocus,iDate from UserQtyConsult where vUserCode='Admin' and vFuncID='80402002'

--调价单
QDTJDMH
SELECT * FROM QDTJDMH
SELECT * FROM QDTJD WHERE DJBH='BD2000038'

--调价单-记账 JZ
UPDATE QDTJD SET JZ='0' WHERE DJBH='BD2000038'

--调价单单据日期 RQ
2011-07-23 00:00:00.000
UPDATE QDTJD SET RQ='2011-05-01 00:00:00.000' WHERE DJBH='BD2000038'

--调价单记账日期 JZRQ
UPDATE QDTJD SET JZRQ='2011-05-01 00:00:00.000' WHERE DJBH='BD2000038'

--调价单调价启用日期 RQ_1
UPDATE QDTJD SET RQ_1='2011-05-01 00:00:00.000' WHERE DJBH='BD2000038'

--调价单调价终止日期 RQ_2
UPDATE QDTJD SET RQ_2='2099-05-01 00:00:00.000' WHERE DJBH='BD2000038'

--调价单-确认日期 BYZD14
UPDATE QDTJD SET RQ_1='2011-05-01 00:00:00.000' WHERE DJBH='BD2000038'

UPDATE QDTJD SET LXDJ='010001' WHERE DJBH='BD2000023'

Q122320356
CKJ:吊牌价
DJ:折后价

SELECT QUDAO.QDMC , CANGKU.CKMC , VW_DBJRDMX.DJBH , VW_DBJRDMX.RQ , datepart(year,RQ) , datepart(quarter,RQ) , str(datepart(month,RQ)/10,1)+str(datepart(month,RQ)%10,1) , str(datepart(week,RQ)/10,1)+str(datepart(week,RQ)%10,1) , VW_DBJRDMX.YDJH , QUYU.QYDM , SHANGPIN.SPDM , QUYU.QYMC , VW_DBJRDMX.QDDM , CANGKU.CKDM , KEHU.QYDM , A.QYMC , SHANGPIN.SPMC , SHANGPIN.ZJF , SHANGPIN.SPBM , GONGHUOSHANG.GHSDM , GONGHUOSHANG.GHSMC , DDLX.DDDM , DDMC , ZDR , VW_DBJRDMX.ZY , SHANGPIN.FJSX1 , FJSX1.SXMC , SHANGPIN.FJSX2 , FJSX2.SXMC , SHANGPIN.FJSX3 , FJSX3.SXMC , SHANGPIN.FJSX4 , FJSX4.SXMC , SHANGPIN.FJSX5 , FJSX5.SXMC , SHANGPIN.FJSX6 , FJSX6.SXMC , SHANGPIN.FJSX7 , FJSX7.SXMC , SHANGPIN.FJSX8 , FJSX8.SXMC , SHANGPIN.FJSX9 , FJSX9.SXMC , SHANGPIN.FJSX10 , FJSX10.SXMC , VW_DBJRDMX.CKJ , ROUND(VW_DBJRDMX.ZK,2) , VW_DBJRDMX.DJ , Sum(ABS(VW_DBJRDMX.SL)) , Sum(ABS(VW_DBJRDMX.SL_3N)) , Sum(ABS(VW_DBJRDMX.SL_3)) , Sum(ABS(VW_DBJRDMX.SL_1)) , Sum(SL_2) , Sum(VW_DBJRDMX.SL_2N) , Sum(ABS(VW_DBJRDMX.JE)) , Sum(JE_3N) , Sum(ABS(VW_DBJRDMX.JE_3N)) , Sum(JE_1) , Sum(ABS(VW_DBJRDMX.JE_2)) , Sum(VW_DBJRDMX.JE_2N) FROM VW_DBJRDMX left join DDLX on VW_DBJRDMX.LXDM = DDLX.DDDM,SHANGPIN LEFT JOIN GONGHUOSHANG ON SHANGPIN.vDefine5=GONGHUOSHANG.GHSDM,QUDAO,QUYU,CANGKU,KEHU,QUYU A ,FJSX1 ,FJSX2 ,FJSX3 ,FJSX4 ,FJSX5 ,FJSX6 ,FJSX7 ,FJSX8 ,FJSX9 ,FJSX10 WHERE VW_DBJRDMX.SPDM=SHANGPIN.SPDM AND VW_DBJRDMX.QDDM=QUDAO.QDDM AND QUDAO.QYDM=QUYU.QYDM AND VW_DBJRDMX.DM1=CANGKU.CKDM AND VW_DBJRDMX.DM1=KEHU.MRCK AND KEHU.QYDM=A.QYDM AND SHANGPIN.FJSX1=FJSX1.SXDM AND SHANGPIN.FJSX2=FJSX2.SXDM AND SHANGPIN.FJSX3=FJSX3.SXDM AND SHANGPIN.FJSX4=FJSX4.SXDM AND SHANGPIN.FJSX5=FJSX5.SXDM AND SHANGPIN.FJSX6=FJSX6.SXDM AND SHANGPIN.FJSX7=FJSX7.SXDM AND SHANGPIN.FJSX8=FJSX8.SXDM AND SHANGPIN.FJSX9=FJSX9.SXDM AND SHANGPIN.FJSX10=FJSX10.SXDM AND (VW_DBJRDMX.JZ = '1') AND VW_DBJRDMX.DJXZ = 15 AND (VW_DBJRDMX.DM1 in ('512002')) AND (VW_DBJRDMX.LXDM in ('001')) GROUP BY QUDAO.QDMC, CANGKU.CKMC, VW_DBJRDMX.DJBH, VW_DBJRDMX.RQ, datepart(year,RQ), datepart(quarter,RQ), str(datepart(month,RQ)/10,1)+str(datepart(month,RQ)%10,1), str(datepart(week,RQ)/10,1)+str(datepart(week,RQ)%10,1), VW_DBJRDMX.YDJH, QUYU.QYDM, SHANGPIN.SPDM, QUYU.QYMC, VW_DBJRDMX.QDDM, CANGKU.CKDM, KEHU.QYDM, A.QYMC, SHANGPIN.SPMC, SHANGPIN.ZJF, SHANGPIN.SPBM, GONGHUOSHANG.GHSDM, GONGHUOSHANG.GHSMC, DDLX.DDDM, DDMC, ZDR, VW_DBJRDMX.ZY, SHANGPIN.FJSX1, FJSX1.SXMC, SHANGPIN.FJSX2, FJSX2.SXMC, SHANGPIN.FJSX3, FJSX3.SXMC, SHANGPIN.FJSX4, FJSX4.SXMC, SHANGPIN.FJSX5, FJSX5.SXMC, SHANGPIN.FJSX6, FJSX6.SXMC, SHANGPIN.FJSX7, FJSX7.SXMC, SHANGPIN.FJSX8, FJSX8.SXMC, SHANGPIN.FJSX9, FJSX9.SXMC, SHANGPIN.FJSX10, FJSX10.SXMC, VW_DBJRDMX.CKJ, ROUND(VW_DBJRDMX.ZK,2), VW_DBJRDMX.DJ ORDER BY QUDAO.QDMC, CANGKU.CKMC, VW_DBJRDMX.DJBH, VW_DBJRDMX.RQ, datepart(year,RQ), datepart(quarter,RQ), str(datepart(month,RQ)/10,1)+str(datepart(month,RQ)%10,1), str(datepart(week,RQ)/10,1)+str(datepart(week,RQ)%10,1), VW_DBJRDMX.YDJH, QUYU.QYDM, SHANGPIN.SPDM, QUYU.QYMC, VW_DBJRDMX.QDDM, CANGKU.CKDM, KEHU.QYDM, A.QYMC, SHANGPIN.SPMC, SHANGPIN.ZJF, SHANGPIN.SPBM, GONGHUOSHANG.GHSDM, GONGHUOSHANG.GHSMC, DDLX.DDDM, DDMC, ZDR, VW_DBJRDMX.ZY, SHANGPIN.FJSX1, FJSX1.SXMC, SHANGPIN.FJSX2, FJSX2.SXMC, SHANGPIN.FJSX3, FJSX3.SXMC, SHANGPIN.FJSX4, FJSX4.SXMC, SHANGPIN.FJSX5, FJSX5.SXMC, SHANGPIN.FJSX6, FJSX6.SXMC, SHANGPIN.FJSX7, FJSX7.SXMC, SHANGPIN.FJSX8, FJSX8.SXMC, SHANGPIN.FJSX9, FJSX9.SXMC, SHANGPIN.FJSX10, FJSX10.SXMC, VW_DBJRDMX.CKJ, ROUND(VW_DBJRDMX.ZK,2), VW_DBJRDMX.DJ
SELECT * FROM VW_DBJRDMX WHERE DJBH='cd1000006';
SELECT * FROM FJSX1
VW_DBJRDMX.DJ 主表
Sum(ABS(VW_DBJRDMX.SL)) 订单数 ,
Sum(ABS(VW_DBJRDMX.SL_3N)) ,有效数
Sum(ABS(VW_DBJRDMX.SL_3)) ,终止数
Sum(ABS(VW_DBJRDMX.SL_1)) ,通知数
Sum(SL_2) 完工数
, Sum(VW_DBJRDMX.SL_2N) ,未完工数
Sum(ABS(VW_DBJRDMX.JE)) , Sum(JE_3N) , Sum(ABS(VW_DBJRDMX.JE_3N)) , Sum(JE_1) , Sum(ABS(VW_DBJRDMX.JE_2)) , Sum(VW_DBJRDMX.JE_2N) FROM VW_DBJRDMX left join DDLX on VW_DBJRDMX.LXDM = DDLX.DDDM,SHANGPIN LEFT JOIN

SELECT Sum(SL_2) FROM VW_DBJRDMX WHERE DJBH='cd1000006';

SELECT VW_DBJRDMX.DJBH ,VW_DBJRDMX.SL,SL_2,VW_DBJRDMX.SL_2N FROM VW_DBJRDMX WHERE DJBH='CD1000006';

SELECT * FROM VW_DBJRDMX WHERE DJBH='cd1000006';

SELECT VW_DBJRDMX.DJBH ,VW_DBJRDMX.SL,SL_2,VW_DBJRDMX.SL_2N FROM VW_DBJRDMX WHERE DJBH='CD1000006';

UPDATE VW_DBJRDMX SET SL_2='4',SL_2N='1' WHERE SPDM='Q122210017', GG1DM='51',GG2DM='12'

--
SELECT * FROM QDNPD

UPDATE QDNPD SET SH='0',JS='0',SP='0' WHERE DJBH='SA1000001';

SELECT * FROM SPYCDMX
DELETE FROM SPYCDMX WHERE DJBH='YC3000126';
DELETE FROM SPYCD WHERE DJBH='YC3000126';