The waterpark that was voted best in the country for about the past 7 years straight is Schlitterbahn in San Antonio, Texas (New Braufelds). I think they also built a second and third Schlitterbahn somewhere near the Texas coast. And they just finished the fourth one, which is in Kansas City, Missouri.
You can also find a lot of waterparks near Palm Springs--plus all the large hotels there (and there are a lot of them) have great pools--as does Las Vegas. The Mandalay Bay hotel in Las Vegas even has its own beach and gigantic wave pool--as well as two smaller pools and a "Lazy River".