CREATE VIEW `v_standingbook` AS select `t1`.`ID` AS `ID`,`t1`.`ID` AS `SB_ID`,`t1`.`RELATION_ID` AS `RELATION_ID`,`t1`.`CHANNEL` AS `CHANNEL`,`t1`.`SORT` AS `SORT`,`t1`.`MEMBER_ID` AS `MEMBER_ID`, (select `t2`.`STUDENT_CODE` AS `STUDENT_CODE` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_CODE`, (select `t2`.`STUDENT_NAME` AS `STUDENT_NAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_NAME`, (select `t2`.`USERNAME` AS `USERNAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_USERNAME`, (select `t3`.`CARD_CODE` AS `CARD_CODE` from `erp_card` `t3` where (`t1`.`ID` = `t3`.`STANDINGBOOK_ID`)) AS `CARD_CODE`,`t1`.`BUSI_TYPE` AS `BUSI_TYPE`,`t1`.`BUSI_AMOUNT` AS `BUSI_AMOUNT`, (select sum(`t4`.`PAYMENT_AMOUNT`) AS `SUM(T4.PAYMENT_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_AMOUNT`, (select sum(`t4`.`CHANGE_AMOUNT`) AS `SUM(T4.CHANGE_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `CHANGE_AMOUNT`, (select sum(`t4`.`FEE`) AS `SUM(T4.FEE)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_CALC`, (select sum(`t4`.`FEE_FINAL`) AS `SUM(T4.FEE_FINAL)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_FINAL`,`t1`.`DELIVERY_FEE` AS `DELIVERY_FEE`,`t1`.`PAYMENT_FEE` AS `PAYMENT_FEE`,`t1`.`COUPON_FEE` AS `COUPON_FEE`, (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1'))) AS `CLASS_AMOUNT_NORMAL`, (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2'))) AS `BOOK_AMOUNT`, (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'3'))) AS `ROOM_AMOUNT`, (select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'4'))) AS `AUDITION_AMOUNT`, (select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_ID`, (select group_concat(`t5`.`CLASS_CODE` separator '/') AS `GROUP_CONCAT(T5.CLASS_CODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_CODE`, (select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_NAME`, (select group_concat(`t5`.`TREECODE` separator '/') AS `GROUP_CONCAT(T5.TREECODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `TREECODE`, (select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_ID`, (select group_concat(`t6`.`BOOK_CODE` separator '/') AS `GROUP_CONCAT(T6.BOOK_CODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_book` `t6`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t6`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_CODE`, (select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_NAME`,`t1`.`STATUS` AS `STATUS`,`t1`.`STATUS` AS `SB_STATUS`,`t1`.`FINANCE_STATUS` AS `FINANCE_STATUS`,`t1`.`FINANCE_RECV_DATE` AS `FINANCE_RECV_DATE`,`t1`.`CREATE_PARTY_ID` AS `CREATE_PARTY_ID`,`t1`.`CREATE_PARTY_NAME` AS `CREATE_PARTY_NAME`,`t1`.`CREATE_PARTY_NAME` AS `SB_OPERATOR`,`t1`.`CREATE_DATE` AS `CREATE_DATE`,`t1`.`CREATE_DATE` AS `SB_DATE`,`t1`.`NORMAL_DATE` AS `NORMAL_DATE`,`t1`.`REMARK` AS `REMARK`,`t1`.`TEACHER_ID` AS `STEACHER_ID`,`t1`.`TEACHER_NAME` AS `STEACHER_NAME` from `erp_standingbook` `t1`; 不好意思,我以为自动能换。。
view是视图,没有索引,索引在视图引用的表上mysql-> SHOW INDEX FROM "你的db".erp_standingbook_detail mysql-> SHOW INDEX FROM "你的db".erp_bookmysql-> EXPLAIN SELECT * from v_standingbook 看执行计划是否有用到后台表的index
select `t1`.`ID` AS `ID`,`t1`.`ID` AS `SB_ID`,`t1`.`RELATION_ID` AS `RELATION_ID`,`t1`.`CHANNEL` AS `CHANNEL`,`t1`.`SORT` AS `SORT`,`t1`.`MEMBER_ID` AS `MEMBER_ID`,
(select `t2`.`STUDENT_CODE` AS `STUDENT_CODE` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_CODE`,
(select `t2`.`STUDENT_NAME` AS `STUDENT_NAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_NAME`,
(select `t2`.`USERNAME` AS `USERNAME` from `erp_student` `t2` where (`t1`.`MEMBER_ID` = `t2`.`ID`)) AS `MEMBER_USERNAME`,
(select `t3`.`CARD_CODE` AS `CARD_CODE` from `erp_card` `t3` where (`t1`.`ID` = `t3`.`STANDINGBOOK_ID`)) AS `CARD_CODE`,`t1`.`BUSI_TYPE` AS `BUSI_TYPE`,`t1`.`BUSI_AMOUNT` AS `BUSI_AMOUNT`,
(select sum(`t4`.`PAYMENT_AMOUNT`) AS `SUM(T4.PAYMENT_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_AMOUNT`,
(select sum(`t4`.`CHANGE_AMOUNT`) AS `SUM(T4.CHANGE_AMOUNT)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `CHANGE_AMOUNT`,
(select sum(`t4`.`FEE`) AS `SUM(T4.FEE)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_CALC`,
(select sum(`t4`.`FEE_FINAL`) AS `SUM(T4.FEE_FINAL)` from `erp_payment` `t4` where (`t1`.`ID` = `t4`.`STANDINGBOOK_ID`) group by `t1`.`ID`) AS `PAYMENT_FEE_FINAL`,`t1`.`DELIVERY_FEE` AS `DELIVERY_FEE`,`t1`.`PAYMENT_FEE` AS `PAYMENT_FEE`,`t1`.`COUPON_FEE` AS `COUPON_FEE`,
(select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1'))) AS `CLASS_AMOUNT_NORMAL`,
(select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2'))) AS `BOOK_AMOUNT`,
(select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'3'))) AS `ROOM_AMOUNT`,
(select sum(`t2`.`PRODUCT_AMOUNT_FINAL`) AS `SUM(T2.PRODUCT_AMOUNT_FINAL)` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'4'))) AS `AUDITION_AMOUNT`,
(select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_ID`,
(select group_concat(`t5`.`CLASS_CODE` separator '/') AS `GROUP_CONCAT(T5.CLASS_CODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_CODE`,
(select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `CLASS_NAME`,
(select group_concat(`t5`.`TREECODE` separator '/') AS `GROUP_CONCAT(T5.TREECODE SEPARATOR '/')` from (`erp_standingbook_detail` `t2` join `erp_class` `t5`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t5`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'1')) group by `t2`.`STANDINGBOOK_ID`) AS `TREECODE`,
(select group_concat(`t2`.`PRODUCT_ID` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_ID SEPARATOR '/')` from `erp_standingbook_detail` `t2` where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_ID`,
(select group_concat(`t6`.`BOOK_CODE` separator '/') AS `GROUP_CONCAT(T6.BOOK_CODE SEPARATOR '/')`
from (`erp_standingbook_detail` `t2` join `erp_book` `t6`) where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_ID` = `t6`.`ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_CODE`,
(select group_concat(`t2`.`PRODUCT_NAME` separator '/') AS `GROUP_CONCAT(T2.PRODUCT_NAME SEPARATOR '/')` from `erp_standingbook_detail` `t2`
where ((`t1`.`ID` = `t2`.`STANDINGBOOK_ID`) and (`t2`.`PRODUCT_TYPE` = _utf8'2')) group by `t2`.`STANDINGBOOK_ID`) AS `BOOK_NAME`,`t1`.`STATUS`
AS `STATUS`,`t1`.`STATUS` AS `SB_STATUS`,`t1`.`FINANCE_STATUS` AS `FINANCE_STATUS`,`t1`.`FINANCE_RECV_DATE` AS `FINANCE_RECV_DATE`,`t1`.`CREATE_PARTY_ID`
AS `CREATE_PARTY_ID`,`t1`.`CREATE_PARTY_NAME` AS `CREATE_PARTY_NAME`,`t1`.`CREATE_PARTY_NAME` AS `SB_OPERATOR`,`t1`.`CREATE_DATE`
AS `CREATE_DATE`,`t1`.`CREATE_DATE` AS `SB_DATE`,`t1`.`NORMAL_DATE` AS `NORMAL_DATE`,`t1`.`REMARK` AS `REMARK`,`t1`.`TEACHER_ID` AS `STEACHER_ID`,`t1`.`TEACHER_NAME` AS `STEACHER_NAME` from `erp_standingbook` `t1`; 不好意思,我以为自动能换。。
如果你建好了索引,视图会跟你的 sql 一样,走索引的。
相对来说,物化视图就给力的多。但是需要你维护。
mysql-> SHOW INDEX FROM "你的db".erp_bookmysql-> EXPLAIN SELECT * from v_standingbook 看执行计划是否有用到后台表的index