Just recalling various reviews I've come across over the years there's the Dragon Knight series which was mostly a comedy RPG series, and there's Kana: Little Sister which supposedly received several high reviews. Plus there's the various KEY games like To Heart, Kanon, Clannad, etc. Most of the KEY games seemed to be made into anime series with the erotic content removed. I've heard of the animes before knowing about the games they were based on, some of the games were apparently re-released with the adult content removed to make them more mainstream. One more recent title I've heard of was Katawa Shoujo as it was developed by a group of 4chan members as an indie title, it supposedly got good reviews too.

There's a notable difference between story/plot based games with some erotic content and the sleazier games that are just excuses for full pornography. Stay away from the horrible RapeLay style games.