The Happiest Days of Our Lives, better known as the intro to Another Brick in the Wall, pt. 2.
As a kid, I used to like Come Sail Away by Styx. Slightly cringy now.
Wierd Al’s the Saga Begins, standing on the shoulders of Don McLean’s American Pie.
The Police’s Don’t Stand So Close to Me, if that’s truly a story song.
Comfortably Numb is part of a rock opera, the same one as The Happiest Days of Our Lives. Most rock-opera songs don’t tell a complete story in their own right, but it does advance the story significantly.
Two more that are sort of story songs: Paradise by the Dashboard Light by Meat Loaf and Billy Joel’s Scenes from an Italian Restaurant.