I played with a trial version and it was really cool. I don't have a really fancy camera and can't shoot in RAW, but it still did some really cool things to my photos. You don't have to have PS in order for it to work, it's a stand-alone program...BUT the features in Lightroom are available in PSCS3 and CS4, I think - so if you have either of those programs, you can probably skip Lightroom.
I don't know how much it costs, though. I'm not going to get it even though it was cool because I'm planning on upgrading to CS4 soon.