Присоединяйтесь ко многим и объединяйте множество ссылок в одну строку
Я консультировался с подобными вопросами, но вопрос отличается, поскольку я присоединяюсь не только ко многим, но и к одному.
Пользователь
+----+-------+---------------+
| id | first | department_id |
+----+-------+---------------+
| 1 | John | 1 |
| 2 | Jane | 2 |
+----+-------+---------------+
отделение
+----+------------+
| id | name |
+----+------------+
| 1 | purchasing |
| 2 | HR |
| 3 | marketing |
+----+------------+
роль
+----+----------+
| id | name |
+----+----------+
| 1 | admin |
| 2 | accounts |
| 3 | sales |
+----+----------+
user_role
+--------+----------+
| user_id | role_id |
+--------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
+--------+----------+
SQL
SELECT user.first, GROUP_CONCAT(role.name), department.id AS 'department.id', department.name AS 'department.name'
FROM user
INNER JOIN department ON user.department_id
INNER JOIN user_role ON user.id = user_role.user_id
INNER JOIN role ON user_role.role_id = role.id
GROUP BY user.name;
Ожидание
John, "admin,accounts,sales", 1, purchasing
Jane, "accounts,sales", 2, HR
По сути, это объединение 4 таблиц: многие ко многим и 1 ко многим с функцией группового объединения.
запрос выдает ошибку.
Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'department.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
У меня ошибка, посмотрите мое редактирование, пожалуйста
Ответы 1
Поскольку отдел уникален для каждого пользователя, вы можете использовать max(), чтобы получить сведения об отделе, чтобы преодолеть ограничения, налагаемые group by.
SELECT
user.first,
GROUP_CONCAT(role.name) AS roles,
max(department.id) AS department_id,
max(department.name) AS department_name
FROM user
INNER JOIN department ON user.department_id = department.id
INNER JOIN user_role ON user.id = user_role.user_id
INNER JOIN role ON user_role.role_id = role.id
GROUP BY user.first;
Также, если имя пользователя не уникально, вам следует:
GROUP BY user.id, user.first
Также псевдонимы столбцов не должны заключаться в одинарные кавычки. Вместо этого используйте обратные кавычки.
Я вижу, понял. вы использовали max, потому что mysql ограничивает каждый столбец агрегатной функцией, верно?
Да, каждый столбец в списке выбора должен быть либо в предложении group by, либо в агрегированном виде.
хорошо, а это ограничение наложено из-за использования функции GROUP_CONCAT или из-за предложения GROUP BY?
Из-за группы. GROUP_CONCAT() — это агрегированный столбец. В качестве альтернативы max() вы можете использовать any_value().
Я только что проверил, не используя max или any_value, и это работает, не знаю. Есть идеи
MySql допускает это, когда для атрибута only_full_group_by установлено значение false, но ошибка, которую вы упоминаете в вопросе, означает, что для него установлено значение true.
Что делает MySql, так это выбирает значение для столбца (любое значение), но в вашем случае есть только 1 значение, поэтому результат правильный. В других rdbms, таких как sqlserver, это было бы запрещено.
Вы можете найти больше здесь: dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
я думаю, потому что у меня не было user.id в моем select и group by, поэтому это выдавало ошибку.
хорошо, я буду использовать any_value, чтобы быть в безопасности, надеюсь, any_value поддерживается и на сервере mysql, в противном случае я буду использовать max, потому что у меня будет вариант использования, чтобы это было кросс-платформенное решение db
Поскольку user.id не был включен ни в группу, ни в выборку, проблем нет. Но на всякий случай используйте user.id в группе, если имена пользователей не уникальны. Также ошибка может быть вызвана тем, что вы заключили псевдоним «department.id» в одинарные кавычки вместо обратных или квадратных скобок.
хорошо, исправлено, теперь используются обратные кавычки, однако вы имеете в виду квадратную скобку [department.id] ? мне выдало ошибку
Правильно, для mysql вы должны использовать обратные кавычки. Квадратные скобки работают на сервере sql и sqlite.
ну, я думаю, что мне нужно управлять двумя сценариями sql, из-за обратных кавычек и квадратных скобок, здесь одинарная кавычка работала в mysql, если она работает на сервере sql, то у меня есть кросс-платформенный сценарий, в противном случае :( Я очень ценю ваша помощь.
Если вы планируете перенести код на сервер sql, то вы должен используете max() (там нет any_value()).
конечно, я сейчас на месте
Что ваш запрос дает в качестве вывода?