- 論壇徽章:
- 0
|
explain
select sum(ifnull(mb.money,mo.transmoney)) transmoney,mb.mptid,mo.sid
from crm_member_order_history mo
left join crm_combined_bill_history mb on mb.moid = mo.moid
left join alp_member m on m.amid = mo.amid
where mo.type in (1,2,6) and mo.transdate > '2015-01-16 00:00:00'
and mo.transdate < '2015-01-18 23:59:59'
and mo.sid in (20,24,25,26,27,28,29,30,229,230,300,301,429,452,988,1116,1117,1165,1196,1240,1337 )
group by mo.sid,mb.mptid;
explain結(jié)果：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mo
type: ALL
possible_keys: crm_member_order_history_alp_shop_FK,idx_type_transdate_sid
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: mb
type: ref
possible_keys: crm_combined_bill_history_crm_member_order_history_fk
key: crm_combined_bill_history_crm_member_order_history_fk
key_len: 4
ref: alipos.mo.moid
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: m
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: alipos.mo.amid
rows: 1
Extra: Using index
3 rows in set (0.00 sec)
-----------------------------------------------------------------------------------------------------------------------------
crm_member_order_history
CREATE TABLE `crm_member_order_history` (
`moid` int(11) NOT NULL AUTO_INCREMENT,
`type` char(5) COLLATE utf8_unicode_ci NOT NULL,
`operation_type` tinyint(4) NOT NULL,
`serialnumber` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`transmoney` decimal(10,2) DEFAULT '0.00',
`other_pay` decimal(10,2) DEFAULT '0.00',
`transcredit` decimal(10,2) DEFAULT '0.00',
`presented` decimal(10,2) DEFAULT '0.00',
`balance` decimal(10,2) DEFAULT '0.00',
`transdate` datetime DEFAULT NULL,
`cardcode` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`cardmbid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`transtatus` tinyint(4) DEFAULT NULL,
`muid` int(11) DEFAULT NULL,
`sid` int(11) DEFAULT NULL,
`maid` int(11) DEFAULT NULL,
`srid` int(11) DEFAULT NULL,
`ceid` int(11) DEFAULT NULL,
`amid` int(11) DEFAULT NULL,
`mptid` int(11) DEFAULT NULL,
`clearorder` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`clearreason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`cashoid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`shifttime_dt` datetime DEFAULT NULL,
`couponamount` int(11) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
`transfernum` char(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`rechargebalance` decimal(10,2) DEFAULT NULL,
`largessbalance` decimal(10,2) DEFAULT NULL,
`crid` int(11) DEFAULT NULL,
`ifinvoice` tinyint(1) DEFAULT '0',
PRIMARY KEY (`moid`),
KEY `crm_member_order_history_alp_member_FK` (`amid`),
KEY `crm_member_order_history_crm_custrad_entty_FK` (`ceid`),
KEY `crm_member_order_history_crm_sale_rule_FK` (`srid`),
KEY `crm_member_order_crm_member_account_FK` (`maid`),
KEY `crm_member_order_history_alp_shop_FK` (`sid`),
KEY `crm_member_order_history_alp_merchant_user_FK` (`muid`),
KEY `crm_member_order_history_crm_pay_type_FK` (`mptid`),
KEY `crm_member_order_history_alp_pos_FK` (`pid`),
KEY `serialnumber` (`serialnumber`),
KEY `type` (`type`),
KEY `crm_member_order_history_crm_reason_FK` (`crid`),
CONSTRAINT `crm_member_order_history_alp_member_FK` FOREIGN KEY (`amid`) REFERENCES `alp_member` (`amid`),
CONSTRAINT `crm_member_order_history_alp_merchant_user_FK` FOREIGN KEY (`muid`) REFERENCES `alp_merchant_user` (`muid`),
CONSTRAINT `crm_member_order_history_alp_pos_FK` FOREIGN KEY (`pid`) REFERENCES `alp_pos` (`pid`),
CONSTRAINT `crm_member_order_history_alp_shop_FK` FOREIGN KEY (`sid`) REFERENCES `alp_shop` (`sid`),
CONSTRAINT `crm_member_order_history_crm_custrad_entty_FK` FOREIGN KEY (`ceid`) REFERENCES `crm_custrad_entty` (`ceid`),
CONSTRAINT `crm_member_order_history_crm_member_account_FK` FOREIGN KEY (`maid`) REFERENCES `crm_member_account` (`maid`),
CONSTRAINT `crm_member_order_history_crm_pay_type_FK` FOREIGN KEY (`mptid`) REFERENCES `crm_pay_type` (`mptid`),
CONSTRAINT `crm_member_order_history_crm_reason_FK` FOREIGN KEY (`crid`) REFERENCES `crm_reason` (`crid`),
CONSTRAINT `crm_member_order_history_crm_sale_rule_FK` FOREIGN KEY (`srid`) REFERENCES `crm_sale_rule` (`srid`)
) ENGINE=InnoDB AUTO_INCREMENT=3143172 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
|
|
免費(fèi)注冊(cè)
發(fā)表于 2015-01-20 12:02