The first one I remember seeing that really made me tickle thru the body of exitment, was the first time I saw the intro for Wing Commander III.
I've always loved games that start cinematically.
And that was the first game I played that gave that. And not only in the intro, but thru the whole game.
IV was also good.
Other than that I'll have to think about that.
There's X-Wing: Alliance, but I think that is also largely due to the soundtrack used.
More recently, the World of Warcraft intro is very nice.