#mysql #sql
#mysql #sql
Вопрос:
Я прошел через несколько заданий и часто вижу базы данных, подобные этой:
Column_Values
------ -------------- --------------
| id | field_id | label |
------------------------------------
| 1 | first_name | First Name -----------------
| 2 | last_name | Last Name -----------------------
| | | | | |
| | | | | |
| | | | | |
------ -------------- -------------- | |
| |
User Row | |
------- | |
| id | | |
------- | |
----------- 9000 | | |
| | 10000 | | |
| | | | |
| | | | |
| | | | |
| ------- | |
| | |
| Mapping Table | |
| ------------ ------------- --------- | |
| | User_Id | Field ID | Value | | |
| ------------------------------------ | |
-----------> 9000 | 1 | John <----------------- |
| 9000 | 2 | Doe <-----------------------
------------ ------------- ---------
Вместо фактического наличия столбца, в котором есть first_name и a last_name , имя столбца фактически хранится в другой таблице.
Как мне выбрать пользователя, чтобы я получил что-то вроде?
--------- ------------- -----------
| User_ID | First Name | Last Name |
-----------------------------------
| 9000 | John | Doe |
| | | |
--------- ------------- -----------
Также есть ли название для этого типа базы данных? Кажется, логика, стоящая за этим, заключается в том, чтобы иметь динамические столбцы, однако я думаю, что проблему можно смягчить с помощью запланированного простоя для обновления таблиц.
Комментарии:
1. Я несколько раз встречал похожий дизайн БД. Одной из причин этого был какой-то супер-универсальный фреймворк форм, который помог нетехническому персоналу создавать свои собственные формы в программном обеспечении, но ценой за это было то, что у вас были все значения из всех форм, сохраненные в виде пары ключ-значение в одной таблице, потому что в фоновом режиме все работало на одном и том же фрагменте кода общей формы, где на самом деле не имеет значения, связана ли форма с пользователем, заказом или чем-то еще. Мой опыт
2. @Emil Спасибо за понимание. Хотелось бы, чтобы существовал лучший дизайн БД, подходящий для этого. MongoDB (без SQL) или другой тип БД, который предоставляет онлайн-модификации БД, звучит как лучшее решение.
3. Это называется моделью значений атрибута сущности, или EAV
4. @user1157751 . . . Цель? Чтобы сохранить консультантов на работе.
Ответ №1:
Условная агрегация должна сработать:
SELECT
u.id,
MAX(CASE WHEN cv.field_id = 'first_name' THEN m.value END) first_name,
MAX(CASE WHEN cv.field_id = 'last_name' THEN m.value END) last_name
FROM user u
INNER JOIN mapping m ON m.user_id = u.id
INNER JOIN Column_Values cv ON cv.id = m.field_id
GROUP BY u.id
ПРИМЕЧАНИЕ: если все, что вам нужно, это идентификатор пользователя и его имя и фамилия, то вам не нужно вводить user таблицу. Просто:
SELECT
m.user_id,
MAX(CASE WHEN cv.field_id = 'first_name' THEN m.value END) first_name,
MAX(CASE WHEN cv.field_id = 'last_name' THEN m.value END) last_name
FROM mapping m ON
INNER JOIN Column_Values cv ON cv.id = m.field_id
GROUP BY m.user_id
Комментарии:
1. Существуют сотни field_id, есть ли какой-то способ динамически создавать регистр выбора?
2. @user1157751: SQL-запрос должен возвращать фиксированный набор столбцов… Для этого вам нужно будет использовать динамический SQL … Или вы можете использовать инструмент по вашему выбору для генерации серии
CASEвыражений.3. Звучит как чрезвычайно сложный в обслуживании SQL-код. Я думаю, что я просто сгенерирую серию выражений case.
4. @GMB Можете ли вы привести пример того, как сделать это динамичным?
Ответ №2:
Попробуйте это
select
User_ID,
max(FirstName) FirstName,
max(LastName) LastName
from
(
select
User_ID,
case when c.Field_Id= 'first_name' then Value end as FirstName,
case when c.Field_Id = 'last_name' then Value end as LastName
from Column_Values c
inner join MappingTable m
on c.Id = m.FieldId
) t
group by
User_ID