gpt4 book ai didi

mysql - 在 MySQL 中获取 2 个值的总和(1 在子查询中)

转载 作者:行者123 更新时间:2023-11-29 22:46:15 25 4
gpt4 key购买 nike

我对这个(应该很简单的)问题束手无策。我认为我已经完成了所有艰苦的工作(因为这个 MySQL SELECT 语句相当复杂,恕我直言(无论如何对我来说)。

我正在尝试从 MySQL SELECT 语句获取两个关键数字字段的 SUM 值。我尝试了明显的变化,但是我无法使子查询中的字段值“Regs”可供父查询执行类似... SUM(Regs+Held) AS Total 的操作。拜托,拜托,帮助!

这是我的 SQL 代码...

    SELECT         p.product_id AS ID,        FROM_UNIXTIME(p.cdate, '%c/%e/%y') AS CDate,        FROM_UNIXTIME(p.mdate, '%c/%e/%y') AS MDate,        p.product_sku AS SKU,        s.mf_category_city AS Town,        s.mf_category_county AS County,        s.mf_category_state AS State,        org.mf_name AS Org,        s.mf_category_name AS Site,        p.product_name AS ClassName,        DATE_FORMAT(p.startDate, '%a %m-%d-%Y') AS StartDate,        DATE_FORMAT(p.endDate, '%a %m-%d-%Y') AS EndDate,        DATE_FORMAT(p.startTime, '%l:%i %p') AS StartTime,        DATE_FORMAT(p.endTime, '%l:%i %p') AS EndTime,        p.age_from,        p.age_to,        pr.product_price AS Price,        CASE             WHEN p.class_status = 0 THEN 'A'             WHEN p.class_status = 1 THEN 'C'             WHEN p.class_status = 2 THEN 'S'        END AS Status,        p.product_publish AS Published,        p.precode AS Code,        CASE             WHEN p.product_refered = 'N' THEN 'USG'             WHEN p.product_refered = 'Y' THEN 'REC'             WHEN p.product_refered = 'B' THEN 'BOTH'        END AS REC,        p.class_target AS Target,        (SELECT SUM(oi.product_quantity) FROM (jos_sport_childs_xrf AS scx)         LEFT JOIN jos_vm_order_item AS oi ON scx.product_id = oi.product_id         WHERE scx.product_id = p.product_id AND oi.order_status IN ('C','P')             GROUP BY scx.child_id LIMIT 1) AS Regs,        p.class_held AS Held,        p.total_registrations AS Total,        p.site_usage_fee AS Fee,        p.player_usage_fee AS PlayerFee,        p.player_usage_percent AS PlayerPercentage,        u.name AS RD,        p.product_desc AS Notes,        CASE             WHEN cxref.category_parent_id = 42 THEN 'USSI'            WHEN cxref.category_parent_id = 98 THEN 'USSI'         END AS Company,        p.belongs_to AS BelongsTo,        p.missdate1 AS Missdate1,        p.missdate2 AS Missdate2,        p.missdate3 AS Missdate3    FROM (jos_vm_product AS p)    INNER JOIN jos_vm_manufacturer_category AS s ON p.venue = s.mf_category_id    LEFT JOIN jos_vm_product_price AS pr ON pr.product_id = p.product_id    INNER JOIN jos_users AS u ON p.user_id = u.id    INNER JOIN jos_vm_product_category_xref AS x ON p.product_id = x.product_id    INNER JOIN jos_vm_category_xref AS cxref ON x.category_id = cxref.category_child_id    LEFT JOIN jos_vm_manufacturer AS org ON s.mf_category_org = org.manufacturer_id    WHERE p.startDate BETWEEN '2015-03-02' AND '2015-06-14'     AND x.category_id IN (102,120,99,106,104,94,59,84,105)     AND u.id NOT IN (33731,46660)

Held 有一个简单的数值,子查询返回记录的 SUM 总值。

最佳答案

让我们单独看看子查询,没有相关引用,所以...

SELECT SUM(oi.product_quantity) 
, scx.product_id
FROM jos_sport_childs_xrf scx
JOIN jos_vm_order_item oi
ON scx.product_id = oi.product_id
WHERE oi.order_status IN ('C','P')
GROUP
BY scx.child_id;

这里我们SELECTproduct_id(我们这样做是因为我们知道稍后会需要它),但我们GROUP BYchild_id。这不好。我们应该做什么?

关于mysql - 在 MySQL 中获取 2 个值的总和(1 在子查询中),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29108328/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com