经过无数个小时的测试，从猫那里得到了代码编写、萨满歌曲演唱等方面的帮助。。。我想我做了一个测试，给出了期望的结果。我测试了一下，它似乎起作用了。
然而，不管我做了什么，单靠sql是不足以完成这项工作的。在绝望中，我不得不使用php来实现。即使使用php，它也被证明是一个挑战。
下面我主要使用php来粘贴我对这个问题的看法。我只希望它能帮助一些人或者给一些人一些关于重构代码和简化逻辑的好主意。

<?php

//Connect to DB
$mysql = new mysqli("127.0.0.1", "root", "", "test");

if ($mysql->connect_error) {
    die("Connection failed: " . $mysql->connect_error);
} 

/*Simplified query - we only get id, address, sex and benefits from our two tabels.
  LEFT JOIN is necessary, because we do not want to miss citizenz that have no benefits.

* /

$sql_statement = "select citizen.ID, citizen.Address, citizen.Sex, benefits.benefits from citizen left join benefits on citizen.ID=benefits.citizen_id";

$results = $mysql->query($sql_statement);
if(!$results)
{
    die("Result is empty! Check the query or data inside tables");
}

/*

* Here we initialize some variables to keep track of further calculations
* 
* @total_women - array($counry => $amount) - Total number of women, divided by countries, and number of men in each country
* @total_men - array($counry => $amount) - Total number of men, divided by countries, and number of men in each country
* @total_benefits - array($counry => array($benefit => $amount)) - Total number of benefits, divided by countries, benefit types and number of benefits in each country
* @ids - array() - Citizen id's from DB. We have to keep track of them, in order to avoid "extra" people in outr results
* @Addresses - array() - Addresses from DB. We have to keep track of them to avoid duplicate addresses in our results
* @$benefit_names() - array() - Keeps the names of benefits, whick we want to count seperatly, All other benefits are "Others"
* /

$total_women = array();
$total_men = array();
$total_benefits = array();
$ids = array();
$Addresses = array();
$benefit_names=array('SSS', 'Coco Life');

//Iterating over recieved results from DB 
foreach($results as $result) {

    //Getting all necessary data
    $id = $result['ID'];
    $address = $result['Address'];
    $sex = $result['Sex'];
    $benefit = $result['benefits'];

    //Ensuring that we don't get duplicate Addresses
    if(!in_array($address, $Addresses)) {
        array_push($Addresses, $address);
    }

    //Ensuring we don't get extra people
    if(!in_array($id, $ids))
    {
        array_push($ids, $id);
        if($sex=='Male') {
            //Dividing men by address
            if(array_key_exists($address, $total_men))
            {
                $total_men[$address]++;
            }
            else
            {
                $total_men[$address] = 1;
            }
        }
        else if($sex=='Female') {
            //Dividing women by address
            if(array_key_exists($address, $total_women))
            {
                $total_women[$address]++;
            }
            else
            {
                $total_women[$address] = 1;
            }
        }
    }

    //Ensuring a person has a benefit
    if($benefit) {
        //Dividing benefits by address
        if(array_key_exists($address, $total_benefits)) {
            //Dividing benefits by benefit name.
            if(in_array($benefit, $benefit_names))
            {
                if(array_key_exists($benefit, $total_benefits[$address])) {
                    $total_benefits[$address][$benefit]++;
                }
                else {
                    $total_benefits[$address][$benefit]=1;
                }
            }
            else if(array_key_exists('Others', $total_benefits[$address]))
            {
                $total_benefits[$address]['Others']++;
            }
            else {
                $total_benefits[$address]['Others'] = 1;
            }
        }
        else {
            if(in_array($benefit, $benefit_names)) {
                $total_benefits[$address][$benefit] = 1;
            }
            else {
                $total_benefits[$address]['Others'] = 1;
            }
        }
    }    
}

//Here after all the manipulations, our hash map looks ugly, to make your life eaier later we fill up the missing parts with 0
foreach($Addresses as $address) {
    foreach($benefit_names as $name) {
        if(!isset($total_benefits[$address][$name])) {
            $total_benefits[$address][$name]=0;
        }
    }
    if(!isset($total_benefits[$address]['Others'])) {
        $total_benefits[$address]['Others']=0;
    }
}

/*

* At this point all the neseccary calculations are made. We only have to take care of outputting the data.
* /

?>

<html>
    <body>
    <table>
        <tr>
            <th>Counry</th>
            <th>Total citizen</th>
            <th>Male</th>
            <th>Female</th>
            <?php
            //Here we make sure that all our separate benefit names get theit own column.
            foreach($benefit_names as $benefit) { ?>
                <th><?php echo $benefit; ?></th>
            <?php } 
            //After we displayed all our separate benefits, we display the "Others" column 
            ?>
            <th>Others</th>
        </tr>
    <?php
        $temp;
        //Here we go through all the unique addresses that we met earlier, ad make a roww for each of them
        foreach($Addresses as $address) { ?>
            <tr>
                <td><?php echo $address; //Outputting current address?></td>
                <td>
                <?php
                    //Here we take care of calculating total citizens based on our current address and output them
                    $total_citizen = 0;
                    if(array_key_exists($address, $total_men)) $total_citizen+=$total_men[$address];
                    if(array_key_exists($address, $total_women)) $total_citizen+=$total_women[$address];
                    echo $total_citizen;
                ?>
                </td>
                <!--Here we display number of women, based on our corrent address-->
                <td><?php if(array_key_exists($address, $total_men)) echo $total_men[$address]; else echo "0"; ?></td>
                <!--Here we display number of men, based on our corrent address-->
                <td><?php if(array_key_exists($address, $total_women)) echo $total_women[$address]; else echo "0"; ?></td>
                <?php
                /*
                *  Here is probably the maddest piece of php code.
                *  We have to make sure that the layout of our data values corresponds with our Column headers layout.
                *  For that, we first iterate over our separate benefit names.
                *  By doing this we ensure that all our seperate benefit names are filled with some data
                *  After we filled all seperate benefits are filled, we make sure that "Others" column is also filled with data
                */
                foreach($benefit_names as $benefit) {
                    //Here we have to make sure that there were some benefits. If there were no benefits, then we fill them with 0.
                    if($total_benefits) { ?>
                        <td><? echo $total_benefits[$address][$benefit]; ?>
                    <?php } else { ?>
                            <td>0</td>
                    <?php }
                }
                //Again, have to make sure that there were some benefits. If there were no benefits, then we fill them with 0.
                if($total_benefits) { ?>
                    <td><?php echo $total_benefits[$address]['Others'] ?></td>
                <?php } else { ?>
                    <td>0<td/>
                <?php } ?>
            </tr>
        <?php } ?>
    </table>
    <body>
