Triumphing against long odds is a staple of the literature I want to emulate, and for good reason--it's very exciting and emotionally charged. The problem, of course, is that if you throw PCs into one-chance-in-a-million situations often, they usually lose, because that's the nature of a one-chance-in-a-million situation. Players know this, and so try to avoid desperate against-all-odds situations for the same pragmatic reasons that generals do.
I'm not really satisfied by any of the possible solutions I can think of. Here's a cursory overview of the parts of the solution space that I can see:
A. The rules favor the PCs in near-hopeless situations.
Somehow, the closer to death you are, the harder you can fight, or somesuch.
My big problem with this is that it violates players' suspension of disbelief pretty strongly. Excessively rational players will point out that the ideal conquering army consists of one lame goblin.
A variant of this is to try to put this aspect into the rules, but obscure it, so that it isn't evident. This doesn't work so well for me, because I'm a pretty hardcore tactician, and I like analyzing the mathematics of RPGs. It's somewhat hard for a game to avoid my analyzing the chances of success--and if I can't understand the chances of success, I'll find it frustrating.
B. The GM favors the PCs in near-hopeless situations.
The big problem here is the difficulty of fudging without the players realizing it. If the players believe that they are safe despite the apparent risk, whatever part of the thrill is based on the real risk will be diminished.
C. PCs have a limited resource they can spend to improve their chances.
The Buffy RPG is an example of this; PCs have a stockpile of Drama Points they can use to prevail in desperate situations, reduce the effect of injuries, or even come back from the dead. But even a stash of healing potions in D&D provides another example of this.
This can work fairly well, actually, because it provides a buffer zone--PCs expend resources to weather an otherwise unweatherable situation, and yet the players feel concerned because they've expended precious resources.
I've used this strategy in my own D&D game; I've given the party three pearls of resurrection, and I like to think that if I manage to force them to use one, they'll feel anguished about it, even without losing a character.
Another risk with Drama Points specifically is that if the PC is not aware of those points, the Drama Points may interfere with a player's identification with their character.
D. Fights are not as lethal as they seem.
There's cavalry who will come to the rescue or something, so for in-game reasons, the seemingly hopeless fight is not so hopeless.
This one, like GM fudging, can't be done very often at all without losing the players' perception of real risk or their perception of ability to judge fights.
E. Players are not able to accurately judge how risky fights are.
Unknown Armies does this by keeping the players in the dark about their current hit point totals. I haven't tried it, but I think it might be pretty effective. I'd like to try it sometime.
F. PCs are fighting for less-than-lethal stakes.
I'm actually doing this at the moment in the fight I've just set up in my game; the PCs probably have enough firepower to kill their opponents without too much trouble--but I expect them not to want to, so there's an extra fillip of challenge there. Another example is that of cases where the PCs aren't fighting to the death, but playing volleyball, intriguing, seducing, or whatnot.
This certainly can be very good gaming, but there is still a strong place for fighting--especially in D&D, where there's a place for a lot of fighting. And there's a reason for that--combat is pretty exciting, because it's naturally higher-risk.
One more example that I'm not sure how to fit in: when I started my campaign, I directed people to bring scratch PCs; my intention was to deliberately kill some of PCs in the first session, and then to bring in longer-term characters. (It didn't work; the PCs won the battle just fine.)
Are there solution approaches that I'm missing, here?