Oh, this is really a huge SQL statement that makes people confused. If optimization is needed, I don't think there's much that can be changed directly in the SQL itself. I have some suggestions.
Don't let the database handle some logical judgments that can be executed in the program.
Split some complex queries into smaller ones and splice them in the program.