mysql,表设计

Question

五个人一起吃饭， 每次吃饭都是随机某个人掏钱，每次吃饭不一定五个人都来（可能某些人不来，这是重点），每30天算一次账，如何算出每个人应该掏多少钱！表如何设计？sql怎么写？

这是最近面试碰到的题！感觉挺有意思，自己对sql不是很了解，想了两天没有结果，求大神帮忙，谢谢！

Answer 1

闲着没事搞了一下，欢迎指教。。
用户表：

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;

Answer 2

问题的点在于每次饭的钱不一样，
这儿有一个解决办法，设计三张表
person表，包括字段 personId,name,age,des....，
record表，包括字段 recordId,time ,money,...
person_record_link表，包括p_r_Id,personId,recordId
然后每次吃饭，都向person_record_link中插入记录，每30天根据personId统计每个人应该付的钱

Answer 3

按照 E-R 原则来设计就行了呀，有两种实体（Entity）——人和饭，有一种多对多关系（Relation）——吃。

具体细化了么，就是楼上说的那样。如果有一个人没来，这个人就不会出现在这个关系里。

Answer 4

吃饭表：
id
uid 用户的id
eat_time 吃饭时间
money 用了多少钱？
person_total 吃饭的人数
paymoney 付了多少钱？（当次会钱的，值与money字段相同，没有会的，记作0)

用户表：
uid 用户的id
name 姓名
.....

计算的时候只需要：

select name,(sum(money/person_total)-sum(paymoney)) as shallpay from 吃饭表 left join 用户表
 where eat_time >月初 and eat_time<月末 group by uid;