--临时接收表
select * from TEORD
/*SDDATE SDREN KHNAME KHADDESS KHTEll KHTOPO SKUNAME SKU CQTY ZQTY JQTY
2013-01-17 00:00:00.000 曹宏 广外 白云区白云大道北2号 卢珍 362079 11 04B 2 NULL 2
2013-01-17 00:00:00.000 罗平 天意 广州市天河区五山路科 梁想 137517451 11 A0050 4 NULL 4
2013-01-17 00:00:00.000 曹宏 华思 花都区汽车城东冈 张美 136320491 N2 N2 2 NULL 2
2013-01-17 00:00:00.000 曹宏 华思 花都区汽车城东冈 张美 136322491 N21 N21 1 NULL 1
2013-01-17 00:00:00.000 曹宏 耐路 花都区汽车城岭西 利衡 159158148 TN21 A1191 2 NULL 2
2013-01-17 00:00:00.000 陈熹 嘉可 建设六马路33号宜安 黄姐 836335 TN27 A2051 1 NULL 1
2013-01-17 00:00:00.000 吴斌 达亿 荔湾区浣花路109号东鹏德宝商务中 李媛 815078 粉 A0X82 1 NULL 1
2013-01-17 00:00:00.000 林应 茂彩 茂名市迎宾二路137号大院 江劲 NULL 废 A00200 1 NULL 1
2013-01-17 00:00:00.000 侯青 珍告 体育西路103号维多利广场A 彭姐 381015 TN21 NULL 1 NULL 1
2013-01-17 00:00:00.000 侯威 东丽 天河区天河北路233号中信广场写 郭生 875212 TN21 A0D2 2 NULL 2
2013-01-17 00:00:00.000 侯威 东丽 天河区天河北路233号中信广场写 郭生 875212 TN21C A0D2 1 NULL 1*/
--查询临时TEORD表插入订单表
insert into ORD select '生成订单号',KHNAME,KHADDESS,KHTEll,KHTOPO from TEORD group by KHNAME,KHADDESS,KHTEll,KHTOPO ORDER BY KHNAME
--查询临时TEORD表插入明细表
insert into ORDT select '生成订单号',KHNAME,SKUNAME,SKU,SUM(CQTY) from TEORD group by KHNAME,SKUNAME,SKU ORDER BY KHNAME--相同KHNAME的生成同样的"订单号","生成订单号"规则KC+年+月+日+4位流水号(例如:KC130117001)
--最后得出如下结果
/*
订单表
生成订单号 KHNAME KHADDESS KHTEll KHTOPO
KC130117001 达亿 荔湾区浣花路109号东鹏德宝商务中 李媛 815078
KC130117002 东丽 天河区天河北路233号中信广场写 郭生 875212
KC130117003 广外 白云区白云大道北2号 卢珍 362079
KC130117004 华思 花都区汽车城东冈 张美 136320491
KC130117005 嘉可 建设六马路33号宜安 黄姐 836335
KC130117006 茂彩 茂名市迎宾二路137号大院 江劲 NULL
KC130117007 耐路 花都区汽车城岭西 利衡 159158148
KC130117008 天意 广州市天河区五山路科 梁想 137517451
KC130117009 珍告 体育西路103号维多利广场A 彭姐 381015
明细表
生成订单号 KHNAME SKUNAME SKU (无列名)
KC130117001 达亿 粉 A0X82 1
KC130117002 东丽 TN21 A0D2 2
KC130117002 东丽 TN21C A0D2 1
KC130117003 广外 11 04B 2
KC130117004 华思 N2 N2 2
KC130117004 华思 N21 N21 1
KC130117005 嘉可 TN27 A2051 1
KC130117006 茂彩 废 A00200 1
KC130117007 耐路 TN21 A1191 2
KC130117008 天意 11 A0050 4
KC130117009 珍告 TN21 NULL 1*/
求相同KHNAME的生成同样的"订单号","生成订单号"规则KC+年+月+日+4位流水号(例如:KC130117001),求代码,菜鸟,最后是存储过程,他这个是半小时接收一次数据!,先谢过!
SET @a='2013-01-17'SELECT 'KC'+SUBSTRING(CONVERT(varchar(20), @a, 112),3,LEN(@a))+LEFT('000'+CONVERT(VARCHAR(4),ROW_NUMBER()OVER(ORDER BY GETDATE())),4)
select 'KC'+convert(varchar(12),[SDDATE],112)+right(10000+row_number()over(order by KHNAME),4),
KHNAME,KHADDESS,KHTEll,KHTOPO
from #TEORD
group by convert(varchar(12),[SDDATE],112),KHNAME,KHADDESS,KHTEll,KHTOPO
ORDER BY KHNAME
怎么样生成不重复的单号,求代码,SQl菜鸟,求一个存储过程.
不改变表顺序use Tempdb
go
--> -->
declare @T table([SDDATE] Datetime,[SDREN] nvarchar(2),[KHNAME] nvarchar(3),[KHADDESS] nvarchar(17),[KHTEll] nvarchar(2),[KHTOPO] int,[SKUNAME] nvarchar(4),[SKU] nvarchar(6),[CQTY] int,[ZQTYJQTY] int)
Insert @T
select '2013-01-17',N'曹宏',N'广外',N'白云区白云大道北2号',N'卢珍',362079,N'11',N'04B',2,2 union all
select '2013-01-17',N'罗平',N'天意',N'广州市天河区五山路科',N'梁想',137517451,N'11',N'A0050',4,4 union all
select '2013-01-17',N'曹宏',N'华思',N'花都区汽车城东冈',N'张美',136320491,N'N2',N'N2',2,2 union all
select '2013-01-17',N'曹宏',N'华思',N'花都区汽车城东冈',N'张美',136322491,N'N21',N'N21',1,1 union all
select '2013-01-17',N'曹宏',N'耐路',N'花都区汽车城岭西',N'利衡',159158148,N'TN21',N'A1191',2,2 union all
select '2013-01-17',N'陈熹',N'嘉可',N'建设六马路33号宜安',N'黄姐',836335,N'TN27',N'A2051',1,1 union all
select '2013-01-17',N'吴斌',N'达亿',N'荔湾区浣花路109号东鹏德宝商务中',N'李媛',815078,N'粉',N'A0X82',1,1 union all
select '2013-01-17',N'林应',N'茂彩',N'茂名市迎宾二路137号大院',N'江劲',null,N'废',N'A00200',1,1 union all
select '2013-01-17',N'侯青',N'珍告',N'体育西路103号维多利广场A',N'彭姐',381015,N'TN21',null,1,1 union all
select '2013-01-17',N'侯威',N'东丽',N'天河区天河北路233号中信广场写',N'郭生',875212,N'TN21',N'A0D2',2,2 union all
select '2013-01-17',N'侯威',N'东丽',N'天河区天河北路233号中信广场写',N'郭生',875212,null,null,null,null
;with c
as
(select ID=ROW_NUMBER()over(order by getdate()),* from @T)
select 'KC'+convert(varchar(6),[SDDATE],12)+right(1000+ROW_NUMBER()over(partition by convert(varchar(6),[SDDATE],12) order by ID),3) as OrderNo,KHNAME,KHADDESS,KHTEll,KHTOPO
into ORD
from c as a
where not exists(select 1 from c where KHNAME=a.KHNAME and KHADDESS=a.KHADDESS and KHTEll=a.KHTEll and [SDDATE]=a.[SDDATE] and ID<a.ID)select * from ORD
/*
OrderNo KHNAME KHADDESS KHTEll KHTOPO
KC130117001 广外 白云区白云大道北2号 卢珍 362079
KC130117002 天意 广州市天河区五山路科 梁想 137517451
KC130117003 华思 花都区汽车城东冈 张美 136320491
KC130117004 耐路 花都区汽车城岭西 利衡 159158148
KC130117005 嘉可 建设六马路33号宜安 黄姐 836335
KC130117006 达亿 荔湾区浣花路109号东鹏德宝商务中 李媛 815078
KC130117007 茂彩 茂名市迎宾二路137号大院 江劲 NULL
KC130117008 珍告 体育西路103号维多利广场A 彭姐 381015
KC130117009 东丽 天河区天河北路233号中信广场写 郭生 875212
*/select b.OrderNo,a.KHNAME,a.SKUNAME,a.SKU,SUM(a.CQTY) as CQTY
into TEORD
from @T as a
inner join ORD as b on a.KHNAME=b.KHNAME
group by b.OrderNo,a.KHNAME,a.SKUNAME,a.SKUselect * from TEORD
/*
OrderNo KHNAME SKUNAME SKU CQTY
KC130117001 广外 11 04B 2
KC130117002 天意 11 A0050 4
KC130117003 华思 N2 N2 2
KC130117003 华思 N21 N21 1
KC130117004 耐路 TN21 A1191 2
KC130117005 嘉可 TN27 A2051 1
KC130117006 达亿 粉 A0X82 1
KC130117007 茂彩 废 A00200 1
KC130117008 珍告 TN21 NULL 1
KC130117009 东丽 NULL NULL NULL
KC130117009 东丽 TN21 A0D2 2
*/
-增加一出库单号表
/*ID 简写 日期 流水号
ID ASAID DDATE RUNNO
1 KC 2013/1/18 1
2 KC 2013/1/18 2
*/
use test
go
--> -->
declare @T table([出库单号] nvarchar(11),[SDDATE] Datetime,[SDREN] nvarchar(2),[KHNAME] nvarchar(3),[KHADDESS] nvarchar(17),[KHTEll] nvarchar(2),[KHTOPO] int,[SKUNAME] nvarchar(4),[SKU] nvarchar(6),[CQTY] int,[ZQTYJQTY] int)
Insert @T
select '','2013-01-17',N'曹宏',N'广外',N'白云区白云大道北2号',N'卢珍',362079,N'11',N'04B',2,2 union all
select '','2013-01-17',N'罗平',N'天意',N'广州市天河区五山路科',N'梁想',137517451,N'11',N'A0050',4,4 union all
select '','2013-01-17',N'曹宏',N'华思',N'花都区汽车城东冈',N'张美',136320491,N'N2',N'N2',2,2 union all
select '','2013-01-17',N'曹宏',N'华思',N'花都区汽车城东冈',N'张美',136322491,N'N21',N'N21',1,1 union all
select '','2013-01-17',N'曹宏',N'耐路',N'花都区汽车城岭西',N'利衡',159158148,N'TN21',N'A1191',2,2 union all
select '','2013-01-17',N'陈熹',N'嘉可',N'建设六马路33号宜安',N'黄姐',836335,N'TN27',N'A2051',1,1 union all
select '','2013-01-17',N'吴斌',N'达亿',N'荔湾区浣花路109号东鹏德宝商务中',N'李媛',815078,N'粉',N'A0X82',1,1 union all
select '','2013-01-17',N'林应',N'茂彩',N'茂名市迎宾二路137号大院',N'江劲',null,N'废',N'A00200',1,1 union all
select '','2013-01-17',N'侯青',N'珍告',N'体育西路103号维多利广场A',N'彭姐',381015,N'TN21',null,1,1 union all
select '','2013-01-17',N'侯威',N'东丽',N'天河区天河北路233号中信广场写',N'郭生',875212,N'TN21',N'A0D2',2,2 union all
select '','2013-01-17',N'侯威',N'东丽',N'天河区天河北路233号中信广场写',N'郭生',875212,null,null,null,null
select * from @T根据流水号表中当天当最大的单号,直接在TEORD表生成"也库单号",生成单号之后插入到流水号表,还请大牛们帮帮忙!
;WITH t AS (
SELECT
DENSE_RANK()OVER(ORDER BY KHNAME ASC) AS row
,KHNAME
FROM TEORD
)
--insert into ORD
select 'KC'+RIGHT(CONVERT(VARCHAR(8),a.SDDATE,112),6)+RIGHT(1000+t.row,3)'生成订单号',a.KHNAME,a.KHADDESS,a.KHTEll,a.KHTOPO from TEORD AS a,t WHERE a.KHNAME=t.KHNAME group by a.SDDATE,a.KHNAME,a.KHADDESS,a.KHTEll,a.KHTOPO,t.row ORDER BY a.KHNAME--查询临时TEORD表插入明细表
;WITH t AS (
SELECT
DENSE_RANK()OVER(ORDER BY KHNAME ASC) AS row
,KHNAME
FROM TEORD
)
--insert into ORDT
select 'KC'+RIGHT(CONVERT(VARCHAR(8),a.SDDATE,112),6)+RIGHT(1000+t.row,3)'生成订单号',a.KHNAME,a.SKUNAME,a.SKU,SUM(CQTY) AS CQTY from TEORD AS a,t WHERE a.KHNAME=t.KHNAME group by a.SDDATE,a.KHNAME,a.SKUNAME,a.SKU,t.row ORDER BY a.KHNAME/*生成订单号 KHNAME KHADDESS KHTEll KHTOPO
---------------- ------ ----------------- ------ -----------
KC130117001 天意 广州市天河区五山路科 梁想 137517451
KC130117002 珍告 体育西路103号维多利广场A 彭姐 381015
KC130117003 耐路 花都区汽车城岭西 利衡 159158148
KC130117004 茂彩 茂名市迎宾二路137号大院 江劲 NULL
KC130117005 嘉可 建设六马路33号宜安 黄姐 836335
KC130117006 东丽 天河区天河北路233号中信广场写 郭生 875212
KC130117007 华思 花都区汽车城东冈 张美 136320491
KC130117008 广外 白云区白云大道北2号 卢珍 362079
KC130117009 达亿 荔湾区浣花路109号东鹏德宝商务中 李媛 815078
生成订单号 KHNAME SKUNAME SKU CQTY
---------------- ------ ------- ------ -----------
KC130117001 天意 11 A0050 4
KC130117002 珍告 TN21 NULL 1
KC130117003 耐路 TN21 A1191 2
KC130117004 茂彩 废 A00200 1
KC130117005 嘉可 TN27 A2051 1
KC130117006 东丽 TN21 A0D2 4
KC130117006 东丽 TN21C A0D2 2
KC130117007 华思 N2 N2 4
KC130117007 华思 N21 N21 2
KC130117008 广外 11 04B 2
KC130117009 达亿 粉 A0X82 1
*/
经测试你的CODE是可以,但有一个问题,当处理完一批次数据之后,在处理另一批数据时,生成单号重复,还请帮忙看看.能否加一下你的QQ,我的QQ是1276615111
;WITH t AS (
SELECT
DENSE_RANK()OVER(ORDER BY KHNAME ASC)+ISNULL(b.row_cnt,0) AS row
,KHNAME
FROM TEORD AS a
LEFT JOIN (
SELECT
COUNT(DISTINCT 生成订单号) AS row_cnt
,CAST('20'+SUBSTRING(生成订单号,3,6) AS DATETIME) AS SDDATE
FROM ORD
GROUP BY SUBSTRING(生成订单号,3,6)
) AS b ON a.SDDATE=b.SDDATE
)
--insert into ORD
select 'KC'+RIGHT(CONVERT(VARCHAR(8),a.SDDATE,112),6)+RIGHT(1000+t.row,3)'生成订单号',a.KHNAME,a.KHADDESS,a.KHTEll,a.KHTOPO from TEORD AS a,t WHERE a.KHNAME=t.KHNAME group by a.SDDATE,a.KHNAME,a.KHADDESS,a.KHTEll,a.KHTOPO,t.row ORDER BY a.KHNAME--查询临时TEORD表插入明细表
;WITH t AS (
SELECT
DENSE_RANK()OVER(ORDER BY KHNAME ASC)+ISNULL(b.row_cnt,0) AS row
,KHNAME
FROM TEORD AS a
LEFT JOIN (
SELECT
COUNT(DISTINCT 生成订单号) AS row_cnt
,CAST('20'+SUBSTRING(生成订单号,3,6) AS DATETIME) AS SDDATE
FROM ORDT
GROUP BY SUBSTRING(生成订单号,3,6)
) AS b ON a.SDDATE=b.SDDATE
)
--insert into ORDT
select 'KC'+RIGHT(CONVERT(VARCHAR(8),a.SDDATE,112),6)+RIGHT(1000+t.row,3)'生成订单号',a.KHNAME,a.SKUNAME,a.SKU,SUM(CQTY) AS CQTY from TEORD AS a,t WHERE a.KHNAME=t.KHNAME group by a.SDDATE,a.KHNAME,a.SKUNAME,a.SKU,t.row ORDER BY a.KHNAME