php - 组重复值

我有下表叫商店物品

id store item qty cost sell date
1  A1     aa   12  5   10   2016-04-06
2  A2     cc   10  6   12   2016-04-06 // Store A2 and A3 have same item
3  A1     bb   15  5   13   2016-04-01 // Store A1 and A3 have same item
4  A3     bb   10  5   13   2016-04-06
5  A3     cc   22  6   12   2016-04-06
6  A1     dd   17  2   12   2016-04-02
7  A1     ee   10  5   10   2016-04-01

现在我期望的结果是：

Item cost sell A1 A2 A3
aa   5    10   12 0  0
bb   5    13   15 0  10
cc   6    12   0  10 22
dd   2    12   17 0  0
ee   5    10   10 0  0

正如您所看到的，每个项目都显示一次，其数量列在每个商店的下面，我现在对如何在查询中使用group感到困惑
代码：

$allstore = $_POST['store']; //Store Name are collected from FORM submit

echo "<table>";
echo "<tr><td align=center>Item Number</td><td align=center>Cost</td><td align=center>Sell</td>";

foreach($allstore as $store => $value){
//I've used foreach to find out which store being selected from checkbox form
    echo "<td align=center>".$value."</td>";
}

echo "</tr>";


    $query = "SELECT * from store_items GROUP by item"; //??
    $result = mysql_query($query);

    while($row = mysql_fetch_assoc($result)){

        echo "<tr><td align=center>{$row['item']}</td><td align=center>{$row['cost']}</td><td align=center>{$row['sell']}</td>";

    foreach($allstore as $store => $value){
    echo "<td align=center>".$row['qty']."</td>";
}

    //End of While loop
    }
echo "</tr>";

最佳答案

您应该使用这样的查询：

SELECT Item, cost, sell
, Sum(CASE WHEN store = 'A1' then qty end) A1
, Sum(CASE WHEN store = 'A2' then qty end) A2
, Sum(CASE WHEN store = 'A3' then qty end) A3
FROM store_items
GROUP BY Item, cost, sell

编辑：
数据查询：

SELECT Item, cost, sell, store, Sum(qty)
FROM store_items
GROUP BY Item, cost, sell, store

或

SELECT Item, cost, sell, Sum(qty)
FROM store_items
WHERE store = 'A1' --for example
GROUP BY Item, cost, sell

关于php - 组重复值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36437940/