ZgCWcvez

1Есть четыре таблицы: пользователей (users), периодов (periods), начислений (charges) и платежей (payments).
2
3  1. Начисление и платеж производятся пользователем за отдельный период.
4  2. За один и тот же период может быть несколько начислений и платежей.
5  3. Но также за период их может и не быть.
6
7Задача. За один SELECT запрос получить сводную таблицу, строки которой содержат:
8
9  1. ID пользователя (user_id);
10  2. ID периода (period_id);
11  3. Cуммарное начисление за этот период для указанного пользователя (charge);
12  4. Cуммарнный платеж за тот же период (payment).
13  5. Размер задолженности на момент этого периода (debt). Если пользователь переплатил, то число может быть отрицательным. Внимание! Задолженность за предыдущие периоды должна суммироваться.
14
15Таблицы:
16
17users
18+-------+--------------+------+-----+---------+----------------+
19| Field | Type         | Null | Key | Default | Extra          |
20+-------+--------------+------+-----+---------+----------------+
21| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
22+-------+--------------+------+-----+---------+----------------+
23
24periods
25+------------+---------+------+-----+---------+----------------+
26| Field      | Type    | Null | Key | Default | Extra          |
27+------------+---------+------+-----+---------+----------------+
28| id         | int(11) | NO   | PRI | NULL    | auto_increment |
29+------------+---------+------+-----+---------+----------------+
30
31charges
32+-----------+---------+------+-----+---------+----------------+
33| Field     | Type    | Null | Key | Default | Extra          |
34+-----------+---------+------+-----+---------+----------------+
35| id        | int(11) | NO   | PRI | NULL    | auto_increment |
36| value     | double  | YES  |     | NULL    |                |
37| period_id | int(11) | YES  |     | NULL    |                |
38| user_id   | int(11) | YES  |     | NULL    |                |
39+-----------+---------+------+-----+---------+----------------+
40
41payments
42+-----------+---------+------+-----+---------+----------------+
43| Field     | Type    | Null | Key | Default | Extra          |
44+-----------+---------+------+-----+---------+----------------+
45| id        | int(11) | NO   | PRI | NULL    | auto_increment |
46| value     | double  | YES  |     | NULL    |                |
47| period_id | int(11) | YES  |     | NULL    |                |
48| user_id   | int(11) | YES  |     | NULL    |                |
49+-----------+---------+------+-----+---------+----------------+
50
51SQL-запрос, создающий указанные таблицы в БД и наполняющих их демо-данными:
52---------------------------------------------------------------------------
53
54SET NAMES utf8;
55SET time_zone = '+00:00';
56SET foreign_key_checks = 0;
57SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
58
59DROP TABLE IF EXISTS `charges`;
60CREATE TABLE `charges` (
61  `id` int(11) NOT NULL AUTO_INCREMENT,
62  `value` double DEFAULT NULL,
63  `period_id` int(11) DEFAULT NULL,
64  `user_id` int(11) DEFAULT NULL,
65  PRIMARY KEY (`id`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68TRUNCATE `charges`;
69INSERT INTO `charges` (`id`, `value`, `period_id`, `user_id`) VALUES
70(1,	1000,	1,	1),
71(2,	1000,	2,	1),
72(3,	1000,	3,	1),
73(4,	1000,	4,	1),
74(5,	500,	5,	1),
75(6,	500,	5,	1);
76
77DROP TABLE IF EXISTS `payments`;
78CREATE TABLE `payments` (
79  `id` int(11) NOT NULL AUTO_INCREMENT,
80  `value` double DEFAULT NULL,
81  `period_id` int(11) DEFAULT NULL,
82  `user_id` int(11) DEFAULT NULL,
83  PRIMARY KEY (`id`)
84) ENGINE=InnoDB DEFAULT CHARSET=utf8;
85
86TRUNCATE `payments`;
87INSERT INTO `payments` (`id`, `value`, `period_id`, `user_id`) VALUES
88(1,	500,	1,	1),
89(2,	300,	2,	1),
90(3,	100,	4,	1);
91
92DROP TABLE IF EXISTS `periods`;
93CREATE TABLE `periods` (
94  `id` int(11) NOT NULL AUTO_INCREMENT,
95  PRIMARY KEY (`id`)
96) ENGINE=InnoDB DEFAULT CHARSET=utf8;
97
98TRUNCATE `periods`;
99INSERT INTO `periods` (`id`) VALUES
100(1),
101(2),
102(3),
103(4),
104(5);
105
106DROP TABLE IF EXISTS `users`;
107CREATE TABLE `users` (
108  `id` int(11) NOT NULL AUTO_INCREMENT,
109  PRIMARY KEY (`id`)
110) ENGINE=InnoDB DEFAULT CHARSET=utf8;
111
112TRUNCATE `users`;
113INSERT INTO `users` (`id`) VALUES
114(1),
115(2),
116(3);