mysql建立视图两个子查询_Mysql多个子查询多个LEFTJOIN视图创建_MySQL

bitsCN.com

CREATE VIEW `v_noprovide` AS SELECT*FROMtb_sectionWHEREprovide = '0'CREATE VIEW `v_thismonth` AS SELECTts.userId AS id,CONCAT('thismonth:',SUM(ts.amount),'yuan') AS 'thismonth'FROMtb_section AS tsWHEREts.yearMonth = DATE_FORMAT(NOW(), '%Y%m')CREATE VIEW `v_lastmonth` AS SELECTts.userId AS id,CONCAT('lastmonth:',SUM(ts.amount),'yuan') AS 'lastmonth'FROMtb_section AS tsWHEREts.yearMonth = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y%m')CREATE VIEW `v_usermanage` ASSELECTu.id AS id,u.email AS email,u.`name` AS `name`,CONCAT(COALESCE (la.lastmonth,'lastmonth:0yuan'),COALESCE (th.thismonth,'thismonth:0yuan')) AS recent,Sum(COALESCE(b.amount,0)) AS totalBonus,Sum(COALESCE(n.amount,0)) AS unbilledFROMtb_user AS uLEFT JOIN tb_bonus AS b ON u.id = b.receiverIdLEFT JOIN v_noprovide AS n ON u.id = n.userIdLEFT JOIN v_thismonth AS th ON u.id = th.idLEFT JOIN v_lastmonth AS la ON u.id = la.idGROUP BY u.id

bitsCN.com

相关标签：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！