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

转载

mob604756ee87ff 2007-11-13 01:17:00

一个考生表，现在要随机生成考号

表结构如下：

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