Oh boy. I’ve been playing sports video games for about as long as I’ve been playing video games. In fact, they might have been all I ever really played, if I never learned about JRPGs! But that’s another discussion entirely.
NHL 94 and NHL 96 (but not NHL 95) on the Genesis were endlessly playable back in those days. They are all-time classic video games, in my opinion, and don’t get discussed often enough.
NBA Street Vol. 2 is also fantastic, and I’m glad @Jax mentioned it. It really shines as a two-player game, especially, because it works as a basketball simulation, but also allows for a good amount of player expression with all the crazy dance-like moves you can make your players do. Plus the commentary and the soundtrack always put you in a smiling good-mood zone.
Mario Strikers is a good arcade-style soccer game, which completely nails the “simple to learn/hard to master” style. I especially like the GameCube version.
More recently, I’ve been impressed by the Super Mega Baseball series, which also features cartoonish characters and made-up teams, but features extremely deep sim elements. It also lets you customize really granular aspects of the baseball experience, from overall AI difficulty to pitch speed to batting power and player exhaustion.
Another very old but all-time favourite of mine is Dusty Diamond’s All-Star Softball on the NES (Softball Tengoku in Japan). This game lets you pick your team from a bunch of Peanuts-looking kids and weirdos, many of whom had unique or idiosyncratic traits, kind of like the fat-dude skinny-dude dynamic in NES Ice Hockey. I could never find this game to purchase, but I must have rented it ten or more times non-consecutively.
Finally, I’ll mention Fight Night Round 4, which captured the brutality and excitement of boxing, and had nuanced controls that were also easy to learn but tough to master.