php - 使用 LEFT JOIN 未按预期工作

Question

我有下表:

商店列表

id store
1    m1
2    m2 
3    m3

包含商店名称，每个商店名称都有自己的已售商品表格，如下所示:

s_m1_sales

id  item  qty  date
1    x1    5   16-5-2016
2    x2    6   16-5-2016
3    x3    1   16-5-2016

s_m2_sales

id  item  qty  date
1    x2    3   16-5-2016
2    x2    3   16-5-2016
3    x2    5   16-5-2016

第三家商店依此类推...

最终表格:

商店商品

id  item  qty  cat  sub
10  x1   15    lady  slip
11  x2   10    lady  shoe
12  x3   10    lady  shoe

现在我正在寻找一份报告，以查找特定类别(猫)在特定日期内售出的商品数量以及按类别分组。我也需要这样的东西:

cat   sub      M1   M2  M3   date
lady  slip     5    0   0     16-5-2016
lady  shoe     7    11  0     16-5-2016

我的 PHP 代码，我使用了 LEFT JOIN 并正确输出结果:

//rest of code 

//Table created 
echo "<table width=90% height=% align=center border=1 cellpadding=3 cellspacing=0>";
echo "<tr>
    <td align=center width=12%><b>Supplier</b></td>
    <td align=center width=12%><b>Category</b></td>
    <td align=center width=12%><b>Sub-Category</b></td>
";

foreach($allstore as $store => $value){
    echo "<td align=center width=5%><b><font color=green size=+1>".$value."</font></b></td>";
}

//$allstore is an array , by which values taken from user checkbox select
$allstore = $_POST['store'];

foreach($allstore as $store => $value){

    $query = "SELECT s_{$value}_sales.item_no,
            store_items.cat,
            store_items.supplier,
            store_items.sub, 
            SUM(s_{$value}_sales.qty) AS a1
      FROM s_{$value}_sales 
      LEFT JOIN store_items ON s_{$value}_sales.item_no = store_items.item_no 
            AND store_items.cat = '".$_POST['cat']."' 
            AND s_{$value}_sales.date BETWEEN '".$_POST['fdate']."' AND '".$_POST['tdate']."' 
            AND store_items.store = '".$value."'
      GROUP by store_items.sub
    ";

    $result=  mysql_query($query);

    while($row = mysql_fetch_assoc($result)) {
        echo "<tr><td align=center width=12%>{$row['supplier']}</td>
        <td align=center width=12%>{$row['cat']}</td>
        <td align=center width=12%>{$row['sub']}</td>
        <td align=center>".$row['a1']."</td>
    ";
    }
}
echo "</tr></table>";
//rest of code..

正如我之前提到的，输出是正确的，但是它多次循环类别和子类别会发生什么情况。我不想在每列中列出商店及其总数量。我该怎么做？

上面的PHP输出如下:

cat  sub   M1   M2   M3
lady slip  5
lady shoe  7
lady slip  0
lady shoe  11
lady slip  0
lady shoe  0

Answer 1

为什么每个商店的销售额都有一个单独的表？您应该能够轻松地将它们全部组合到一个带有商店 id 列的表中。这也将有助于解决您的问题，即您正在为每个商店循环进行查询。使用这种方法，如果没有 PHP 中的一些后处理，您将永远无法将跨商店的数据编译为单行。

让我提出一个替代的 store_sales 表结构，如下所示:

id  store_id  item_id  qty  date
1      1      10       5   16-5-2016
2      1      11       6   16-5-2016
3      1      12       1   16-5-2016
4      2      11       3   16-5-2016
5      2      11       3   16-5-2016
6      2      11       5   16-5-2016
...

请注意 store_id 字段的添加以及将 item 字段替换为 item_id 字段(您应该在此处引用适当的外键)。

这使您可以将查询简化为:

SELECT
    store_list.store AS store,
    store_items.cat AS cat,
    store_items.sub AS sub,
    SUM(store_sales.qty) AS qty
FROM store_list
LEFT JOIN store_sales
    ON store_list.id = store_sales.store_id
INNER JOIN store_items
    ON store_sales.item_id = store_items.id
WHERE store_sales.date BETWEEN ? AND ?
GROUP BY `store`, `cat`, `sub`
ORDER BY `store` ASC, `cat` ASC, `sub` ASC

这将给出如下结果集:

store  cat   sub   qty
m1     lady  slip  5
m1     lady  shoe  7
m2     lady  shoe  11
m3     null  null  0

在 PHP 中读取多维数组并以您为页面选择的任何格式显示是很简单的。我认为尝试将数据查询为您所显示的“数据透视表”类型的格式没有多大值(value)，因为这只会使查询变得更复杂且性能更低。这可以在选择中使用 case 语句来完成，但我不想让我的答案的焦点变得困惑，这主要是为了让你的数据库模式井然有序。