如何在mysql中将ot_total和ot_shipping放在一行中？

Question

如何在mysql中将ot_total和ot_shipping放在一行中？

我想从我们的桌子上得到每一份订单的费用。我怎样才能在mysql中得到这个表？

非常感谢。

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `ot`
-- ----------------------------
DROP TABLE IF EXISTS `ot`;
CREATE TABLE `ot` (
  `id` int(10) NOT NULL auto_increment,
  `orders_id` int(10) default NULL,
  `class` varchar(30) default NULL,
  `value` float(30,0) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ot
-- ----------------------------
INSERT INTO `ot` VALUES ('1', '1', 'ot_shipping', '10');
INSERT INTO `ot` VALUES ('2', '1', 'ot_total', '100');
INSERT INTO `ot` VALUES ('3', '1', 'ot_insurance', '1');
INSERT INTO `ot` VALUES ('4', '2', 'ot_shipping', '10');
INSERT INTO `ot` VALUES ('5', '2', 'ot_total', '80');

​

​

​

​

Answer 1

select orders_id, 
       sum(case when class='ot_shipping' then value else 0 end) as shipping,
       sum(case when class='ot_total' then value else 0 end) as total
from ot
GROUP BY orders_id;

SqlFiddle

Answer 2

select o.orders_id, 
       sum(case when o.class='ot_shipping' then o.value else 0 end) as ot_shipping,
       sum(case when o.class='ot_total' then o.value else 0 end) as ot_total
from ot o
GROUP BY o.orders_id;