北大“數(shù)據(jù)庫原理”上機實踐題目總結(jié)（12）

declare @WID char（5）
    declare @RID char（5）
    set @MAX_Earning = -65535
    set @NumID = 'XXX'
    set @MAX_NumID = 'XXX'
    declare CurNum cursor for
    select NID
    from Numbers
    open CurNum
    fetch from CurNum into @NumID
    while @@fetch_status = 0
    begin
    set @Earning = 0
    declare CurChip cursor for
    select RRecord.HID as WinID，CRecord.HID as RID
    from RRecord，CRecord
    where RRecord.SerNum = CRecord.SerNum and CRecord.NID = @NumID
    open CurChip
    fetch from CurChip into @WID，@RID
    while @@fetch_status = 0
    begin
    if @WID = @RID
    set @Earning = @Earning + 8
    else
    set @Earning = @Earning - 2
    fetch from CurChip into @WID，@RID
    end
    close CurChip
    DEALLOCATE CurChip
    if @Earning > @MAX_Earning
    begin
    set @MAX_NumID = @NumID
    set @MAX_Earning = @Earning
    end
    print @NumID + CONVERT（Varchar（20），@Earning）
    fetch from CurNum into @NumID
    end
    close CurNum
    DEALLOCATE CurNum
    print '賺錢最多的會員'
    print @MAX_NumID + CONVERT（Varchar（20），@MAX_Earning）
    /*=======================================================*/
    /*馬場主賺了多少錢*/
    declare @Cash int
    set @Cash = 0
    declare CurTol cursor for
    select RRecord.HID as WinID，CRecord.HID as RID
    from RRecord，CRecord
    where RRecord.SerNum = CRecord.SerNum
    open CurTol
    fetch from CurTol into @WID，@RID
    while @@fetch_status = 0
    begin
    if @WID = @RID
    set @Cash = @Cash - 8
    else
    set @Cash = @Cash + 2
    fetch from CurTol into @WID，@RID
    end
    close CurTol
    DEALLOCATE CurTol
    print '場主賺了' + CONVERT（ VARCHAR（10），@CASH） + '錢'
    drop table CRecord
    drop table RRecord
    drop table Numbers
    drop table Horses