It may not be out yet, but Guitar Hero III is going to have what is easily one of the greatest soundtracks to ever grace a game.
I mean, have you seen the huge list of songs that have been announced for the game?
And adding downloadable tracks further on down the line, plus rerecordings of lost master tracks, and co-op career.... dammit, I want this game, solely because the music will kick so very much ass.
There's an artical about it on this site somewhere, but I don't know how to do links, so you'll have to find it yourself.
Anyway, for games that have good music with being built for said music, I'd have to say I'm pretty surprised nobody's mentioned Super Mario 64 yet, as that game had some really good stuff, it's good enough that you often times can't tell it's midi.