mysql,表设计

问题描述

问 题

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

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

解决方案

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

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;

这篇关于mysql,表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！