588
社区成员
发帖
与我相关
我的任务
分享
(SELECT DISTINCT fitemid,fnumber,编号 FROM a3)
ALTER PROC [dbo].[p_yb]
AS
DECLARE @name VARCHAR(100);
DECLARE @sql VARCHAR(2000);
DECLARE table_import CURSOR
FOR
SELECT DISTINCT 课程名称
FROM a3;
OPEN table_import;
FETCH NEXT FROM table_import INTO @name;--匹配第一条记录
WHILE ( @@FETCH_STATUS = 0 )-- 读取游标状态,循环
BEGIN
SET @sql = 'INSERT INTO t_item
( FItemID ,
FItemClassID ,
FExternID ,
FNumber ,
FParentID ,
FLevel ,
FDetail ,
FName ,
FUnUsed ,
FBrNo ,
FFullNumber ,
FDiff ,
FDeleted ,
FShortNumber ,
FFullName ,
UUID ,
FGRCommonID ,
FSystemType ,
FUseSign ,
FChkUserID ,
FAccessory ,
FGrControl ,
FHavePicture
)
SELECT fitemid + ROW_NUMBER() OVER ( ORDER BY 编号 ) ,
3003 ,
-1 ,
RIGHT(''000''
+ CAST(fnumber + ROW_NUMBER() OVER ( ORDER BY 编号 ) AS VARCHAR),
3) ,
0 ,
1 ,
1 ,
'''+@name+''' ,
0 ,
0 ,
RIGHT(''000''
+ CAST(fnumber + ROW_NUMBER() OVER ( ORDER BY 编号 ) AS VARCHAR),
3) ,
0 ,
0 ,
RIGHT(''000''
+ CAST(fnumber + ROW_NUMBER() OVER ( ORDER BY 编号 ) AS VARCHAR),
3) ,
'''+@name+''' ,
NEWID() ,
-1 ,
1 ,
0 ,
NULL ,
0 ,
-1 ,
0
FROM (SELECT DISTINCT fitemid,fnumber,编号 FROM a3)AS a3
CROSS JOIN ( SELECT MAX(fitemid) AS fitemid
FROM t_item
) AS B
CROSS JOIN ( SELECT MAX(CAST(fnumber AS INT)) AS fnumber
FROM t_item_3003
) AS c ;'
;
PRINT ( @sql );
EXEC(@sql);
FETCH NEXT FROM table_import INTO @name;
END;
ALTER PROC [dbo].[p_yb]
AS
declare @name varchar(100)
declare @sql varchar(2000)
declare table_import cursor for
SELECT distinct 课程名称 FROM a3
open table_import
fetch next from table_import into @name--匹配第一条记录
while (@@FETCH_STATUS=0)-- 读取游标状态,循环
begin
set @sql='
insert into t_item(FItemID,FItemClassID,FExternID,FNumber,FParentID,FLevel,FDetail,FName,FUnUsed,FBrNo,FFullNumber,FDiff,FDeleted
,FShortNumber,FFullName,UUID,FGRCommonID,FSystemType,FUseSign,FChkUserID,FAccessory,FGrControl,FHavePicture)
select fitemid+1,
3003,-1,
right(''000''+CAST(fnumber+1 as varchar),3),
0,1,1,
'''+@name+''',
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
'''+@name+''',
NEWID(),-1,1,0,null,0,-1,0
from a3
join (select MAX(fitemid) as fitemid FROM t_item) as B on 1=1
join (select MAX(cast(fnumber as int)) as fnumber from t_item_3003) as c on 1=1
'
print(@sql)
exec(@sql)
fetch next from table_import into @name
END
我的建议是直接把row_number改成1。其实我更觉得没必要加这个1,因为每个课程就一条记录的话,加不加有啥区别。也就是直接去掉这个row_number()就可以了[/quote]
第一个字段可以不用,但是其他的字段还是要用,而且按着你这样执行,查询出来的结果是两条一样的值,还是不行
[/quote]
有点被你搞蒙了,既然你说每个课程只要一条记录,对吧。那你用这个row_number() over(partition by 编号)有什么作用呢,你希望这个row_number() over(partition by 编号)出来的值是多少啊,它永远都是从1开始到你的编号结束,加入你整个表有四条记录,那这个row_number() over(partition by 编号)就会是1到4,你查询出来的结果也就是四条。也就是你插入的结果。但是你又说要一条。具体你需要做什么呢,达成什么效果。建议给点测试数据。你下面给的表截图数据,我说的我也不是很明白。你说fnumber是每个表的独立的,可是我看这个值也有很多重复的啊
ALTER PROC [dbo].[p_yb]
AS
declare @name varchar(100)
declare @sql varchar(2000)
declare table_import cursor for
SELECT distinct 课程名称 FROM a3
open table_import
fetch next from table_import into @name--匹配第一条记录
while (@@FETCH_STATUS=0)-- 读取游标状态,循环
begin
set @sql='
insert into t_item(FItemID,FItemClassID,FExternID,FNumber,FParentID,FLevel,FDetail,FName,FUnUsed,FBrNo,FFullNumber,FDiff,FDeleted
,FShortNumber,FFullName,UUID,FGRCommonID,FSystemType,FUseSign,FChkUserID,FAccessory,FGrControl,FHavePicture)
select fitemid+1,
3003,-1,
right(''000''+CAST(fnumber+1 as varchar),3),
0,1,1,
'''+@name+''',
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
'''+@name+''',
NEWID(),-1,1,0,null,0,-1,0
from a3
join (select MAX(fitemid) as fitemid FROM t_item) as B on 1=1
join (select MAX(cast(fnumber as int)) as fnumber from t_item_3003) as c on 1=1
'
print(@sql)
exec(@sql)
fetch next from table_import into @name
END
我的建议是直接把row_number改成1。其实我更觉得没必要加这个1,因为每个课程就一条记录的话,加不加有啥区别。也就是直接去掉这个row_number()就可以了[/quote]
第一个字段可以不用,但是其他的字段还是要用,而且按着你这样执行,查询出来的结果是两条一样的值,还是不行
ALTER PROC [dbo].[p_yb]
AS
declare @name varchar(100)
declare @sql varchar(2000)
declare table_import cursor for
SELECT distinct 课程名称 FROM a3
open table_import
fetch next from table_import into @name--匹配第一条记录
while (@@FETCH_STATUS=0)-- 读取游标状态,循环
begin
set @sql='
insert into t_item(FItemID,FItemClassID,FExternID,FNumber,FParentID,FLevel,FDetail,FName,FUnUsed,FBrNo,FFullNumber,FDiff,FDeleted
,FShortNumber,FFullName,UUID,FGRCommonID,FSystemType,FUseSign,FChkUserID,FAccessory,FGrControl,FHavePicture)
select fitemid+1,
3003,-1,
right(''000''+CAST(fnumber+1 as varchar),3),
0,1,1,
'''+@name+''',
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
0,0,
right(''000''+CAST(fnumber+1 as varchar),3),
'''+@name+''',
NEWID(),-1,1,0,null,0,-1,0
from a3
join (select MAX(fitemid) as fitemid FROM t_item) as B on 1=1
join (select MAX(cast(fnumber as int)) as fnumber from t_item_3003) as c on 1=1
'
print(@sql)
exec(@sql)
fetch next from table_import into @name
END
我的建议是直接把row_number改成1。其实我更觉得没必要加这个1,因为每个课程就一条记录的话,加不加有啥区别。也就是直接去掉这个row_number()就可以了
declare table_import cursor for
SELECT distinct 课程名称 FROM a3
在创建游标的时候加一个distinct