It seems to me that most any game should take the weather into account, particularly pre-modern games. Maybe a city-based noir game doesn't need much weather aside from some rain for atmosphere, but the inhabitants of a medieval fantasy world will be hugely affected by rain, snow, high heat, and high winds, especially if they need to travel any distance cross-country. I've known more story-oriented gamemasters to argue that the weather should be chosen based on story needs, but more often than not, that means the weather is always somewhat cool to somewhat warm, partly cloudy, with moderate breezes and no precipitation. Even sandbox gamers, usually happy to roll dice and consult tables, tend to ignore the weather. At best, weather effects are written into the adventure, like the notes about high temperatures and heat damage in the Sind Desert and Plain of Fire in Master of the Desert Nomads (X4). I have to admit that I often overlook it myself, although I have fiddled with various weather rules over the years.
I've seen a lot of tables describing the temperature highs and lows and daily chance of rain for each month for a given area. The campaign sourcebooks for Iron Crown Enterprises' MERP/Rolemaster were pretty good about this. The problem with these is they're pretty well tied to that area, and while you could repurpose them for areas of similar climate in your homebrew game world, not every area is well represented. Middle Earth is heavy on temperate areas and high mountains, but pretty light on jungles and tundras.
The AD&D Wilderness Survival Guide has a decently workable system, maybe a little too detailed and a little too fiddly to use at the table, but pretty good if you prepare ahead of time and your players don't suddenly decide to go mountain climbing in the middle of your forest exploration session. It's a generic system, covering pretty much every realistic terrain type found on Earth. Lately I've been pretty happy with Spes Magna Games' Dodeca Weather. It's a little more streamlined and easier to work with at the table, although it still helps to prep ahead.
For non-realistic weather, the thing that pops into mind for me is The Epic of Aerth for Mythus. If you're not familiar with this book (great sourcebook if you don't want to build a world from scratch, and great inspiration if you do), it covers an earthlike surface world, a primitive hollow world filled with dinosaurs and ice age critters, the long winding caverns filled with a weird underground ecology (Gygaxian naturalism in its most developed state), and the parallel world of Faeree. Faeree is where the weather angle comes in, with several pages of tables devoted to the strange changes that occur regularly to the environment, including the sun, moon, stars, clouds, plants, and animals of the world, as well as the rate at which time passes relative to the "real" world for characters exploring the Faeree lands.
Way back in the day, I had cobbled together a fairly simplistic weather chart using the weather effects listed in the Moldvay D&D Expert set under the Control Weather spell and the terrain/biome types I found in the Funk and Wagnall's encyclopedia we had at the time. It was just a broad overview, and didn't provide enough detail on rainfall or snowfall to adjudicate travel effects or flooding, but it was super simple...front and back of a page of notebook paper. I wish I still had it. I think I tossed it when I got the WSG, or maybe sometime later in all my military moves.
At any rate, I'm always interested in seeing new systems, and how other DM/GMs handle things like this. Show me how you make it rain.