Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Даны две таблицы. В первой таблице содержатся данные о клиентах и номера их заказов. Во второй — номера заказов, даты и сумма выполненных работ за каждую дату. Выведите, пожалуйста, с помощью SQL сумму выполненных работ за месяц по клиентам, у которых больше трёх заказов.
- */
- /* Создание таблиц и наполнение данными */
- CREATE TABLE orders (order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL);
- INSERT INTO orders (client_id) VALUES (1), (1), (1), (2), (2), (3), (2), (1), (1), (2), (4), (3);
- CREATE TABLE works (order_id INT NOT NULL, dates date, worksum INT NOT NULL);
- INSERT INTO works (order_id, dates, worksum) VALUES (1, '2008-10-10', 100), (2, '2008-10-14', 222), (3, '2008-10-20', 333), (4, '2008-10-23', 444), (5, '2008-10-27', 234), (6, '2008-11-12', 4324), (7, '2008-11-17', 645), (8, '2008-11-25', 6456), (9, '2008-12-13', 423), (10, '2008-12-16', 43), (11, '2008-12-18', 54), (12, '2008-12-23', 555);
- /* Временная таблица, где будет содержаться ответ */
- CREATE TEMPORARY TABLE answer (client_id INT NOT NULL, month varchar(7), total INT);
- /* Только клиенты и их заказы у которых больше трёх заказов */
- CREATE VIEW order_client_3 AS SELECT order_id, client_id FROM orders WHERE client_id IN (SELECT client_id FROM orders GROUP BY client_id HAVING COUNT(order_id) > 3);
- /* Основная таблица, где есть нужные клиенты со своими заказами, даты этих заказов и суммы */
- CREATE VIEW main_t AS SELECT * FROM order_client_3 LEFT JOIN works USING (order_id);
- /* Процедура наполняет нашу временную таблицу ответом
- Для этого открываем курсор на перечень всех нужных нам клиентов (больше трёх заказов сделали в нашем магазине) и по каждому клиенту в цикле делаем выборку суммы денег, которую он уплатил в нашем магазине с группировкой по месяцам*/
- DROP PROCEDURE IF EXISTS getanswer;
- delimiter //
- CREATE PROCEDURE getanswer()
- BEGIN
- DECLARE c_ID INT;
- DECLARE done INT DEFAULT FALSE;
- DECLARE clients_list CURSOR FOR SELECT DISTINCT client_id from main_t;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
- OPEN clients_list;
- read_loop: LOOP
- FETCH clients_list INTO c_ID;
- IF done THEN
- LEAVE read_loop;
- END IF;
- INSERT INTO answer (client_id, month, total) SELECT client_id, date_format(dates, "%Y-%m"), SUM(worksum) FROM main_t WHERE client_id=c_ID GROUP BY date_format(dates, "%Y-%m");
- END LOOP;
- CLOSE clients_list;
- END;
- //
- delimiter ;
- CALL getanswer();
- /* Выводим ответ */
- SELECT * FROM answer;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement