php - 循环PHP，按组无重复字段，求和mysql

Question

这是我的表“dp”

Year      | Month     | Payment|  Payer_ID | Payment_Recipient |
2008/2009 | July      | 100000 |     1     |        John       |
2008/2009 | August    | 200000 |     1     |        Jane       |
2009/2010 | August    | 150000 |     1     |        Jane       |
2009/2010 | September | 175000 |     1     |        John       |

我的 php 页面需要这样的输出:

Year      |       July     |      August   |     September | 
2008/2009 | 100000 | John  | 200000 | Jane |    0   |   -  | 
2009/2010 |    0   |   -   | 150000 | Jane | 175000 | John |

我使用这个sql查询:

$query_sql = "SELECT
   year
 , SUM(IF(Month='July', Payment, 0)) As 'July'
 , SUM(IF(Month='August', Payment, 0)) As 'August'
 , SUM(IF(Month='September', Payment, 0)) As 'September'
FROM 
 dp
WHERE 
 Payer_ID = 1
GROUP BY
 Year";


<table>
<tr>
<td>year</td>
<td>July</td>
<td>August</td>
<td>September</td>
</tr>
<?php
while($data=mysql_fetch_array($sql)){
?>
<tr>
<td><?php echo $data['year']; ?></td>
<td><?php echo $data['July']; ?></td>
<td><?php echo $data['August']; ?></td>
<td><?php echo $data['September']; ?></td>
</tr>
<?php } ?>
</table>

输出总是这样的:

Year      |  July  | August | September | 
2009/2010 |    0   | 150000 | 175000    |

仅显示 1 年(不循环)。

有人可以帮助我修复“sum(if”中的代码以包含付款收件人并修复 php 中的变量以循环吗？

Answer 1

粗略的 SQL 方法:-

   SELECT DISTINCT dp.Year, SubJuly.Payment, SubJuly.Payment_Recipient, SubAugust.Payment, SubAugust.Payment_Recipient, SubSeptember.Payment, SubSeptember.Payment_Recipient
    FROM dp
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'July'
    ) SubJuly
    ON dp.Year = SubJuly.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'August'
    ) SubAugust
    ON dp.Year = SubAugust.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'September'
    ) SubSeptember
    ON dp.Year = SubSeptember.Year
WHERE  dp.Payer_ID = 1

通过使用月/年列表并针对原始表进行 LEFT JOINing，然后仅处理 php 中的行更改，可能可以更有效地完成此操作。

将其放回到原来的 php 中:-

<?php


$query_sql = "   SELECT DISTINCT dp.Year, SubJuly.Payment, SubJuly.Payment_Recipient, SubAugust.Payment, SubAugust.Payment_Recipient, SubSeptember.Payment, SubSeptember.Payment_Recipient
    FROM dp
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'July'
    ) SubJuly
    ON dp.Year = SubJuly.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'August'
    ) SubAugust
    ON dp.Year = SubAugust.Year
    LEFT OUTER JOIN
    ( 
        SELECT Year, Month, Payment, Payer_ID, Payment_Recipient 
        FROM dp
        WHERE Month = 'September'
    ) SubSeptember
    ON dp.Year = SubSeptember.Year
WHERE  dp.Payer_ID = 1";

....................
?>
<table>
<tr>
<td>year</td>
<td colspan='2'>July</td>
<td colspan='2'>August</td>
<td colspan='2'>September</td>
</tr>
<?php
while($data=mysql_fetch_assoc($sql))
{
    echo "<tr><td>".implode("</td><td>", $data)."</td></tr>";
} 
?>
</table>

请注意，您可能应该使用 mysqli_* 函数而不是 mysql_* 函数。