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

/*所在地是北京，余額大于10的會(huì)員*/
    select NID，NName，NAdd，NCash
    from Numbers
    where NAdd = 'address1' and NCash >= 2
    /*獲勝次數(shù)最多的馬的產(chǎn)地*/
    select HAdd
    from Horses
    where Horses.HID in
    （Select HID
    from RRecord
    group by HID
    having count（*） >= all
    （Select count（*）
    from RRecord
    group by HID）
    ）
    /*投注次數(shù)最多的會(huì)員*/
    select NID
    from CRecord
    group by NID
    having count（*） >= all
    （Select count（*）
    from CRecord
    group by NID）
    /*U001投注*/
    insert into CRecord
    VALUES （'U001'，'H003'，3）
    update Numbers
    set NCash = NCash - 2
    where NID = 'U001'
    update Numbers
    set NCash = NCash + 10
    where NID = 'U001' and exists
    （select *
    from RRecord，CRecord
    where RRecord.SerNum = CRecord.SerNum and RRecord.HID = CRecord.HID and NID = 'U001'
    ）
    /*得錢最多的會(huì)員（只考慮獎(jiǎng)勵(lì)）*/
    declare @NumID char（5）
    declare @MAX_NumID char（5）
    declare @Earning int
    declare @MAX_Earning int
    set @MAX_Earning = 0
    declare CurNum cursor for
    select distinct NID，count（*）
    from RRecord，CRecord
    where RRecord.HID = CRecord.HID
    group by NID
    open CurNum
    fetch from CurNum into @NumID，@Earning
    while @@fetch_status = 0
    begin
    if @Earning > @MAX_Earning
    begin
    set @MAX_Earning = @Earning
    set @MAX_NumID = @NumID
    end
    fetch from CurNum into @NumID，@Earning
    end
    close CurNum
    deallocate CurNum
    print '得到獎(jiǎng)厲最多的會(huì)員'
    print @MAX_NumID + CONVERT（Varchar（20），@MAX_Earning * 10）
    /*賺錢最多的會(huì)員（包括投注的錢）*/
    /*=======================================================*/