一个考生表,现在要随机生成考号,遇到sql疑问,请教各位大虾


一个考生表,现在要随机生成考号
表结构如下:

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 ] )

可以正常运行的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




奇怪处:
下面查询可以正常运行,但做成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  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

为什么,虽然目前已经搞定了错误,但还是不明白原因,有谁知道请赐教。谢谢

转载于:https://www.cnblogs.com/shiningrise/archive/2007/11/13/957415.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值