如上圖
紅框和綠框分別為兩個群組
希望輸入某料號找到該群組所有料號
*一個料號只會有一個群組
*料號會對應該群組"任意料號"
測試資料
`
CREATE TABLE #a(
pno nvarchar(20),
pro nvarchar(20)
);
insert into #a
select '10000-00072-10','10000-00072-01'
insert into #a
select '10000-00072-02','10000-00072-07'
insert into #a
select '10000-00072-08','10000-00072-01'
insert into #a
select '10000-00072-07','10000-00072-08'
insert into #a
select '10000-00072-05','10000-00072-01'
insert into #a
select '10000-00072-09','10000-00072-03'
insert into #a
select '10000-00072-06','10000-00072-03'
insert into #a
select '10000-00072-04','10000-00072-03'`
上圖設計方式較簡單
但因產品長度,半徑...規格不同
導致命名群組名稱會有困難
目前不考慮這方式
或是說只能用這方法
希望可以聽聽前輩們想法
我認為上面的方式比較好,做一個對應表,硬要做到你想的那樣也是可以,只是很奇怪
declare @a table(
pno nvarchar(20),
pro nvarchar(20)
);
declare @b table(
keyword nvarchar(20)
);
declare @c table(
keyword nvarchar(20)
);
insert into @a
values( '10000-00072-10','10000-00072-01'),
( '10000-00072-02','10000-00072-07'),
( '10000-00072-08','10000-00072-01'),
( '10000-00072-07','10000-00072-08'),
( '10000-00072-05','10000-00072-01'),
( '10000-00072-09','10000-00072-03'),
( '10000-00072-06','10000-00072-03'),
( '10000-00072-04','10000-00072-03')
DECLARE @InputKey as NVARCHAR(20)
set @InputKey ='10000-00072-03' ;
insert into @c
values (@InputKey)
WHILE (SELECT count(keyword) from @c) >= 1
BEGIN
insert into @b
select keyword from @c
delete from @c
insert into @c
select pro as keyword from (
select pro from @a
inner join @b on keyword = pno
union
select pno from @a
inner join @b on keyword = pro
) as a where a.pro not in (select keyword from @b)
END
Select pno,pro from @a
inner join @b on keyword = pro
CREATE TABLE #a(
AID int,
pno nvarchar(20),
pro nvarchar(20),
BID int,
);
create table #b(
BID int,
)
insert into #a(AID, PNO, BID)
values
(1, '10000-00072-10', 1),
(2, '10000-00072-01', 1),
(3, '10000-00072-08', 1),
(4, '10000-00072-05', 1),
(5, '10000-00072-07', 1),
(6, '10000-00072-02', 1),
(7, '10000-00072-09', 2),
(8, '10000-00072-06', 2),
(9, '10000-00072-04', 2),
(10, '10000-00072-03', 2)
insert into #b(BID)
values (1), (2)
select * from #a A1
inner join #b B on A1.BID = B.BID
inner join #a A2 on B.BID = A2.BID
where A1.PNO = '10000-00072-10'