У меня есть этот запрос:
SELECT
userlist.USERID,
(case when (sum( CASE WHEN track.OFFER_ID = 221 THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211
FROM
userlist
INNER JOIN track ON userlist.USERID = track.USERID
group by
userid
Это вывод:
+------------+----------
| USERID | offer_211 |
+------------+----------
| 1657487706 | 0 |
| 1238439394 | 0 |
| 1238427171 | 1 |
| 1248431441 | 0 |
| 1248464345 | 1 |
Цель этого запроса:
Существует таблица пользователей, таблица предложений и таблицы треков. Таблица треков содержит все клики из таблицы пользователей. Я хочу создать представление, подобное выше, которое покажет мне все клики пользователей для всех предложений. Если пользователь нажал на определенное предложение, он отобразит "1" под этим предложением, если не отобразит "0". Все записи поступают из таблицы дорожек.
Вот что мне нужно для помощи: Я хочу иметь возможность динамически создавать столбцы "offer_211" из таблицы "предложение" вместо указания их вручную (см. Мой запрос)
Итак, для всех предложений, которые пользователь нажал или не щелкнул (столбцы), можно было бы предложить таблицу.
он должен выглядеть следующим образом: "offer _" + offer.ID
Он должен выглядеть следующим образом: (предполагая, что в таблице предложений есть только 2 предложения с идентификаторами 211, 212)
В таблице предложений может быть 100 предложений, поэтому для этого представления должно быть такое же количество динамических столбцов.
| USERID | offer_211 | offer_212 |
+------------+----------+-------------
| 1657487706 | 0 | 1 |
| 1238439394 | 0 | 0 |
| 1238427171 | 1 | 0 |
| 1248431441 | 0 | 1 |
| 1248464345 | 1 | 0 |
Таблица дорожек:
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(22) | NO | PRI | NULL | auto_increment |
| OFFER_ID | int(22) | YES | MUL | NULL | |
| USERID | int(22) | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
userlist Таблица:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| USERID | int(22) | NO | PRI | 0 | |
| EMAIL | varchar(200) | YES | | NULL | |
| FIRSTNAME | varchar(100) | YES | | NULL | |
| LASTNAME | varchar(100) | YES | | NULL | |
Таблица предложений:
+------------------+--------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+-------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(100) | YES | | NULL | |
| DESCRIPTION | text | YES | | NULL | |
| URL | text | YES | | NULL | |
Такой запрос может стать очень грязным, очень быстрым. Я бы предложил использовать более стандартные запросы для получения ваших данных, а затем обработать эти данные в вашем приложении.
Однако, если есть действительно веская причина для создания динамического запроса, здесь один из способов сделать это - это всего лишь строительные блоки, поэтому вам нужно будет изменить его в соответствии с вашими требованиями:
Используйте CONCAT и GROUP_CONCAT, чтобы создайте инструкцию SELECT для использования в подготовленный отчет:
SELECT CONCAT(
'SELECT',
GROUP_CONCAT(
' SUM(IF(offer_id = ', offer_id,
', 1, 0)) AS offer_', offer_id),
' FROM track;')
INTO @sql
FROM (SELECT DISTINCT offer_id FROM track) AS track;
Здесь созданная команда:
SELECT @sql;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT SUM(IF(offer_id = 1, 1, 0)) AS offer_1, SUM(IF(offer_id = 2, 1, 0)) AS offer_2, SUM(IF(offer_id = 3, 1, 0)) AS offer_3, SUM(IF(offer_id = 4, 1, 0)) AS offer_4 FROM track; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Создайте и выполните подготовленный оператор из динамического SQL:
PREPARE stmt FROM @sql;
EXECUTE stmt;
+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
| 3 | 2 | 1 | 1 |
+---------+---------+---------+---------+
DEALLOCATE PREPARE stmt;
Майк,
Вот что он возвращает: см. в конце, как его отрезать - странно. Что это может быть?
Я также не понял последнюю часть вашего комментария - последний блок;
Create and execute a prepared statement from the dynamic SQL:
PREPARE stmt FROM @sql;
EXECUTE stmt;
+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
| 3 | 2 | 1 | 1 |
+---------+---------+---------+---------+
DEALLOCATE PREPARE stmt;
Вот новый запрос:
SELECT CONCAT(
'SELECT',
GROUP_CONCAT(
' SUM(IF(OFFER_ID = ', offer_id,
', 1, 0)) AS offer_', offer_id),
' FROM track;')
INTO @sql
FROM (SELECT DISTINCT ID as OFFER_ID from offer where `STATUS`=1) AS track;
select @sql;
Вот результат:
SELECT SUM (IF (OFFER_ID = 178, 1, 0)) AS offer_178, SUM (IF (OFFER_ID = 234, 1, 0)) AS offer_234, SUM (IF (OFFER_ID = 206, 1, 0)) AS offer_206, SUM (IF (OFFER_ID = 213, 1, 0)) AS offer_213, SUM (IF (OFFER_ID = 229, 1, 0)) AS offer_229, SUM (IF (OFFER_ID = 220, 1, 0)) AS offer_220, SUM (IF (OFFER_ID = 221, 1, 0)) AS offer_221, SUM (IF (OFFER_ID = 222, 1, 0)) AS offer_222, SUM (IF (OFFER_ID = 225, 1, 0)) AS offer_225, SUM ( IF (OFFER_ID = 226, 1, 0)) AS offer_226, SUM (IF (OFFER_ID = 257, 1, 0)) AS offer_257, SUM (IF (OFFER_ID = 259, 1, 0)) AS offer_259, SUM (IF ( OFFER_ID = 258, 1, 0)) AS offer_258, SUM (IF (OFFER_ID = 260, 1, 0)) AS offer_260, SUM (IF (OFFER_ID = 228, 1, 0)) AS offer_228, SUM (IF (OFFER_ID = 230, 1, 0)) AS offer_230, SUM (IF (OFFER_ID = 232, 1, 0)) AS offer_232, SUM (IF (OFFER_ID = 233, 1, 0)) AS offer_233, SUM (IF (OFFER_ID = 239, 1, 0)) AS offer_239, SUM (IF (OFFER_ID = 240, 1, 0)) AS offer_240, SUM (IF (OFFER_ID = 241, 1, 0)) AS offer_241, SUM (IF (OFFER_ID = 242, 1, 0)) AS offer_242, SUM (IF (OFFER_ID = 243, 1, 0)) AS offer_243, SUM (IF (OFFE FROM track;
Рассматривали ли вы получение списка идентификаторов пользователей и идентификаторов предложений, отсортированных сначала по идентификатору пользователя, а затем по идентификатору предложения? Это та же самая информация, только что организованная несколько иначе, плюс было бы намного, намного быстрее для базы данных выполнить запрос типа "select user_id, offer_id из трека".