Я хочу отобразить сводку, подобную этой, на основе двух таблиц

Мои две таблицы MYSQL выглядят следующим образом:

Таблица 1: гражданин

=============================
ID |  Name | Sex    | Address |
=============================
5  | James | Male   | India
6  | Shella|Female  | India
7  | Jan   | Male   | NY
8  | May   | Female | USA
==============================

Таблица 2: преимущества

==========================
ID| citizen_ID | benefits
==========================
1 | 5          | SSS
2 | 6          | Coco Life
3 | 7          | SSS
4 | 7          | Sunlife
==========================

Я хочу отобразить это выглядит так:

====================================================================
Address | Total Citizen | Male | Female | SSS | Coco Life | Others |
====================================================================
India   | 2             |  1   |  1     |  1  |    1      |   0    |
NY      | 1             |  1   |  0     |  1  |    0      |   1    |
USA     | 1             |  0   |  1     |  0  |    0      |   0    |
==================================================================

Кто-нибудь может дать мне подсказку о том, как это сделать?
Мой начальный код:

        $sql = "SELECT  Address,
count(case when Sex='Male' then 1 end) as male_cnt,
count(case when Sex='Female' then 1 end) as female_cnt,
count(*) as total_cnt FROM citizen
GROUP BY Address";

1

Решение

Ты на правильном пути. Теперь вам просто нужно сделать Left Join от Address стол к benefits Таблица. Левое объединение позволяет нам рассмотреть Address даже когда нет соответствующего benefits вход для любого из его граждан.

Чтобы подсчитать общее количество граждан, мужчин и женщин, вам нужно использовать COUNT(DISTINCT ID) после присоединения. Поскольку присоединение может создавать повторяющиеся строки, у гражданина может быть несколько преимуществ.

Кроме того, для подсчета «других» выгод нам необходимо benefit IS NOT NULL и это NOT IN ('SSS', 'Coco Life'),

В многостоловых запросах целесообразно использовать Aliasing для ясности кода (удобочитаемости) и избежания неоднозначного поведения.

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
2

Другие решения

После бесчисленных часов тестирования, получения помощи от кота в написании кода, пении шаманских песен и т. Д. Я думаю, что я сделал пример, который дает желаемый результат. Я проверил это немного, и это похоже на работу.

Тем не менее, независимо от того, что я делал, 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>

Я разместил комментарии настолько, насколько мог, чтобы было легче понять, что делает этот кусок кода.

1

По вопросам рекламы ammmcru@yandex.ru
Adblock
detector