如何将MySQL查询的SUM求和改为按日期分组的乘法计算？

Hey there! Let's fix this up for you. Since MySQL doesn't have a built-in PRODUCT() aggregate function (unlike SUM() which you were using), we need a clever workaround to calculate the product of course values grouped by date.

Step 1: The Math Behind Grouped Product Calculation #

To get the product of values in a group, we'll use a combination of MySQL's mathematical functions:

• LN(): Takes the natural logarithm of each course value
• SUM(): Adds up all those logarithms (since log(a*b) = log(a) + log(b))
• EXP(): Converts the summed logarithm back to the original product (the inverse of LN())

The core formula looks like this:

EXP(SUM(LN(course))) AS total_course_product

Important Note: This works only if all course values are positive non-zero numbers. If your data includes zeros or negative values, you'll need extra logic to handle those edge cases (we'll cover that later).

Step 2: Modify Your index.php Query #

Assuming your original mysqli query looked something like this:

// Original SUM-based query
$query = "SELECT date, SUM(course) AS total_course FROM people GROUP BY date";

Replace it with the product calculation query:

// Updated product-based query
$query = "SELECT date, EXP(SUM(LN(course))) AS total_course_product FROM people GROUP BY date";

Step 3: Update Result Handling in PHP #

If you were displaying the sum before, adjust your output code to show the product instead. Here's a full example including connection and result rendering:

// Assuming your mysqli connection is already set up (e.g., $conn = mysqli_connect(...))
$result = mysqli_query($conn, $query);

if (mysqli_num_rows($result) > 0) {
    // Output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        // Format the product to 2 decimal places for readability
        echo "Date: " . $row["date"] . " | Product of Courses: " . number_format($row["total_course_product"], 2) . "<br>";
    }
} else {
    echo "No results found";
}

// Don't forget to close the connection when done
mysqli_close($conn);

Edge Case Handling #

If course can be 0 #

If any value in a date group is 0, the entire product becomes 0. Add a CASE statement to handle this explicitly:

SELECT 
    date,
    CASE 
        WHEN SUM(CASE WHEN course = 0 THEN 1 ELSE 0 END) > 0 THEN 0
        ELSE EXP(SUM(LN(course)))
    END AS total_course_product
FROM people 
GROUP BY date

If course can be negative #

Negative values will break the logarithm calculation. We need to track the number of negatives to set the correct sign of the product:

SELECT 
    date,
    CASE 
        WHEN SUM(CASE WHEN course = 0 THEN 1 ELSE 0 END) > 0 THEN 0
        ELSE 
            -- Determine sign: even number of negatives = positive, odd = negative
            SIGN(COUNT(CASE WHEN course < 0 THEN 1 END) % 2) * EXP(SUM(LN(ABS(course))))
    END AS total_course_product
FROM people 
GROUP BY date

内容的提问来源于stack exchange，提问作者kuballo