Where can I buy a "roaming" or "international" SIM card — one with attached phone numbers in multiple countries?

What you're probably looking for is often called a "Roaming SIM" or "International SIM". These have numbers for multiple countries attached to them, and generally lower costs to make/receive calls (and sometimes also texts and data) in those countries.

Depending on the one you go for, they may have varying numbers of countries covered from the start, and you may or may not be able to add new countries as needed.

Personally, I have one from TruPhone, but there are a number of other companies out there. With TruPhone, you get one number as standard which is in your home country. As you need them, you add numbers from other countries (from a small-ish list) on a month-by-month basis. Their costs work well for the kind of travelling I do, but it's worth checking out others as the overall cost varies between provides based on the number of countries you need numbers for, the length of time you want numbers, how many calls you do in+out of country etc.

(Handy TruPhone hint - their customer service team on email and phone have always proved utterly useless whenever I've had problems. Instead, describe your issue in 140 characters and post it on twitter, then someone clueful from TruPhone very quickly pops up and helpfully solves your problem!)

I'd say as a general rule, if you go to just one or two countries very often, it'll be cheaper to get a pay-as-you-go SIM card from that country. However, that sim probably won't work when you're home though, sometimes unless you pay extra, sometimes never, so it depends if you need the number to always work or not. If you visit lots of different countries, and/or need to give out various stable and always working country numbers (and you don't want to do call forwarding), then an international sim may be best. Do the maths yourself and decide!