mysql设计表月份_mysql,表设计

闲着没事搞了一下，欢迎指教。。

用户表：

CREATE TABLE `usr` (

`uid` int(11) NOT NULL,

`name` char(10) DEFAULT NULL,

PRIMARY KEY (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

吃饭记录表：

CREATE TABLE `meal` (

`mid` int(11) NOT NULL,

`money` int(11) DEFAULT NULL comment '付款金额',

`pay_uid` int(11) DEFAULT NULL comment '付款人员id',

`dt` date DEFAULT NULL,

PRIMARY KEY (`mid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

吃饭人员流水表：

CREATE TABLE `meal_jnl` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`mid` int(11) DEFAULT NULL,

`in_uid` int(11) DEFAULT NULL comment '参加人员id',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

造数据：

INSERT INTO `usr` (`uid`, `name`)

VALUES

(1, 'jim'),

(2, 'jeak'),

(3, 'lucy'),

(4, 'carl'),

(5, 'jerry'),

(6, 'mark');

INSERT INTO `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');

INSERT INTO `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);

获取没人月底应付金额：

select in_uid,out_mon-pay_mon tm,name from (

select sum(case when money is null then 0 else money end) pay_mon,uid,name

from meal right join usr

on meal.`pay_uid`=usr.uid

where dt between '' and ''

group by pay_uid) aaa

right join (

select in_uid,sum(am) out_mon

from (

select a.mid,money/count(in_uid) am

from meal_jnl a join meal b

on a.mid=b.mid

where b.dt between '' and ''

group by mid) aa

join meal_jnl bb

on aa.mid=bb.mid group by in_uid) bbb

on aaa.uid=bbb.in_uid;