问Django ORM SELECT with join sql

EN

class LawyerCounsel(models.Model):
    lawyer_idx = models.AutoField(primary_key=True)
    lawyer_name = models.CharField(max_length=50)
    lawyer_status = models.CharField(max_length=1)
    register_date = models.DateTimeField()
    lawyeridx = models.ForeignKey(CounselAnswer, to_field='lawyer_idx',
unique='true', db_column="counsel_answer_idx", on_delete="models.DO_NOTHING")

        class Meta:
            managed = False
            db_table = 'lawyer'


class CounselAnswer(models.Model):
    counsel_answer_idx = models.AutoField(primary_key=True)
    counsel_idx = models.IntegerField()
    lawyer_idx = models.IntegerField()
    counsel_answer_title = models.CharField(max_length=255)
    counsel_answer_contents = models.TextField()
    counsel_seleted = models.CharField(max_length=1)
    counsel_answer_agree = models.IntegerField()
    lawyer_ip = models.CharField(max_length=20, blank=True, null=True)
    counsel_answer_delay_time = models.IntegerField()
    register_date = models.DateTimeField()
    update_date = models.DateTimeField()

    class Meta:
        managed = False
        db_table = 'counsel_answer'

stats_data_test = LawyerCounsel.objects.filter(register_date=yearmonth).order_by("cnt")

select * from (
            select 
                y.lawyer_name,
                DATE_FORMAT(y.register_date, '%Y-%m-%d') as reg_date,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903') as cnt,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 1) as cnt1,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 2) as cnt2,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 3) as cnt3,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 4) as cnt4,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 5) as cnt5,
                (select count(counsel_answer_idx) from counsel_answer where lawyer_idx = y.lawyer_idx and DATE_FORMAT(register_date, "%Y%m") = '201903' and week(register_date,5) - week(DATE_SUB(register_date,INTERVAL DAYOFMONTH(register_date)-1 DAY),5) = 6) as cnt6
            from lawyer y
        where y.lawyer_status = 'N'
        ) A order by A.cnt desc;