#sql #sql-server
#sql #sql-сервер
Вопрос:
Ниже приведена моя структура таблицы.
- AttributeMaster — эта таблица является главной таблицей атрибутов, которая будет доступна для каждого запроса.
| AttrMasterId | attrName |
|---|---|
| 1 | Статьи расходов |
| 2 | Бизнес-причина |
- AttributeValue — когда пользователь заполняет данные из таблицы, если столбец пуст, мы не сохраняем его значение в базе данных.
Для каждого запроса существует несколько строк (TaskId). Каждая задача должна иметь атрибуты из мастера атрибутов. Теперь, если у пользователя нет атрибута, мы не сохраняем его в базе данных.
| AttrValId | RequestID | Идентификатор задачи | AttrMasterId | attrValue | RecordStatus |
|---|---|---|---|---|---|
| 1 | 200 | 1 | 1 | Мебель | A |
| 2 | 200 | 2 | 1 | Infra | A |
| 3 | 200 | 2 | 2 | Перемещение | A |
В приведенном выше сценарии для запроса 200 для идентификатора задачи — 1 у меня есть значение только для одного атрибута. Для идентификатора задачи — 2 у меня заполнены оба атрибута.
Результат запроса должен дать мне 4 строки, по 2 для каждого идентификатора задачи, с нулевыми заполнителями в столбце attrValue.
select * from AttributeMaster cam
left join AttributeValue cav on cam.AttrMasterId = cav.AttrMasterId
and cav.requestId = 36498 and cav.recordStatus = 'A'
right outer join (select distinct AttrMasterId from attrValue cav1 where cav1.requestId = 36498 ) ctI on cti.AttrMasterId = cav.AttrMasterId;
До сих пор я пробовал разные объединения, пытался самостоятельно объединить таблицу значений атрибутов, как указано выше, по-прежнему никаких результатов для заполнения пустых строк.
Любая помощь или указания будут оценены. Спасибо.
Редактировать 1: ожидаемый результат выглядит следующим образом:
| RequestID | Идентификатор задачи | AttrMasterId | attrValue | RecordStatus |
|---|---|---|---|---|
| 200 | 1 | 1 | Мебель | A |
| 200 | 1 | 2 | NULL | NULL |
| 200 | 2 | 1 | Infra | A |
| 200 | 2 | 2 | Перемещение | A |
Комментарии:
1. Добавление ожидаемого результата помогло бы!
2. @Arun добавил ожидаемый результат
Ответ №1:
Рабочая скрипка для SQL Server
Поскольку действительно должна быть таблица задач, я добавил ее в качестве термина CTE в первом решении. Вторая форма просто использует ваши существующие таблицы напрямую, с тем же результатом.
WITH Task (TaskId) AS (
SELECT DISTINCT TaskId FROM AttributeValue
)
, pairs (TaskId, AttrMasterId) AS (
SELECT Task.TaskId, AttributeMaster.AttrMasterId
FROM AttributeMaster CROSS JOIN Task
)
SELECT pairs.*
, AttributeMaster.*
, cav.*
FROM pairs
JOIN AttributeMaster
ON pairs.AttrMasterId = AttributeMaster.AttrMasterId
LEFT JOIN AttributeValue AS cav
ON pairs.AttrMasterId = cav.AttrMasterId AND pairs.TaskId = cav.TaskId
AND cav.requestId = 200 AND cav.recordStatus = 'A'
ORDER BY pairs.TaskId, pairs.AttrMasterId
;
-------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ --------------
| TaskId | AttrMasterId | AttrMasterId | AttrName | AttrValId | RequestId | TaskId | AttrMasterId | AttrValue | RecordStatus |
-------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ --------------
| 1 | 1 | 1 | Expense Items | 1 | 200 | 1 | 1 | Furniture | A |
| 1 | 2 | 2 | Business Reason | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 1 | 1 | Expense Items | 2 | 200 | 2 | 1 | Infra | A |
| 2 | 2 | 2 | Business Reason | 3 | 200 | 2 | 2 | Relocation | A |
-------- -------------- -------------- ----------------- ----------- ----------- -------- -------------- ------------ --------------
Вторая форма без добавленного Task термина CTE…
WITH pairs AS (
SELECT DISTINCT AttributeValue.TaskId, AttributeMaster.AttrMasterId
FROM AttributeMaster CROSS JOIN AttributeValue
)
SELECT pairs.*
, AttributeMaster.*
, cav.*
FROM pairs
JOIN AttributeMaster
ON pairs.AttrMasterId = AttributeMaster.AttrMasterId
LEFT JOIN AttributeValue AS cav
ON pairs.AttrMasterId = cav.AttrMasterId AND pairs.TaskId = cav.TaskId
AND cav.requestId = 200 AND cav.recordStatus = 'A'
ORDER BY pairs.TaskId, pairs.AttrMasterId
;
Комментарии:
1. Спасибо, приятель. Это работает. Я был настолько сосредоточен на самостоятельном соединении, что забыл об аспекте перекрестного соединения. работает как шарм.
2. Вы могли бы сделать это с одним соединением меньше, если хотите, см. Мой ответ
3. @GuidoG Это правильно. Я включил дополнительное соединение только для отображения attrName в результате, что не было обязательным требованием, и я не хотел включать имя в исходное перекрестное соединение. Нам нужны были только пары (TaskId, AttrMasterId) перед внешним соединением.
Ответ №2:
Вот еще одно решение, которое не требует CTE.
В нем также используется ответ TaskID like @jon-armstrong
declare @AttributeMaster table (MasterID int, Name varchar(50))
declare @AttributeValues table (ValueID int, RequestID int, TaskID int, MasterID int, Value varchar(50), Status varchar(1))
insert into @AttributeMaster (MasterID, Name)
values (1, 'Expense'), (2, 'Business')
insert into @AttributeValues (ValueID, RequestID, TaskID, MasterID, Value, Status)
values (1, 200, 1, 1, 'Furniture', 'A'),
(2, 200, 2, 1, 'Infra', 'A'),
(3, 200, 2, 2, 'Relocation', 'A')
select t.RequestID, t.TaskID, t.MasterID, v.Value, v.Status, t.Name
from ( select distinct m.MasterID, v.TaskID, v.RequestID, m.Name
from @AttributeMaster m cross join @AttributeValues v
) t
left join @AttributeValues v on v.MasterID = t.MasterID and v.TaskID = t.TaskID
and v.RequestID = 200 and v.Status = 'A'
order by t.TaskID, t.MasterID
результат
RequestID TaskID MasterID Value Status Name
200 1 1 Furniture A Expense
200 1 2 NULL NULL Business
200 2 1 Infra A Expense
200 2 2 Relocation A Business