Got the same problem when building a static Qt 6.5.1 on Steam Deck (Arch Linux), where zstd was provided by Homebrew (didn't want to risk messing up the "main" environment by installing system-wide dependencies via pacman).
As I understood, what's happening (at least, in my case) is that in /home/linuxbrew/.linuxbrew/lib/cmake/zstd/zstdTargets.cmake there is this line:
foreach(_cmake_expected_target IN ITEMS zstd::libzstd_shared zstd::libzstd_static)
And the fix (for building static Qt 6.5.1 as of now) is to remove zstd::libzstd_shared part. That is most certainly not a great idea, especially that this file isn't meant to be modified, but I really can't tell where this actually needs to be "fixed": on Qt side (in FindWrapZSTD.cmake?), or in the Homebrew zstd formula, or elsewhere. Either way, after I left only the static variant in zstdTargets.cmake, static Qt configured and built without problems (and then I reverted these changes that I made in zstdTargets.cmake).
Related to that, there is this bugreport and this commit, which you could apply on top of 6.5.1 sources, but that won't resolve the problem still - it will only make you delete zstd::libzstd_static from zstd's config instead of zstd::libzstd_shared (which means that you'll end up with zstd being a shared/dynamic library dependency of your Qt build).
Finally, peculiarly enough, building a static Qt on Mac OS, where zstd is also provided by Homebrew, didn't cause this problem for me (with Qt 6.5.1), although this topic claims that it might happen there too.
So, coming back to the original post in this thread, I would suggest you to take a look at zstd package that you have installed in your system - probably you'll be able to apply the same workaround in zstdTargets.cmake. Another option, I reckon, would be to disable zstd in Qt configuration, but I'm not sure if it is an optional dependency (still?).