--配件表
create table Thardware
(
FhId int identity(1,1) primary key, --配件id
FxmName nvarchar(50) not null, --项目名如:内存
FfId int --设备id
)
--设备表
create table Tfacility
(
FfId int identity(1,1) primary key,
FfName nvarchar(50)
)alter table Thardware
add constraint FK_FfId foreign key(FfId) references Tfacility(FfId) insert into Tfacility values('pc-001');
insert into Tfacility values('pc-002');
insert into Tfacility values('pc-003');insert into Tfacility values('pc-003');insert into Thardware values('内存',1);
insert into Thardware values('cpu',1);insert into Thardware values('内存',2);
insert into Thardware values('cpu',2);insert into Thardware values('内存',3);
insert into Thardware values('cpu',3);FfName 内存 cpu
pc-001 内存 cpu
pc-002 内存 cpu
pc-003 内存 cpu
create table Thardware
(
FhId int identity(1,1) primary key, --配件id
FxmName nvarchar(50) not null, --项目名如:内存
FfId int --设备id
)
--设备表
create table Tfacility
(
FfId int identity(1,1) primary key,
FfName nvarchar(50)
)alter table Thardware
add constraint FK_FfId foreign key(FfId) references Tfacility(FfId) insert into Tfacility values('pc-001');
insert into Tfacility values('pc-002');
insert into Tfacility values('pc-003');insert into Tfacility values('pc-003');insert into Thardware values('内存',1);
insert into Thardware values('cpu',1);insert into Thardware values('内存',2);
insert into Thardware values('cpu',2);insert into Thardware values('内存',3);
insert into Thardware values('cpu',3);FfName 内存 cpu
pc-001 内存 cpu
pc-002 内存 cpu
pc-003 内存 cpu
select a.FfName,
max(case when px = 1 then b.FxmName else '' end) as [内存],
max(case when px = 2 then b.FxmName else '' end) as [cpu]
from Tfacility a
join
(select *,px = row_number() over (partition by FfId order by FhId) from Thardware)b
on a.FfId = b.FfId
group by a.FfName