如何在MySQL中显示导致计算总数的所有单独字段 - How can I display all the individual fields that led to the calculated total in MySQL?

How can I display all the individual fields that led to the calculated total in MySQL?

本文关键字：字段单独计算 MySQL 显示 | 更新日期: 2023-09-27

我想得到一些帮助来解决我的问题，我正在显示一段时间内获得的总销售额，我已经成功地做到了这一点，我现在需要的是如何显示导致总销售额的所有单个价格。例如，总价是10，我买了4个苹果，6个桔子，10就是总价。

我在这里有我的查询显示它在一个时间段使用输入类型日期，

$datefrom   = $_GET['datefrom'];
$dateto     = $_GET['dateto'];
$qry = "SELECT sum(order_detail.quantity*order_detail.price) as chenes, orders.date
        FROM order_detail 
        LEFT JOIN orders ON order_detail.orderid=orders.serial
        WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";
mysql_set_charset("UTF8");
$result = @mysql_query($qry);
while ($row=mysql_fetch_array($result)){
    echo "Sales from ".date("F d Y",strtotime($datefrom))." ";
    echo "to ".date("F d Y",strtotime($dateto))." are a total of ";
    echo "₱".number_format($row['chenes'],2);
}

这是我的代码，用于显示每个orderid的单独价格。

$AAA = "SELECT order_detail.quantity,order_detail.price,order_detail.orderid, orders.date
        FROM order_detail 
        LEFT JOIN orders ON order_detail.orderid=orders.serial
        WHERE orderid=3";
$BBB=@mysql_query($AAA);
while($CCC=mysql_fetch_array($BBB)){
    echo "<br>".$CCC['price']."<br>";
}

它可以工作，但到目前为止，我不知道如何在没有orderid的情况下自动显示它。

请帮我显示每个的价格，因为到目前为止我只显示了总数，而我现在需要的是显示总数，它是分解的。我已经尝试了上面的查询，但到目前为止我被卡住了。请帮忙。非常感谢。

通过使用此查询，您将在每个订单ID的不同两列中获得逗号分隔的数量和价格。

SELECT GROUP_CONCAT(IFNULL(od.quantity,0)) AS quantity,
       GROUP_CONCAT(IFNULL(od.price,0)) AS price,
       od.orderid,o.date
FROM orders o
    LEFT JOIN order_detail od ON od.orderid=o.serial
GROUP BY o.serial;

我修复了您的代码。

$qry = "SELECT sum(order_detail.quantity*order_detail.price) as chenes, orders.date, order_detail.price
        FROM order_detail 
        LEFT JOIN orders 
        ON order_detail.orderid=orders.serial
        WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";
        $result = @mysql_query($qry);
        while ($row=mysql_fetch_array($result)){
        echo "Sales from ".date("F d Y",strtotime($datefrom))." ";
        echo "to ".date("F d Y",strtotime($dateto))." are a total of ";
        echo "₱".number_format($row['chenes'],2);
            $AAA = "SELECT order_detail.quantity*order_detail.price as xax,order_detail.price,order_detail.orderid, orders.date
                FROM order_detail 
                LEFT JOIN orders 
                ON order_detail.orderid=orders.serial
                WHERE date(orders.date) BETWEEN '$datefrom' AND '$dateto'";
            $BBB=@mysql_query($AAA);
            while($CCC=mysql_fetch_array($BBB)){
                echo "<br>".$CCC['xax']."<br>";
            }
        }

根据我读到的评论，以下是我所做的。创建了一个新的查询，基于您的帖子并添加了WHERE子句。