The Police Quest games and SWAT (Corner Mirror, FLASHBANG!) were already mentioned and are probably the best you can get given what you are seeking.
The Kojima games were also already mentioned. If you are willing to allow sci-fi / cyberpunk settings, I think that Deus Ex should also be a worthwhile consideration here, and perhaps the Blade Runner (PC, adventure game) fits.
Half-joking, if you want a detective game there is Carmen Sandiego
You may really enjoy the game "Hostages" (Amiga). You shoot many terrorists but it takes a semi-realistic approach. You play the game in sections, first you position snipers sneaking through spotlights that the terrorists setup in the embassy. Then the helicopter drops some men on the roof and you can alternate between controlling the snipers that can remove any terrorists near the windows (but don't confuse them with the hostages) and the others, which can rappel down and go inside the building by crashing through a window into a (hopefully cleared) room. Then you can navigate inside the embassy with automatic weapons and gun down the remaining terrorists. At the end you get "rated" with a newspaper article whose heading loosely matches that attempt, describing whether you lost any men or any hostages were killed. Very cool.
Some people suggested Streets of Rage and Robocop. While I think they were joking, Robocop 3 for the Amiga is not an arcade game. You may not want it as it has driving sections (where you ram down a target vehicle while being shot at) and still shoot plenty of people in the shooting sections, but you must do your best to avoid killing any hostages. There are also jetpack sections near the end and some one on one fights with the android from the movie (Otomo?).
For the time both these Amiga games were quite interesting games with varied gameplay. Worth checking out (Hostages possibly holds out to play today better than Robocop 3). Look them up on youtube or something.
Ivo.