Mmm, guns... Aside from the mandatory portal/gravity/BFG weapons, here's a few of my own favourites.
Allow me to +1 the Unreal flak cannon. It's like a shotgun, but it fires red-hot shots that bounce off walls, looks much cooler, and oh yeah, shoots grenades too. Arcing-fire shrapnel fragmentation grenades. Nailing someone with one of those from a huge distance away is so satisfying, 'cos it's really hard...
Also the InstaGib gun. InstaGib is such a fun game mode that I play it far more than the normal version (well, that and Rocket Arena; 16 players on Morpheus with Rocket Arena active is just obscene amounts of fun). The gun itself looks meh, I'd have preferred if they'd enhanced the pulse rifle or something instead of the shock rifle, but the game mode that comes with it is exciting, interesting and tactical (and means you don't spend yonks searching for decent firepower or items only to get killed by some git with a rocket launcher).
I loved the Halo sniper rifle. I like sniper weapons in general, including the epically awesome HL2 crossbow (arcing-fire sniper weapons are so much more satisfying because of the increased difficulty, plus the results of crossbow shot hitting soft Combine flesh and the physics thereof) and Painkiller's frankly ridiculous boltgun. Halo's, though, has two levels of zoom, looks cool, has a four-shot magazine with a fairly nice reload time, and leaves that funky smoke trail behind it. It's an excellent weapon and blasting Elites out in one shot via application of a bullet to the base of the skull makes me a happy man.
Painkiller's entire arsenal. Everyone raves about the electrodriver (the infamous shurikens'n'lightning cannon) but in game it's actually outperformed by the awesomely destructive rocket chaingun (seriously, they combined insanely accurate rapid fire with a fairly quick-shooting rocket launcher?). The default Painkiller weapon is like a blender and a grappling hook mixed together, the shotgun's ability to reduce enemies to several (steaming, blood-spewing, cartwheeling-through-the-air) chunks coupled with its 'Your health matters not' freezer make it utterly awesome, and then there's the stakegun a.k.a. the penis extension cannon, which nails bad guys to walls with metre-long stakes *and* chucks grenades out as an alt-fire.
Then there's the expansion, Battle out of Hell, which gives you the incredibly damaging SMG/flamethrower (just like what it says on the tin, except that the SMG fires high explosive armour piercing rounds and the flames spewed out by the 'thrower ignite the environment, staying around for a good long time!), and the hilariously messy boltgun (imagine the stakegun with a sniper scope and the ability to fire 5 metal spikes at once instead of one wooden one; oh, and it shoots 10 grenades, not 1).
There's FEAR's Type 7 Particle Gun. It distorts half the screen when it fires, makes an utterly indescribable noise, and reduces enemies to a cloud of blood and a charred, collapsing skeleton. Oh, and it's more accurate and has better zoom than the game's sniper rifle. Similarly, the shotgun, which when combined with FEAR's semi-ragdoll physics makes for some *incredibly* entertaining slow-motion kills. (One shot to the stomach to throw them into the air, doubled over; another blast to the head jack-knifes their corpse in mid-air, pitching them backwards and to the floor. In glorious, crimson-spewing slow motion.)
HL2's pulse rifle. Despite its horrifically limited ammo capacity, this thing is a beast. It kills stuff dead. It's accurate, powerful, has a meaty-but-unusual sound and that weird ricocheting alternate fire. And its reload animation is so neat!
Then there's Prey. Prey's weaponry is bizarre and interesting but one gun stands head and shoulders above the rest: the autocannon. Think a double-barrelled machine gun with a fat, explosive-egg-spewing facehugger/scorpion crossbreed clinging to the top. The MG fire has a delicious sound and perforates enemies rather nicely, especially at close range; the parabolic-firing grenades do a lot of damage, and like the flak cannon, are extremely satisfying when you score a direct hit with one. Plus it's the most awesome-looking weapon of all. Did I mention it had a facehugger on it?
There's a few Quake 4 guns that are worth a mention (I just love Quake 4's arsenal). The rail gun, a sniper rifle that can shoot through multiple enemies and leaves a plasma-glowing trail behind it. The dark matter cannon, Q4's BFG, launching a slow-moving black hole thingy that picks up enemies and carries them off, exploding in a sphere of dark energy when it hits something immovable. The basic machine gun, a hell of a weapon (it's a lot more powerful than most games' '#2-slot crappy assault rifle' gun, and has a sniper mode on alt-fire). The nailgun, a homing machine gun (yes, you heard me) that turns enemies to gibs in no time... need I go on?
Exteel, online mech MMO-FPS-deathmatchthingy extraordinaire, has some wonderful weapons, which become even cooler when you realise that the thing wielding them is about 20 metres tall and the weapons are suitably scaled-up. The Annihilator Blades are unusual, powerful and REALLY COOL LOOKING. There's a gigantic (as in 30 metres long) rocket revolver; plasma-firing sci-fi uzis; Gundam-style mech sized lightsabers; and so on. Oh, and a flamethrower shotgun.
Finally, allow me to give an honourable mention to the arsenal of Mario Kart. The blue shell, for example, is a long-range fast-moving indirect-fire homing artillery piece that makes a really cool (and terrifying, when you're in first place!) noise as it flies, and explodes into a huge sphere of plasma-like stuff. The 'battery' as I call it, of three red shells, is essentially a trio of heatseeking missiles that orbit your vehicle until fired, protecting you from enemies and their fire. The Bullet transforms your vehicle into a gigantic projectile (with angry eyes, and all) that blasts forward along the track, bowling anything in its path out of the way.
Pwned.
Yeah, I like my guns.