Hmmm, tough call Dowly...tough call...
One of the classics is probably the intro to FFVII, probably one of the most overrated FFs, but it's a good intro:
(the remake, I know)
The intro to RA2 was awesome too:
And, although not an intro, the Battle of New York in World in Conflict cutscene was pretty damn awesome:
Also, these cutscenes from the Soviet side:
Also, the intros for War of the Worlds...because I'm kinda bias:
Final Fantasy IX...you kinda have to play the game to understand this, but it was a pretty big "Oh...plot twist." moment:
Medal of Honour - Conversational German

:
Team Fortress 2 - Meet the Spy:
I'm sure there are others, but I can't recall them at the moment.