The Smashing Pumpkins have a ton of great ones that fit this thread perfectly. It’s one of the things I love so much about Billy’s songwriting.
Hummer is the embodiment of it. Both lyrically and musically, it has so much hope and doom, even at the same time.