Dwarf Fortress comes to mind. Water can be pressurized and (re-)routed through a mountain as it's constantly influenced by gravity.

The game isn't about water (it's essentially a city-building game), but the water mechanics really add to the depth of the gameplay. There's a good article on Gamasutra on how they did the water. I'll try to find it.

Edit: found it. Here's some representative images..