代码之家 › 专栏 › 技术社区 › Kardo

如何基于mysql中一个表中的其他两个列设置列值?

inner-join mysql

Kardo · 技术社区 · 8 年前

在mySql中,我试图设置列的值( total )基于同一表中其他两列的值。

CREATE TABLE IF NOT EXISTS `order_total` (
    `order_total_id` int(10) NOT NULL AUTO_INCREMENT,
    `order_id` int(11) NOT NULL,
    `code` varchar(32) NOT NULL,
    `title` varchar(255) NOT NULL,
    `value` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `sort_order` int(3) NOT NULL,
    PRIMARY KEY (`order_total_id`),
    KEY `order_id` (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=244 DEFAULT CHARSET=utf8;

INSERT INTO `order_total` (`order_total_id`, `order_id`, `code`, `title`, `value`, `sort_order`) VALUES
(241, 80, 'sub_total', 'Sub-Total', '400.0000', 1),
(242, 80, 'shipping', 'Free Shipping', '10.0000', 3),
(243, 80, 'total', 'Total', '0', 9);

我尝试了这样一个在本地mySql中工作的代码,但我想可能有更好的方法来实现它。

UPDATE order_total ot 
INNER JOIN (
    SELECT order_id, value
    FROM order_total
        WHERE code = 'sub_total'
    GROUP BY order_id
) o ON ot.order_id = o.order_id
INNER JOIN (
    SELECT order_id, value
    FROM order_total
        WHERE code = 'shipping'
    GROUP BY order_id
) o2 ON ot.order_id = o2.order_id
SET ot.value = o.value + o2.value 
WHERE ot.code = 'total' AND ot.order_id = 80

如何提高效率?

2 回复 | 直到 8 年前

Md Monjur Ul Hasan 8 年前

您可以使用一个查询计算总数,如下所示:

update order_total ot 
INNER JOIN (
   SELECT order_id, sum(value) value
   FROM order_total
   WHERE code = 'sub_total' or code= 'shipping'
   GROUP BY order_id
) o ON ot.order_id = o.order_id
SET ot.value = o.value
WHERE ot.code = 'total' AND ot.order_id = 80;

Tim Biegeleisen 8 年前

当前查询在技术上无效,因为子查询正在选择未显示在 GROUP BY 条款但我们可以通过使用条件聚合来查找每个订单的小计和发货值来解决此问题,并使查询更加简洁:

UPDATE order_total ot 
INNER JOIN
(
    SELECT
        order_id,
        MAX(CASE WHEN code = 'sub_total' THEN value END) AS sub_value,
        MAX(CASE WHEN code = 'shipping'  THEN value END) AS shipping_value,
    FROM order_total
    GROUP BY order_id
) o
    ON ot.order_id = o.order_id
SET ot.value = o.sub_value + o.shipping_value
WHERE
    ot.code = 'total' AND
    ot.order_id = 80;

对于这个特殊的问题,公认的答案是要走的路。但如果你想要的不是总和,那就行不通了。我的回答是让你这样做:

SET ot.value = o.sub_value + 2*o.shipping_value

也就是说,如果我们想将装运价值的权重设为2,那么这个答案可以很容易地做到这一点。