如果我加入两个表,我将得到下面的表
SELECT a.Category, a.categoryid, b.parentid,b.Level
FROM a JOIN b ONa.CatId=b.Catid
实际数据:-
Category Catid ParentID Level
-----------------------------------------------------------
Pumps & Accss 20 0 0
Inground Pumps 213 20 1
Above Ground 215 20 1
Commercial Pumps 216 20 1
Hawrd super pumps 814 213 2
Hywrd north pumps 815 213 2
这是一个使用游标的示例,我必须对所有类别ID都这样应用...
我需要显示以下数据:
Cat1 Cat2 Cat3 ItemNo
------------------------------------------------------------
Pumps & Accss Above Ground Hawrd super AX007123
Pumps & Accss Above Ground Hywrd north AX0071201
Pumps & Accss Commercial Pu Hawrd super AX007754
Pumps & Accss Commercial Pu Hawrd super AX0077891
Pumps & Accss Inground Pumps Hywrd north AX0071251
Cat1列中的Level 1和Cat2列中的Level2像这样i
需要获取输出。像这样,我有10个级别,请发布一些好的答案来解决此问题。
检查此...您可以在同一张表上多次使用以建立多级联接
declare @category table(categoryId int, category char(100), principalId int)
declare @article table(articleId int, article varchar(100), categoryId int)
insert into @category(categoryId, category, principalId)
values (1,'One', null)
insert into @category(categoryId, category, principalId)
values (2,'two', null)
insert into @category(categoryId, category, principalId)
values (11,'eleven', 1)
insert into @category(categoryId, category, principalId)
values (21,'twenty one', 2)
insert into @category(categoryId, category, principalId)
values (22,'twenty two', 2)
insert into @category(categoryId, category, principalId)
values (111,'one hundred eleven', 11)
insert into @category(categoryId, category, principalId)
values (211,'two hundred eleven', 21)
insert into @category(categoryId, category, principalId)
values (221,'two hundred twenty one', 22)
insert into @category(categoryId, category, principalId)
values (2211,'two thousand two hundred twenty one', 221)
insert into @article(articleId, article, categoryId)
values (1, 'Article 1', 111)
insert into @article(articleId, article, categoryId)
values (2, 'Article 2', 211)
insert into @article(articleId, article, categoryId)
values (3, 'Article 3', 221)
insert into @article(articleId, article, categoryId)
values (4, 'Article 4', 2211)
select coalesce(c5.category,'') c5,
coalesce(c4.category,'') c4,
coalesce(c3.category,'') c3,
coalesce(c2.category,'') c2,
coalesce(c1.category,'') c1,
a.article
from @article a
left join @category c1 on c1.categoryId = a.categoryId
left join @category c2 on c2.categoryId = c1.principalId
left join @category c3 on c3.categoryId = c2.principalId
left join @category c4 on c4.categoryId = c3.principalId
left join @category c5 on c5.categoryId = c4.principalId
如果您共享表格定义,这有助于改善我的答案,希望它可以帮助您指出正确的方向
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句