SQLize | PHPize | SQLtest

drop table if EXISTS LOANS_TABLE; CREATE TABLE if not exists LOANS_TABLE ( LOAN_ID int, CLIENT_ID int, LOAN_DATE date, LOAN_AMOUNT float); drop table if EXISTS CLIENTS_TABLE; CREATE TABLE if not exists CLIENTS_TABLE ( CLIENT_ID int, CLIENT_NAME VARCHAR(20), BIRTHDAY date, GENDER VARCHAR(20)); INSERT INTO CLIENTS_TABLE VALUES (1, 'bob', '20200115', 'male'), (2, 'rocky', '20200215', 'female'), (3, 'like', '20200215', 'female'), (4, 'ricky', '20200215', 'male'); INSERT INTO LOANS_TABLE VALUES (1, 1, '20200115', 10000), (2, 2, '20200215', 20000), (3, 3, '20200315', 30000), (4, 4, '20200415', 40000), (5, 1, '20200116', 15000), (6, 2, '20200315', 35000), (7, 3, '20200315', 5000), (8, 1, '20200115', 1500), (9, 2, '20200115', 500), (10, 1, '20200115', 1500), (11, 1, '20200115', 1500), (12, 1, '20200115', 1500), (13, 1, '20200115', 1500), (14, 1, '20200115', 1500); SET @query = NULL; -- устанавливаем лимит group_concat побольше, 1024 по умолчанию не хватает и запрос обрывается на полуслове SET SESSION group_concat_max_len = 1000000; -- Подсчитываем порядковый номер договора за год WITH agg_table1 AS( SELECT YEAR(loan_date) as year_, RANK() OVER(PARTITION BY client_id ORDER BY loan_id) AS loan_rank FROM loans_table ) -- Используем DISTINCT внутри GROUP_CONCAT для получения уникальных сочетаний SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM( CASE WHEN loan_rank = "', loan_rank, '" AND year_ = "', year_,'" THEN 1 ELSE 0 END) AS "Количество ', loan_rank, ' договоров, оформленных в ', year_, '"') ORDER by year_, loan_rank ASC) FROM agg_table1 into @query; -- Собираем запрос целиком: год year_, информация о поле gender, количество клиентов c -- n-ым договором за год year_, группируем по полу, сортируем по полу в обратном порядке SET @query = CONCAT( 'SELECT gender, ', @query, 'FROM ( SELECT YEAR(LT.loan_date) as year_, CT.gender AS gender, RANK() OVER(PARTITION BY LT.client_id ORDER BY LT.loan_id) AS loan_rank FROM CLIENTS_TABLE CT JOIN LOANS_TABLE LT ON CT.client_id = LT.client_id) agg_table2 GROUP BY gender ORDER BY gender DESC' ); SELECT @query; PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;