Показать 3 разных значения одного столбца и одной таблицы в одной строке результатов
Я использую SQL Server 2014 для своего проекта. У меня есть три таблицы:
Table A:
Column: id
Table B:
Column: id, id_of_Table_A
Table C:
Column: id, id_of_Table_B, category
The relationship between A and B is one to many
The relationship between B and C is one to many
В таблице C есть 10 значений категорий для столбца category, но меня интересуют только три категории (cat1, cat2, cat3). Я надеюсь запросить и получить следующие результаты для трех категорий:
id_of_Table_A, category, category, category
У меня есть следующее утверждение:
select a.id, c.category from Table_C c
join Table_B b on b.id = c.id_of_Table_B
join Table_A a on a.id = b.id_of_Table_A
where c.category = 'cat1' and a.id in (1, 2, 3)
Но в этом утверждении перечислены только
id_of_Table_A, cat1
который содержит только cat1 данного id_of_Table_A. Я надеюсь получить что-то вроде:
1, cat1, cat2, cat3
если cat1, cat2 и cat3 существуют в таблице C для данного идентификатора 1 в таблице A.
Если один оператор не может дать желаемых результатов, то хранимая процедура подойдет.
Обновлять
В следующем примере результаты
1, cat1, cat2, cat3
cat1, cat2, cat3 должны исходить из трех разных записей в таблице B. Предположим, у нас есть эти таблицы и строки:
Table A: 1,2
Table B: (1,1), (2,1), (3,1), (4,2), (5,2), (6,2)
Table c: (1,1,'cat3'), (2,2,'cat1'), (3,3,'cat2'), (4,4,'cat1'), (5,5,'cat4'), (6,6,'cat2')
Тогда ожидаемые результаты должны быть:
1, cat1, cat2, cat3
По сути, отношения таблиц образуют дерево. Я надеюсь найти корень (таблица A) с листьями cat1, cat2 и cat3 на непересекающихся путях от корня.
where c.category = 'cat1' означает, что вас интересует только cat1.
Это просто пример. Я не могу написать запрос для получения результатов cat2 и cat3.
Ответы 1
Вы можете объединить все категории для id_of_b в одно значение xml, а затем отобразить категории в столбцах. Что-то вроде этого.
declare @a table(id int)
declare @b table(id int,id_of_a int)
declare @c table(id int,id_of_b int,category varchar(50))
insert @a values(1),(2)
insert @b values(1,1),(2,1),(3,2)
insert @c values(1,1,'cat1'),(2,1,'cat2'),(3,1,'cat3'),(4,2,'cat4')
;with cte as(
select a.id,
cast((select category from @c c where c.id_of_b = b.id for xml auto,root,type) as xml) xcat
from @a a inner join @b b on a.id = b.id_of_a
)
select id,
t.v.value('c[1]/@category','varchar(50)') cat1,
t.v.value('c[2]/@category','varchar(50)') cat2,
t.v.value('c[3]/@category','varchar(50)') cat3
from cte
cross apply xcat.nodes('root') t(v)
Обновленный ответ на обновленный вопрос
declare @a table(id int)
declare @b table(id int,id_of_a int)
declare @c table(id int,id_of_b int,category varchar(50))
insert @a values(1),(2)
insert @b values (1,1), (2,1), (3,1), (4,2), (5,2), (6,2)
insert @c values (1,1,'cat3'), (2,2,'cat1'), (3,3,'cat2'), (4,4,'cat1'), (5,5,'cat4'), (6,6,'cat2')
;with cte as(
select a.id,
cast((select category from @c c inner join @b b on c.id_of_b = b.id
where b.id_of_a=a.id
for xml auto,root,type) as xml) xcat
from @a a
)
select id,
t.v.value('c[1]/@category','varchar(50)') cat1,
t.v.value('c[2]/@category','varchar(50)') cat2,
t.v.value('c[3]/@category','varchar(50)') cat3
from cte
cross apply xcat.nodes('root') t(v)
Привет, Алекс, кажется, синтаксическая ошибка для ;with cte as.... Management Studio указывает на наличие ошибки.
Все еще есть сообщение об ошибке в Management Studio. Ошибка синтаксиса.
какое сообщение?
Сообщение 102, уровень 15, состояние 1, строка 14. Неверный синтаксис рядом с ')'.
Спасибо за обновление. Пример работает, но результаты не такие, как я ожидал. Прошу прощения за то, что пост не достаточно ясен. Пожалуйста, смотрите мой обновленный пост.
Алекс, я никогда не использовал предложенный вами подход. Быстрый вопрос: какова его производительность в случае большой таблицы (миллионы записей)?
Демонстрационные данные лучше всего использовать как DDL + ДМЛ. Пожалуйста, редактировать ваш вопрос, чтобы включить его. Для получения дополнительной информации прочитай это.