存储过程给2017年以前的人加一个人员编号
0
所有回答(2)
0
1、查出2017年前的数据到临时表
2、给临时表加RowNumber
3、用游标遍历临时表，用RowNumber去凑一个流水号。
4、以上具体语法自己找资料。可参考 http://www.cnblogs.com/chengeng/p/4449971.html
看不明白
没有接触过，所以看得很头疼
@君九彧:
SELECT row_number() over(order by CREATETIME DESC) as RowNumber
--,'' AS StaffCode
,USERID,CREATETIME INTO #YUSER FROM YUSER
where len(USERID)=32
alter table #YUSER add StaffCode varchar(500) default ''
DECLARE @USERID VARCHAR(500)
DECLARE @CREATETIME VARCHAR(500)
DECLARE @StaffCode VARCHAR(500)
DECLARE myCursor CURSOR FOR(SELECT USERID,CREATETIME,StaffCode FROM #YUSER)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @USERID,@CREATETIME,@StaffCode
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE #YUSER
SET StaffCode=substring(CREATETIME,0,5) + cast(RowNumber as varchar(10))
WHERE USERID=@USERID
FETCH NEXT FROM myCursor INTO @USERID,@CREATETIME,@StaffCode
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #YUSER
drop table #YUSER
SELECT row_number() over(order by CREATETIME DESC) as RowNumber
--,'' AS StaffCode
,USERID,CREATETIME INTO #YUSER FROM YUSER
where len(USERID)=32
alter table #YUSER add StaffCode varchar(500) default ''
DECLARE @USERID VARCHAR(500)
DECLARE @CREATETIME VARCHAR(500)
DECLARE @StaffCode VARCHAR(500)
DECLARE myCursor CURSOR FOR(SELECT USERID,CREATETIME,StaffCode FROM #YUSER)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @USERID,@CREATETIME,@StaffCode
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE #YUSER
SET StaffCode=substring(CREATETIME,0,5) + cast(RowNumber as varchar(10))
WHERE USERID=@USERID
FETCH NEXT FROM myCursor INTO @USERID,@CREATETIME,@StaffCode
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #YUSER
drop table #YUSER
@君九彧: 
0
USE [数据库名] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[存储过程名] 参数的定义 AS BEGIN SET NOCOUNT ON; sql语句 END GO
这里的dbo是什么？
存储过程名是随意的吧？
sql语句我加
update Jd_Xyjbxx t
set rybm =(select to_char(tbsj,'yyyy') || lpad(lsh,5,'0') from (
select rowid rid,
row_number() over(partition by tbsj order by sysdate) lsh
from jd_xyjbxx t
) where t.rowid = rid
可以吗
@君九彧: dbo是前缀，可以不写；存储过程名字随便起；sql语句出写你要的sql就行了
