У меня есть таблица ввода:
+-----+------------+---------------------+---------+-------------+
| id | client_id | subscription_date | program |how_long_subs|
+-----+------------+---------------------+---------+-------------+
| 1 | 1 | 2016-05-20 00:00:00 | W90 | 12 |
| 2 | 4 | 2017-01-01 00:00:00 | W90 | 6 |
| 314 | 7 | 2017-03-21 00:00:00 | AB | 12 |
| 3 | 8 | 2016-12-19 00:00:00 | W90 | 12 |
| 4 | 9 | 2018-06-08 00:00:00 | W90 | 12 |
| 5 | 10 | 2017-06-12 00:00:00 | W90 | 12 |
| 313 | 10 | 2017-01-18 00:00:00 | AB | 12 |
| 377 | 11 | 2018-12-22 00:00:00 | AB | 12 |
| 308 | 11 | 2017-12-22 00:00:00 | AB | 12 |
| 6 | 11 | 2017-12-22 00:00:00 | W90 | 12 |
| 7 | 13 | 2017-01-05 00:00:00 | W90 | 6 |
| 8 | 21 | 2017-03-10 00:00:00 | W90 | 12 |
| 325 | 22 | 2017-03-02 00:00:00 | AB | 1 |
+-----+------------+---------------------+---------+-------------+
id — уникальный автоинкрементный столбец для этой таблицы. client_id — для связи с таблицей клиентов. Я ищу результат в форме:
+------------+----------+-----------------------------------------+--------+
| client_id | programs | dates |how_long|
+------------+----------+-----------------------------------------+--------+
| 1 | W90 | 2016-05-20 00:00:00 | 12 |
| ... | | | |
| 11 | W90|AB | 2017-12-22 00:00:00|2018-12-22 00:00:00 | 12|12 |
+------------+----------+-----------------------------------------+--------+
поэтому каждый client_id имеет 3 столбца: программы, даты, how_long
пока что у меня ниже для колонок програмы и na_ile
SELECT
client_id,
GROUP_CONCAT(DISTINCT program SEPARATOR '|') AS programs
FROM subscriptions GROUP BY client_id
но я понятия не имею, как это сделать для свиданий, поэтому все в порядке.
В качестве примера я выбрал клиента, который имеет несколько строк для разных программ, чтобы лучше проиллюстрировать это, поскольку вы можете видеть, что в результате даты являются МАКСИМАЛЬНОЙ датой для определенной программы.
Чтобы лучше понять, что я пытаюсь сделать, это обработать мою таблицу ввода, чтобы можно было рассчитать оставшуюся подписку для разных программ.
Формула это:
если client_id не имеет записей в подписках, используйте дату из таблицы клиентов
и вернуть разницу между этой датой +1 год и сегодня.
если client_id имеет записи в подписках, то
для каждой уникальной программы клиент имеет в таблице sub:
получить максимальную дату подписки и соответствующие инструкции how_long_subs для этой максимальной даты / программы. добавьте how_long_subs месяцы к дате подписки и верните разницу с сегодняшнего дня в днях.
Мой план состоит в том, чтобы получить таблицу результатов, как описано выше, и использовать php для обработки строк, чтобы получить что-то вроде этого:
example result for client_id: 11
W90: 2017-12-22 00:00:00 +12 months - now()
AB: 2018-12-22 00:00:00 +12 months - now()
Можете ли вы помочь мне с запросом и советом mySQL, если подход, который я использую, в порядке, или, может быть, есть более простой способ сделать это.
Спасибо
SELECT A.client_id,
GROUP_CONCAT(DISTINCT A.program SEPARATOR '|') programs,
GROUP_CONCAT(DISTINCT A.subscription_date SEPARATOR '|') dates,
GROUP_CONCAT(A.how_long_subs SEPARATOR '|') how_long
FROM
(SELECT client_id, subscription_date, MAX(program) program, how_long_subs
FROM subscriptions
GROUP BY client_id, subscription_date,how_long_subs) A
GROUP BY A.client_id;
Увидеть ДЕМО на SQL Fiddle.
Вы можете использовать подзапрос для получения максимальной даты
SELECT client_id, GROUP_CONCAT( program SEPARATOR '|') programs,
GROUP_CONCAT(max_date SEPARATOR '|') max_date
FROM (
SELECT
client_id,
programs,
max(subscription_date) max_date
FROM subscriptions
GROUP BY client_id, program )