Depends what you mean by intro.
Unreal and Doom 3 both had incredible in-game walk-around intros...
Half-life's intro was pretty great too.
If you mean intro movie/cutscene, I have way too many to list that I loved. I can't pick just one, or even put them in order, but for now I'll just list stuff I've thought of. Fallout series since I've been playing 3 and 2 again recently. Supreme Commander's is pretty well done. Total Annihilation's was pure awesome. Warcraft 2 was good, Starcraft:BW's was good...I could name plenty that were pretty sweet.
Hell, the dominion:storm over gift 3 intro, like, I thought I was the only person on the planet who ever played that game, but apparently not. I didn't remember the intro being so kickass until just now, though. The mechwarrior series always had great intros...
OH!
OH!
I got something.
Command and Conquer. (original, though red alert 1 was hilariously awesome too. ("Sooner or later, time will tell.."))
I loved the C&C intro because it had some awesome explosions, some dark comedy, and hell, it was a citizen browsing TV channels and you learned about both factions and some events going on in the news from it. I know it's been done before, but I thought it was pretty cool at the time.