SELECT so.*,t1.时间 AS 最后记账时间,IFNULL(SUM(t1.金额),0) AS 总金额,IFNULL(SUM(金额),0) AS 定单金额,IFNULL(SUM(t3.金额),0) AS 销售单金额'
.' FROM 订单表 AS so LEFT JOIN '
.'(SELECT ID,金额,编号,时间 FROM 流水表 WHERE 类型 = 1 order by datatime desc)t1 ON ((t1.编号 = so.订单号) OR (t1.编号 = so.定单号)) LEFT JOIN '
//.'(SELECT ID,金额,编号 FROM 流水表 WHERE 类型 = 1)t2 ON t2.编号 = so.定单号 LEFT JOIN '
.'流水表 AS t2 ON t2.编号 = so.定单号 AND t2.类型 = 1 LEFT JOIN '
.'(SELECT 金额,编号 FROM 流水表 WHERE 类型 = 1 AND 科目 = "销售单定金")t3 ON t3.编号 = so.订单
我要根据订单里面的订单号或者定单号,多次求和得到订单总金额、定单金额及销售单金额,所有金额都记录在流水表的金额中,通过类型及编号来区分,我试了很久,销售金额或者定定定单金额求和总是会有误差,总是会多出二三倍,我估计应该是三个查询会把把的有查询结果汇总。
求高手指点这个联合查询应该怎么写。
为了方便大家读懂,我已经把表名及字段名等都换成了中文。phpmysql
.' FROM 订单表 AS so LEFT JOIN '
.'(SELECT ID,金额,编号,时间 FROM 流水表 WHERE 类型 = 1 order by datatime desc)t1 ON ((t1.编号 = so.订单号) OR (t1.编号 = so.定单号)) LEFT JOIN '
//.'(SELECT ID,金额,编号 FROM 流水表 WHERE 类型 = 1)t2 ON t2.编号 = so.定单号 LEFT JOIN '
.'流水表 AS t2 ON t2.编号 = so.定单号 AND t2.类型 = 1 LEFT JOIN '
.'(SELECT 金额,编号 FROM 流水表 WHERE 类型 = 1 AND 科目 = "销售单定金")t3 ON t3.编号 = so.订单
我要根据订单里面的订单号或者定单号,多次求和得到订单总金额、定单金额及销售单金额,所有金额都记录在流水表的金额中,通过类型及编号来区分,我试了很久,销售金额或者定定定单金额求和总是会有误差,总是会多出二三倍,我估计应该是三个查询会把把的有查询结果汇总。
求高手指点这个联合查询应该怎么写。
为了方便大家读懂,我已经把表名及字段名等都换成了中文。phpmysql
应为
SUM(IFNULL(t1.金额,0))
$OrderSql = 'SELECT so.*,di.DistributorName,di.showName AS distributorShowName,'
.'sd.fullName AS driverStaffName,sd.number AS driverStaffNum,'
.'d.fullName AS dischargerStaffName,d.number AS dischargerStaffNum,'
.'s.fullName AS sellStaffName,s.number AS sellStaffNum,'
.'o.fullName AS orderStaffName,o.number AS orderStaffNum,'
.'c.fullName AS cancelStaffName,c.number AS cancelStaffNum,'
.'IFNULL(SUM(sb.totalBackAmount),0) AS sellBackTotalAmount,'
.'t1.datatime AS debtCollectionTime'
.',SUM(if(t1.incomeSpending = 1,t1.amount,0)) as totalIncludeAmount'
.',SUM(if(t1.basedNum = so.orderGoodsOrderNum,t1.amount,0)) as orderReceivedDeposit'
.',SUM(if(t1.captionName = "销售单定金",t1.amount,0)) as sellReceivedDeposit'
.',SUM(if(t1.captionName = "销售回款",t1.amount,0)) as backAmount'
.',SUM(if(t1.captionName = "销售退款",t1.amount,0)) as SellBackReceivedAmount'
.' FROM '.C('DB_PREFIX').'sell_order AS so LEFT JOIN '
.C('DB_PREFIX').'staff AS d ON d.ID = so.dischargerStaffID LEFT JOIN '
.C('DB_PREFIX').'staff AS sd ON sd.ID = so.driverStaffID LEFT JOIN '
.C('DB_PREFIX').'staff AS s ON s.ID = so.sellStaffID LEFT JOIN '
.C('DB_PREFIX').'staff AS o ON o.ID = so.orderStaffID LEFT JOIN '
.C('DB_PREFIX').'staff AS c ON c.ID = so.cancelStaffID LEFT JOIN '
.C('DB_PREFIX').'distributor AS di ON di.ID = so.distributorID LEFT JOIN '
.C('DB_PREFIX').'sell_back_order AS sb ON sb.sellOrderNum = so.orderNum LEFT JOIN '
.'(SELECT amount,basedNum,datatime,captionName,incomeSpending FROM '.C('DB_PREFIX').'general_journal group by ID order by datatime desc)t1 ON ((t1.basedNum = so.orderNum) OR (t1.basedNum = so.orderGoodsOrderNum) OR (t1.basedNum IN (sb.orderNum)))'
.' WHERE 1 = 1';
主要是这个重复了。最多的居然重复取出了四条!!回款金额直接翻四倍!!!
so 是左表 t1 是右表,中间的先忽略不计连接条件有 3 个 且是或条件
必然导致结果中属于 t1 的记录增多