mysql crete view

CREATE VIEW user_algo_view AS
select `u`.`userId` AS `UserId`,`u`.`userCode` AS `UserCode`,group_concat(`al`.`id` separator ',') AS `AlgoIdList`,group_concat(`al`.`algoCode` separator ',') AS `AlgoCodeList` from ((`user` `u` left join `algouser` `alu` on((`u`.`userId` = `alu`.`userId`))) left join `algo` `al` on((`alu`.`algoId` = `al`.`id`))) group by `u`.`userId` ;

CREATE VIEW user_portfolio_view AS
SELECT u.userId AS UserId,u.userCode AS UserCode,group_concat(pf.ID separator ',') AS PortfolioIdList,group_concat(pf.portfolioName separator ',') AS PortfolioNameList from ((`user` `u` left join `portfoliouser` `pfu` on((`u`.`userId` = `pfu`.`userID`))) left join `portfolio` `pf` on((`pf`.`ID` = `pfu`.`portfolioID`))) group by `u`.`userId` ;

CREATE VIEW user_account_view AS SELECT u.userId AS UserId, u.userCode AS UserCode, group_concat(ac.id SEPARATOR ',') AS AccountIdList, group_concat( ac.accountCode SEPARATOR ',' ) AS AccountCodeList FROM ( ( user u LEFT JOIN accountuser acu ON ( ( u.userId = acu.userID ) ) ) LEFT JOIN account ac ON ( ( acu.accountID = ac.id ) ) ) GROUP BY u.userId ;

 

posted @ 2017-05-09 09:51  苍洱  阅读(196)  评论(0编辑  收藏  举报