(Major spoilers within)
I don't know any really BAD games with good endings, but i know of a decent game with a surprisingly outstanding ending - Pokemon Mystery Dungeon 2.
...
.....Ok, all stopped laughing now?
........Right, good. Anyway...The whole story is very chirpy and syrupy-sweet the whole time, but then in the last few chapters it really gets ominous, and at the very end after a surprisingly difficult boss fight, the ending is unbeleivable. Long story short, your main character gets paradox'd out of existance, and your partner totally freaks out about this, and the whole series of events that happens after this is incredibly done...Its REALLY sad o: Like, worse-than-final-fantasy-x sad. However, Diagla (the Poke-God of time, if you didn't know) reverses the paradox, so you and your partner still get your happy-ever-after.
...OR DO THEY
A few ingame weeks after this happens, your main character starts getting these vision-like dreams, where a different god is trying to kill you to set time straight again, giving the player the feeling that your character won't be allowed his happy ending after all, that thier death had been delayed, not prevented...
But yeah, this is Nintendo and all, so yeah, everything gets resolved, and we all knew that would happen, but its still an amazing change of pace from the usual happy-happy-joy-joy approach the game had that put it very near the top of my favourite-game-story list. =P