CC: Red Alert 2 has one of the best intros I've seen. Not gonna list all the great one's that I've seen but some Final Fantasies and Dynasty Warriors' have cool intros too.
Edit: Oh yes, almost forgot to mention the one that really got me pumped up.
Warhammer 40k: Dawn of War. That's THE BEST intro I've EVER seen