Which so weird, because Age of Wonders solved it years ago: build stacks of units, have maintenance costs for standing armies that project zones of control. When one stack moves into another's zone of control a fight commences on a different screen where there's a grid and turn based combat. Fight until there is a winner. In small skirmishes with AI opponents you can auto-resolve.
Seriously, it can't be that hard. And it allows for later units to have area effects. Want to call in a bombing strike? Well if you have nearby air units you can do that (AoW you could cast spells that did area effect damage). And you could expand it. For instance, if your enemy brought anti-air they have a chance to shoot down the fighters. If their engineers have been digging trenches it can mitigate that damage.
Seriously, if Civ could keep the rest and get with it on combat (use this system coupled with attrition when invading/supply lines) it would be the greatest.
Yes, I think there should be an option for tactical fighting, and I hope that it will come in civ VII. However, it should be somewhat optional, since I think civ caters much more to builder type players that don't want to fight that much than AoW does. Additionally, I'm not sure if it wouldn't be more fitting to copy Endless Legend's tactical fighting, where you still fight on the actual map with your small stacks. That means that you cannot have 6 crossbows in a city behind walls, but only one unit per tile in tactical fighting. But you would be fighting around the districts, plundering improvements, etc which I think would be really nice to do. Also, in Endless Legend, it's not a fight to the death, but limited to 6 turns of tactical fighting, so you can often retreat if you want to. (and I really hate[!] that wounded units do full damage in AoW, this is really harming the otherwise great fighting system of this game, which might be the best in all 4X games).
Back to OP: I think that long unit build times do have advantages, and I say that only playing epic and marathon speed. It makes you cherish your units much more. I don't have a lot of them around, some garrison units at some points and an army of between 5 to 8 units for defending and attacking. In many games, I don't use units at all, in others I lose one or two, and I like it that way. Not just because of the promotions, but also because of the 10+ turns a knight needs to be built an average city. It also means planning ahead in the earlier game - if the enemy catches you unprepared on marathon in classical age, you are in trouble. And it means that if I attack, I also need to plan ahead and have a good sieging team assembled, since brining in reinforcements every other turn isn't possible (except if I'd go for total war, which I really rarely do).