php - 如何汇总查询中的结果

Question

我有以下查询:

SELECT cb.customers_id, cb.products_id, p.products_model, pd.products_name, cb.customers_basket_quantity, p.products_price, (p.products_price * cb.customers_basket_quantity) AS product_total
    FROM customers_basket cb, products p, products_description pd
    WHERE cb.customers_id =194075
        AND cb.products_id = pd.products_id
        AND p.products_id = pd.products_id

我试图弄清楚是否可以获得“cart_total”而无需执行其他查询，或使用 PHP 来保持运行总计。

我不确定是否可以使用 CASE 语句来做某事。

建议？

Answer 1

SELECT cb.customers_id, cb.products_id, p.products_model, pd.products_name, cb.customers_basket_quantity, p.products_price, (p.products_price * cb.customers_basket_quantity) AS product_total,

(SELECT sum(p.products_price * cb.customers_basket_quantity) 
FROM customers_basket cb, products p
WHERE cb.customers_id =194075
AND cb.products_id = p.products_id
group by cb.customers_id) AS cart_total

FROM customers_basket cb, products p, products_description pd
WHERE cb.customers_id =194075
AND cb.products_id = pd.products_id
AND p.products_id = pd.products_id

指向 Fiddle 的链接