</html>

为了更容易理解这段代码的功能，我尽可能多地添加了注解。

你在正确的轨道上。你现在只需要做一个 Left Join 从 Address 餐桌 benefits table。左连接将允许我们考虑 Address 即使没有相应的 benefits 任何公民的入境。
为了计算公民总数，男性人数和女性人数，现在需要使用 COUNT(DISTINCT ID) 加入之后。因为加入可能会产生重复的行，因为一个公民可能有多个好处。
此外，为了计算“其他”好处，我们需要确保 benefit IS NOT NULL 是的 NOT IN ('SSS', 'Coco Life') .
在多表查询中，建议使用别名以提高代码的清晰度（可读性）并避免模棱两可的行为。

SELECT  
  c.Address,
  COUNT(DISTINCT CASE WHEN c.Sex = 'Male' THEN c.ID END) AS male_cnt,
  COUNT(DISTINCT CASE WHEN c.Sex = 'Female' THEN c.ID END) AS female_cnt,
  COUNT(DISTINCT c.ID) AS total_citizen_cnt, 
  COUNT(CASE WHEN b.benefits = 'SSS' THEN 1 END) AS SSS_cnt, 
  COUNT(CASE WHEN b.benefits = 'Coco Life' THEN 1 END) AS Coco_Life_cnt, 
  COUNT(CASE WHEN b.benefits IS NOT NULL AND 
                  b.benefits NOT IN ('SSS', 'Coco Life') THEN 1 END) AS Others_cnt 
FROM citizen AS c 
LEFT JOIN benefits AS b 
  ON b.citizen_ID = c.ID 
GROUP BY c.Address