使用PHP和MySQL的动态表格/矩阵
使用PHP和MySQL的动态表格/矩阵
提问于 2013-04-14 18:17:02
我在尝试以表格格式显示MySQL数据时遇到问题。基本上，我有一个表，其中存储付款与字段金额，项目，货币和地点。我想在一个表格中显示这些内容，该表格显示顶部的位置，第二行上的货币，第一列行中的项目，然后是表中与位置/货币相对应的其余部分的支付总和。下面是我的MySQL表数据的一个示例：
item | amount | currency | location
----------------------------------------------
Item 1 | 250 | USD | UK
Item 2 | 450 | GBP | UK
Item 3 | 780 | EUR | Germany
Item 2 | 50 | GBP | Spain
Item 2 | 150 | GBP | Spain
Item 4 | 375 | USD | UK
Item 4 | 650 | GBP | Germany
Item 1 | 350 | USD | UK我希望实现的最终结果是：
| Germany | Spain | UK |
| EUR | GBP | GBP | GBP | USD |
---------------------------------
Item 1 | | | | | 600 |
Item 2 | | | 200 | 450 | |
Item 3 | 780 | | | | |
Item 4 | | 650 | | | 375 |目前，我正在使用多个查询来实现结果，这些查询对每个项目使用了大量嵌套查询，如下所示：
<table width="1046" border="0">
<tr>
<td></td>
<td colspan="2">Germany</td>
<td>Spain</td>
<td colspan="2">UK</td>
</tr>
<tr>
<td></td>
<td>EUR</td>
<td>GBP</td>
<td>GBP</td>
<td>GBP</td>
<td>USD</td>
</tr>
<? $q1 = mysqli_query("SELECT item FROM table1 group by item order by item asc");
while($row1 = mysqli_fetch_assoc($q1)){
$item = $row1['item']; ?>
<tr>
<td><? echo $item; ?></td>
<? $q2 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='EUR' and item='$item' and location='Germany'");
while($row2 = mysqli_fetch_assoc($q2)){ ?>
<td><? echo number_format($row2['amt'],0); ?></td>
<? } ?>
<? $q3 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='Germany'");
while($row3 = mysqli_fetch_assoc($q3)){ ?>
<td><? echo number_format($row3['amt'],0); ?></td>
<? } ?>
<? $q4 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='Spain'");
while($row4 = mysqli_fetch_assoc($q4)){ ?>
<td><? echo number_format($row4['amt'],0); ?></td>
<? } ?>
<? $q5 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='GBP' and item='$item' and location='UK'");
while($row5 = mysqli_fetch_assoc($q5)){ ?>
<td><? echo number_format($row5['amt'],0); ?></td>
<? } ?>
<? $q6 = mysqli_query("SELECT sum(amount) as amt from table1 WHERE currency='USD' and item='$item' and location='UK'");
while($row6 = mysqli_fetch_assoc($q6)){ ?>
<td><? echo number_format($row6['amt'],0); ?></td>
<? } ?>
</tr>
<? } ?>
</table>我想知道是否有更好的方法来达到同样的结果？
回答 1
Stack Overflow用户
发布于 2013-04-14 18:55:40
MySQL没有PIVOT函数，但如果您希望在一个查询中检索此数据，则可以使用带有CASE表达式的聚合函数将行中的数据放入列中：
select item,
sum(case when location = 'Germany' and currency = 'EUR' then amount else 0 end) Ger_EUR,
sum(case when location = 'Germany' and currency = 'GBP' then amount else 0 end) Ger_GBP,
sum(case when location = 'Spain' and currency = 'GBP' then amount else 0 end) Spa_GBP,
sum(case when location = 'UK' and currency = 'GBP' then amount else 0 end) UK_GBP,
sum(case when location = 'UK' and currency = 'USD' then amount else 0 end) UK_USD
from table1
group by item;参见SQL Fiddle with Demo。这给出了结果：
| ITEM | GER_EUR | GER_GBP | SPA_GBP | UK_GBP | UK_USD |
----------------------------------------------------------
| Item 1 | 0 | 0 | 0 | 0 | 600 |
| Item 2 | 0 | 0 | 200 | 450 | 0 |
| Item 3 | 780 | 0 | 0 | 0 | 0 |
| Item 4 | 0 | 650 | 0 | 0 | 375 |页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15997952
复制相关文章
相似问题