Billy The Mountain by Zappa & The Mothers
A mountain (Billy) and his wife (Ethyl, a tree) decide to go on vacation, which causes untold havoc when they open a hole in the earth’s crust. They are confronted by a mysterious would-be hero named Studebacher Hoch, who wears cardboard, foil-covered wings and is propelled by flies. The meeting of mountain and man results in Studebacher falling off of Billy’s side to ignominious defeat.
(I’ve left out quite a few details, but you get the idea…
)
How about The Night the Lights Went out in Georgia?
Little sister kills her brother’s cheating wife and her lover.
Mark Knopfler wrote the story of Mason and Dixon on their way to America “to draw the line”.
He and James Taylor sing the characters’ lines.
“'39” by Queen is about time dilation.
“Boris the Spider” by the Who. Several other Entwhistle songs had unusual subjects: “905” about clones, his solo “I was Just Being Friendly” about mistaking a woman for a prostitute, “Cousin Kevin” and “Fiddle About” from Tommy
ENTWISTLE!
“Squire James” by Sanford and Townsend.
Woman catches husband in his barn with a younger woman. Burns the barn down with them in it. Gets visited in the night by smouldering figures riding demon horses.
Billy Joel’s “Downeaster Alexa” is about the plight of commercial fishermen.
Faith No More had a few of these off of “The Real Thing.” For instance:
“Edge of the World” is first-person creepy guy trying to get a little girl to come with him (“it’s not the point that I’m 40 years older…you can trust me, I’m no criminal, but I’d kill my mother to be with you”)
“Zombie Eaters” is a newborn baby singing to its mother
“Surprise! You’re Dead” is about becoming a vampire
“Underwater Love” is about a guy trying to drown his lover (“liquid seeps into your lungs, but your eyes look so serene”)
Metallica has a song called “Of Wolf and Man,” which is about werewolves
Violent Femmes has “Country Death Song,” which is about a guy going crazy and throwing his young daughter into a bottomless well
Maxwell’s Silver Hammer, about a serial killer, and Timothy, about cannibalism during a mine accident, are a bit odd.
Iron Maiden performs songs about The Rime of the Ancient Mariner, Dune, WWII dogfights, the massacre of natives by American settlers, and what feels to me like sort of a Legend of Sleepy Hollow tale about coming across some devil worshipers in the night. And that’s just on their first few albums. I haven’t heard any of their albums since Powerslave.
I liked the song about the experimental zeppelin going down in a storm on its maiden voyage.
This could be a Drive-By Trucker thread!
That Wig about a preacher’s wife who shot him and ran off with the kids. Song tells you why.
*
Deeper In.* Based on a true story of… uh… well… ‘Brotherly Love’.
*
18 Wheels of Love*. Another true story of how the author’s Mom found love late in life.
Goode’s Field Road. About a businessman in too deep and gets offed to protect the family.
So, so many more. The Great Car Dealer War, Pulaski, 3 Dimes Down…
“Satan Gave Me A Taco” by Beck.
“Oliver’s Army” by Elvis Costello and the Attractions. An army recruiter’s pitch to an unemployed British kid, and keeping unemployment deliberately high to grease the skids that push unemployable teenagers into the English army.
“If I Had a Boat” by Lyle Lovett. A little boy dreams about what the ultimate freedom would be like. His musings go a little off the rails as he overthinks them, but he’s pretty sure a boat and a pony are involved.
“All I Wanna Do” by Sheryl Crow. Ever watch somebody in a bar peel the label off a wet beer bottle? Yeah? How about at 10:00 AM?
“Savoy Truffle” by the Beatles, G. Harrison writer. You know those name and placement charts in a Whitman Sampler that tell you what and where each chocolate is? Bet you can’t write a song about every single one of them! Anyway, that stuff’s bad for your teeth.
Lovett also had other songs with odd premises. “L.A. County” is about a killer who murders his ex-girlfriend at the church altar as she’s about to marry someone else.* “Church” is about a church service going on too long.
The Bonzo Dog Band has songs about vacations (“Postcard”), a boring neighbor (“My Pink Half of the Drainpipe”), a woman being murdered (“Death Cab for Cutie”), boredom itself (“I’m Bored”), bodybuilding (“Mr. Apollo”), a tent (“Tent”), egotism (“Look at Me, I’m Wonderful”), “Waiting for the Wardrobe,” and many more.
And, of course, just about every song by Captain Beefheart.
*In a live callin, someone requested it, telling Lovett they played it at their wedding. Lovett asked, “Do you even know what that song is about?” She replied, “Well, we changed the lyrics a little.”
Not exactly an uncommon premise. An uncomfortable one, but kind of a staple of old folk and blues songs.
I don’t know if this counts, but the song Steppenwolf by Hawkwind is based on the novel of the same name
One of the more notable rock songs about Einstein’s Special Theory of Relativity. 
My nominee: “Tweeter and the Monkey Man,” by the Traveling Wilburys – a song about two drug dealers in New Jersey, one of whom is apparently a trans woman and Vietnam vet.
Is “Flowers On the Wall” about a stint in the mental hospital? “Playing solitaire til dawn with a deck of 51”?
“Cygnus X-1” by Rush, the first part which concludes “A Farewell to Kings”, is about a black hole in space, specifically the one named Cygnus X-1.
Elton John and Bernie Taupin wrote a song about a mass shooting: “Ticking.” It was released on the Caribou album in 1974. Definitely not their usual material.
Jethro Tull-Hunting Girl
Alan Parsons Project-the whole Tales of Mystery and Imagination album.
David Bromberg-Sharon
Mary Gauthier- Drag Queens in Limousines.