一个考生表,现在要随机生成考号
表结构如下:
USE
[
WZ16
]
GO
/**/ /****** 对象: Table [dbo].[Jwc_Kaosheng] 脚本日期: 11/13/2007 01:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ Jwc_Kaosheng ] (
[ KaoshengId ] [ bigint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ KaoshiId ] [ bigint ] NOT NULL ,
[ BanjiStudentId ] [ bigint ] NOT NULL ,
[ KaoHao ] [ int ] NULL ,
[ ShiChangNum ] [ nvarchar ] ( 20 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ZuoweiHao ] [ int ] NULL ,
[ Zongfen ] [ float ] NULL ,
[ TScore ] [ float ] NULL ,
[ Dengdi ] [ nvarchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ClassMingci ] [ bigint ] NULL ,
[ ClassMingciAdd ] [ bigint ] NULL ,
[ SchoolMingci ] [ bigint ] NULL ,
[ SchoolMingciAdd ] [ bigint ] NULL ,
[ AllMingci ] [ bigint ] NULL ,
[ AllMingciAdd ] [ bigint ] NULL ,
[ Active ] [ bit ] NULL CONSTRAINT [ DF_Jwc_Kaosheng_Active ] DEFAULT (( 1 )),
CONSTRAINT [ PK__Kaosheng__173876EA ] PRIMARY KEY CLUSTERED
(
[ KaoshengId ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 考试 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' KaoshiId '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 学生 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' BanjiStudentId '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 考号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' KaoHao '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 试场号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ShiChangNum '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 座位号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ZuoweiHao '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 总分 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Zongfen '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 标准分 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' TScore '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 等第 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Dengdi '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 班内名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ClassMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 班内进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ClassMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 校内名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' SchoolMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 校内进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' SchoolMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 四校名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' AllMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 四校进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' AllMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 参评 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Active '
GO
USE [ WZ16 ]
GO
ALTER TABLE [ dbo ] . [ Jwc_Kaosheng ] WITH CHECK ADD CONSTRAINT [ FK_Jwc_Kaosheng_Base_BanjiStudent ] FOREIGN KEY ( [ BanjiStudentId ] )
REFERENCES [ dbo ] . [ Base_BanjiStudent ] ( [ BanjiStudentID ] )
GO
ALTER TABLE [ dbo ] . [ Jwc_Kaosheng ] WITH CHECK ADD CONSTRAINT [ FK_Jwc_Kaosheng_Jwc_Kaoshi ] FOREIGN KEY ( [ KaoshiId ] )
REFERENCES [ dbo ] . [ Jwc_Kaoshi ] ( [ KaoshiID ] )
GO
/**/ /****** 对象: Table [dbo].[Jwc_Kaosheng] 脚本日期: 11/13/2007 01:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ Jwc_Kaosheng ] (
[ KaoshengId ] [ bigint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ KaoshiId ] [ bigint ] NOT NULL ,
[ BanjiStudentId ] [ bigint ] NOT NULL ,
[ KaoHao ] [ int ] NULL ,
[ ShiChangNum ] [ nvarchar ] ( 20 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ZuoweiHao ] [ int ] NULL ,
[ Zongfen ] [ float ] NULL ,
[ TScore ] [ float ] NULL ,
[ Dengdi ] [ nvarchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ClassMingci ] [ bigint ] NULL ,
[ ClassMingciAdd ] [ bigint ] NULL ,
[ SchoolMingci ] [ bigint ] NULL ,
[ SchoolMingciAdd ] [ bigint ] NULL ,
[ AllMingci ] [ bigint ] NULL ,
[ AllMingciAdd ] [ bigint ] NULL ,
[ Active ] [ bit ] NULL CONSTRAINT [ DF_Jwc_Kaosheng_Active ] DEFAULT (( 1 )),
CONSTRAINT [ PK__Kaosheng__173876EA ] PRIMARY KEY CLUSTERED
(
[ KaoshengId ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 考试 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' KaoshiId '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 学生 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' BanjiStudentId '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 考号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' KaoHao '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 试场号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ShiChangNum '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 座位号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ZuoweiHao '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 总分 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Zongfen '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 标准分 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' TScore '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 等第 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Dengdi '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 班内名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ClassMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 班内进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' ClassMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 校内名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' SchoolMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 校内进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' SchoolMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 四校名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' AllMingci '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 四校进步名次 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' AllMingciAdd '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 参评 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' Jwc_Kaosheng ' , @level2type = N ' COLUMN ' , @level2name = N ' Active '
GO
USE [ WZ16 ]
GO
ALTER TABLE [ dbo ] . [ Jwc_Kaosheng ] WITH CHECK ADD CONSTRAINT [ FK_Jwc_Kaosheng_Base_BanjiStudent ] FOREIGN KEY ( [ BanjiStudentId ] )
REFERENCES [ dbo ] . [ Base_BanjiStudent ] ( [ BanjiStudentID ] )
GO
ALTER TABLE [ dbo ] . [ Jwc_Kaosheng ] WITH CHECK ADD CONSTRAINT [ FK_Jwc_Kaosheng_Jwc_Kaoshi ] FOREIGN KEY ( [ KaoshiId ] )
REFERENCES [ dbo ] . [ Jwc_Kaoshi ] ( [ KaoshiID ] )
可以正常运行的SQL:
set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [ dbo ] . [ RandomKaoHao ]
-- Add the parameters for the stored procedure here
@KaoshiId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
-- declare @KaoshiId bigint
-- set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
from Jwc_Kaosheng,VKaosheng
where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
and Jwc_Kaosheng.kaoshiId = @KaoshiId
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = (select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
-- where kaoshiId = @KaoshiId
;
-- select KaoshengId,KaoHao from Jwc_Kaosheng where kaoshiId = @KaoshiId
END
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [ dbo ] . [ RandomKaoHao ]
-- Add the parameters for the stored procedure here
@KaoshiId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
-- declare @KaoshiId bigint
-- set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
from Jwc_Kaosheng,VKaosheng
where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
and Jwc_Kaosheng.kaoshiId = @KaoshiId
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = (select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
-- where kaoshiId = @KaoshiId
;
-- select KaoshengId,KaoHao from Jwc_Kaosheng where kaoshiId = @KaoshiId
END
奇怪处:
下面查询可以正常运行,但做成sp就不行了,考号很多重复。
declare
@KaoshiId
bigint
set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
-- from Jwc_Kaosheng,VKaosheng
-- where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
-- and Jwc_Kaosheng.kaoshiId = @KaoshiId
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = ( select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
where kaoshiId = @KaoshiId
与上面正常运行的查询,相对就的sp
set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
-- from Jwc_Kaosheng,VKaosheng
-- where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
-- and Jwc_Kaosheng.kaoshiId = @KaoshiId
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = ( select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
where kaoshiId = @KaoshiId
set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [ dbo ] . [ RandomKaoHao ]
-- Add the parameters for the stored procedure here
@KaoshiId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
-- declare @KaoshiId bigint
-- set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
-- from Jwc_Kaosheng,VKaosheng
-- where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
-- and Jwc_Kaosheng.kaoshiId = @KaoshiId
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = ( select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
where kaoshiId = @KaoshiId
;
-- select KaoshengId,KaoHao from Jwc_Kaosheng where kaoshiId = @KaoshiId
END
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [ dbo ] . [ RandomKaoHao ]
-- Add the parameters for the stored procedure here
@KaoshiId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
-- declare @KaoshiId bigint
-- set @KaoshiId = 4
;
With VKaosheng as (
select KaoshengId, row_number() over ( order by newid () ) as OrderId
from Jwc_Kaosheng where kaoshiId = @KaoshiId
)
-- update Jwc_Kaosheng
-- set Jwc_Kaosheng.KaoHao = VKaosheng.OrderId
-- from Jwc_Kaosheng,VKaosheng
-- where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId
-- and Jwc_Kaosheng.kaoshiId = @KaoshiId
update Jwc_Kaosheng
set Jwc_Kaosheng.KaoHao = ( select top 1 OrderId from VKaosheng where Jwc_Kaosheng.KaoshengId = VKaosheng.KaoshengId)
where kaoshiId = @KaoshiId
;
-- select KaoshengId,KaoHao from Jwc_Kaosheng where kaoshiId = @KaoshiId
END
为什么,虽然目前已经搞定了错误,但还是不明白原因,有谁知道请赐教。谢谢