在MySQL的同一个表中,基于不同组的条件生成多个和结果的最佳方法是什么?
我相信我选择不好,但我无法找到更好的方法.
想象一下,我有一个订单表,我跟踪订单的客户,它的价值和订单的一些限定符(类型).我想对按订单类型组合分组的给定客户的总订单求和:
delimiter $$
CREATE TABLE `orders` (
`idorder` int(11) NOT NULL,
`value` double DEFAULT NULL,
`idclient` int(11) DEFAULT NULL,
`type1` int(11) DEFAULT NULL,
`type2` int(11) DEFAULT NULL,
`type3` bit(1) DEFAULT NULL,
PRIMARY KEY (`idorder`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
我的选择:
SELECT T0.idclient, T1.condition1, T2.condition2, T3.condition3 FROM
(SELECT 1 AS idclient) AS T0
LEFT JOIN
(SELECT idclient, SUM(value) condition1 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 0) AS T1
ON T0.idclient = T1.idcli