Warcraft 3 would be the answer to your question. It has a wonderful single player campaign, great story and lore if you're into that, great characters and interesting gameplay. To be fair it's fairly similar to SC2, being a Blizz game and all, but that is most obvious in multiplayer, less in single. The combined campagins of Reign of Chaos and Frozen throne will provide you with quite a lot of game time, and even if the gameplay dosen't root you to the chair, the epic story will. Also the insane number of custom maps out there will provide countless more hours of fun. Easily and clearly the best pick.
Next up are some other very good strategy games, but unfortunately they all have some fairly big flaws as well :
Age of Empires: (there are a lot of games in the series, but AoE2 is still the best for me, so that's the one I'm writing about. Feel free to give them all a try, I think you can buy each for a dollar. Including 3)
AoE2 was superb back in the day, I've had more fun with this than with SC1 but today it might lack polish and depth. To be fair this game is heavily coloured by nostalgia and I really don't know how it would hold up today. Neither Age of Mythology or AoE3 have been as fun or entertaining for me. Poor balance, limited replay value...
Warhammer40k Dawn of War (+ all the expansions)
This game does things a bit differently than typical RTSs, but not in a bad way. It manages to introduce squad based tactical shooter elements into the genre without making it difficult or awkward, no easy feat I'd imagine. Fun and visceral, it's worth playing, but I didn't personally like the campaign and the balance seems poor at times. A few squads of upgraded Tau basic units are insanely powerful. Playing the skirmish games with a friend against the AI, or even against other friends though is loads of fun (and the AI is very challenging on the higher settings). Races are different enough from each other to make things interesting and suitable for different play styles, but similar enough so that the mechanics don't get tedious.
Company of Heroes doesn't really fall into your category strictly speaking, since there's little to no base building, resource harvesting, and other such staples of the RTS genre, but it is a VERY engaging game that should provide hours of fun and challenges. It is indeed probably the only RTS-ish game out there that can really make you feel for your troops.
There's fun to be had with the Command and Conquer games (any and all of them. They are similar enough to each other that this really covers all of them. Different setting and different units you will find across the games, but the gameplay itself is the same.)
Very standard base building RTS fare (hell, this series kinda defined the standard) with some interesting units here and there and some minor variations on gameplay (like tech trees, or XP enabled power-ups) and Super-Weapons to wreak havoc on your foes. Kinda poor balance and pacing, some horrible AI (You will chew your mouse off in frustration when you meet the abysmal pathfinding in Generals) and a general same-y-ness to everything make these games fun, but not great.
Universe at War: Earth Assault is a very compelling and interesting mix of modern and old-school RTS gameplay, mostly depending on the faction you play. The Novus have typical RTS gameplay, with resource harvesters, buildings, unit training, etc. The Hierarchy mix it up with what can be called massive mobile bases/weapons platforms that have you choose between a walking death star, a unit production facility, or anything in between. The Masari make use of a dual-style gameplay with a light/dark mode for most units, changing their behaviour drastically. All of this comes in a nice shiny current-gen graphics wrapper, with streamlined controls and interface that make for a quite compelling package. Haven't played it as much as the others and don't know if it has any major flaws, but while the game doesn't shine, it doesn't stink either.
The games aren't in any particular order (except for Warcraft3, it deserves to be the first). The short descriptions don't, by any means, cover the entire games, but it should give you an idea what to expect from them, especially from the perspective of a SC2 player
Have fun.