sql server面試試題

question:howcanilistnon-contignousdata?
indatabasepubs,icreateatabletestusingstatementasbelow,andiseveralrowasbelow

&nbsp程序代碼
createtabletest
(idintprimarykey)
go

intotestvalues(1)
intotestvalues(2)
intotestvalues(3)
intotestvalues(4)
intotestvalues(5)
intotestvalues(6)
intotestvalues(8)
intotestvalues(9)
intotestvalues(11)
intotestvalues(12)
intotestvalues(13)
intotestvalues(14)
intotestvalues(18)
intotestvalues(19)
go

nowiwanttolisttheresultofthenon-contignousrowasbelow,howcanidoit?

&nbsp程序代碼
missingaftermissingbefore
——————-———————
6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp8
9&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp11
…

&nbsp

answer：
selectidfromtesttwherenotexists(select1fromtestwhereid=t.id+1)
ornotexists(select1fromtestwhereid=t.id-1)

question:howcanilistallbookwithpricesgreatherthantheaveragepriceofbooksofthesametype?
indatabasepubs,haveatablenamedtitles,itscolumnnamedpricemeanthepriceofthebook,andanothernamedtypemeanthetypeofbooks.
nowiwanttogettheresultasbelow:

&nbsp程序代碼
type&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsptitle&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspprice&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
————————————————————————————————————————————————————————-
business&nbsp&nbsp&nbsp&nbspthebusyexecutive''sdatabaseguide&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp19.9900
…
…
…
…

answer：

&nbsp程序代碼
selecta.type,a.title,a.pricefromtitlesa,
(selecttype,price=avg(price)fromtitlesgroupbytype)b
wherea.type=b.typeanda.price&gtb.price