mysql 设计表_mysql,表设计

拉莫斯之舞

闲着没事搞了一下，欢迎指教。。用户表：CREATETABLE`usr`(`uid`int(11)NOTNULL,`name`char(10)DEFAULTNULL,PRIMARYKEY(`uid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;吃饭记录表：CREATETABLE`meal`(`mid`int(11)NOTNULL,`money`int(11)DEFAULTNULLcomment'付款金额',`pay_uid`int(11)DEFAULTNULLcomment'付款人员id',`dt`dateDEFAULTNULL,PRIMARYKEY(`mid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;吃饭人员流水表：CREATETABLE`meal_jnl`(`id`int(11)NOTNULLAUTO_INCREMENT,`mid`int(11)DEFAULTNULL,`in_uid`int(11)DEFAULTNULLcomment'参加人员id',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=52DEFAULTCHARSET=utf8;造数据：INSERTINTO`usr`(`uid`,`name`)VALUES(1,'jim'),(2,'jeak'),(3,'lucy'),(4,'carl'),(5,'jerry'),(6,'mark');INSERTINTO`meal`(`mid`,`money`,`pay_uid`,`dt`)VALUES(1,122,2,'2017-06-01'),(2,56,4,'2017-06-01'),(3,56,1,'2017-06-02'),(4,76,3,'2017-06-03'),(5,54,5,'2017-06-04'),(6,66,2,'2017-06-05'),(7,77,2,'2017-06-05'),(8,34,3,'2017-06-06'),(9,54,1,'2017-06-07'),(10,77,4,'2017-06-08'),(11,45,5,'2017-06-08'),(12,87,2,'2017-06-10'),(13,123,3,'2017-06-11'),(14,431,1,'2017-06-11'),(15,23,4,'2017-06-12');INSERTINTO`meal_jnl`(`id`,`mid`,`in_uid`)VALUES(1,1,2),(2,1,3),(3,1,4),(4,1,5),(5,2,1),(6,2,2),(7,2,4),(8,2,5),(9,3,1),(10,3,2),(11,3,3),(12,3,4),(13,3,5),(14,4,3),(15,4,4),(16,4,5),(17,5,2),(18,5,5),(19,6,4),(20,6,5),(21,6,1),(22,6,2),(23,7,2),(24,7,5),(25,7,1),(26,8,2),(27,8,3),(28,8,4),(29,8,5),(30,9,1),(31,9,4),(32,10,1),(33,10,2),(34,10,3),(35,10,4),(36,10,5),(37,11,1),(38,11,2),(39,11,5),(40,12,2),(41,12,5),(42,13,3),(43,13,1),(44,14,1),(45,14,3),(46,14,4),(47,15,3),(48,15,4),(49,15,5),(50,15,6),(51,11,6);获取没人月底应付金额：selectin_uid,out_mon-pay_montm,namefrom(selectsum(casewhenmoneyisnullthen0elsemoneyend)pay_mon,uid,namefrommealrightjoinusronmeal.`pay_uid`=usr.uidwheredtbetween''and''groupbypay_uid)aaarightjoin(selectin_uid,sum(am)out_monfrom(selecta.mid,money/count(in_uid)amfrommeal_jnlajoinmealbona.mid=b.midwhereb.dtbetween''and''groupbymid)aajoinmeal_jnlbbonaa.mid=bb.midgroupbyin_uid)bbbonaaa.uid=bbb.in_uid;