一道比较难的SQL请教高手^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
四张表如下所示:A,B,C,D
CatalogId CatalogName ParentCatalogID
----------- ----------------- ---------------
1 笔记本 0
2 台式机 0
DeviceID DeviceCatalogID DeviceBrand DeviceModel DeviceDesc DevicePrice
----------- --------------- --------------------- ---------------
------
1 1 IBM 高端配制 Core2 2.0G/X1600显卡 500.00
2 1 IBM 中端配置 迅驰P4M1.6G/独立9500显卡 800.00
3 2 联想 中端配制 PD820/128M1600显卡 500.00
4 2 联想 高端配制 P42.4C/64M显卡 1000.00
DevicePartID DeviceID DevicePartName
------------ ----------- ---------------------
1 1 内存
2 2 光驱
3 1 显示器
4 3 硬盘
DeviceDetailID DevicePartID DeviceDetailDesc DeviceDetailPrice
-------------- ------------ ------------------ ---------------------
1 1 512G三星内存 540.00
2 1 1G三星内存 800.00
3 2 128MPPT光驱 300.00
4 4 120G三星硬盘 800.00
5 3 方正纯平显示器 700.00
6 1 128M三星内存 300.00
并且数据是不定的
想要得到 结果（我只举了一条记录，另的省约了）
CatalogId CatalogName DeviceID DeviceBrand DeviceModel DeviceDesc DevicePrice DevicePartID DevicePartName DeviceDetailID
1 笔记本 1 IBM 高端配制 Core2 2.0G/X1600显卡 500.00 1,3 内存,显示器 1,2,6,5
DeviceDetailDesc
512G三星内存,1G三星内存,128M三星内存,方正纯平显示器
[解决办法]
create table c(CatalogId int,
CatalogName varchar(20),
ParentCatalogID int)
insert into c select 1, '笔记本 ', 0
union all select 2, '台式机 ', 0
create table d(DeviceID int,
DeviceCatalogID int,
DeviceBrand varchar(20),
DeviceModel varchar(20),
DeviceDesc varchar(50),
DevicePrice money)
insert into d select 1, 1, 'IBM ', '高端配制 ', 'Core2 2.0G/X1600显卡 ', 500.00
union all select 2, 1, 'IBM ', '中端配置 ', '迅驰P4M1.6G/独立9500显卡 ', 800.00
union all select 3, 2, '联想 ', '中端配制 ', 'PD820/128M1600显卡 ', 500.00
union all select 4, 2, '联想 ', '高端配制 ', 'P42.4C/64M显卡 ', 1000.00
create table dp(DevicePartID int,
DeviceID int,
DevicePartName varchar(10))
insert into dp select 1, 1, '内存 '
union all select 2, 2, '光驱 '
union all select 3, 1, '显示器 '
union all select 4, 3, '硬盘 '
create table dd(DeviceDetailID int,
DevicePartID int,
DeviceDetailDesc varchar(50),
DeviceDetailPrice money)
insert into dd select 1, 1, '512G三星内存 ', 540.00
union all select 2, 1, '1G三星内存 ', 800.00
union all select 3, 2, '128MPPT光驱 ', 300.00
union all select 4, 4, '120G三星硬盘 ', 800.00
union all select 5, 3, '方正纯平显示器 ', 700.00
union all select 6, 1, '128M三星内存 ', 300.00
alter FUNCTION wyj(@char varchar(20),@type varchar(10))
RETURNS varchar(1000)
BEGIN
DECLARE @re varchar(1000)
set @re= ' '
if(@type= '1.1 ')
begin
select @re=@re+cast(dp.devicepartid as varchar(10))+ ', ' from c
left join d
on d.deviceid=c.catalogid
left join dp
on dp.DeviceID=d.devicecatalogid
left join dd
on dd.DevicePartID=dp.DevicePartID
where c.catalogid=@char
group by dp.devicepartid
RETURN @re
end
if(@type= '1.2 ')
begin
select @re=@re+cast(dp.devicepartname as varchar(50))+ ', ' from c
left join d
on d.deviceid=c.catalogid
left join dp
on dp.DeviceID=d.devicecatalogid
left join dd
on dd.DevicePartID=dp.DevicePartID
where c.catalogid=@char
group by dp.devicepartid,dp.devicepartname
RETURN @re
end
if(@type= '2.1 ')
begin
select @re=@re+cast(dd.DeviceDetailID as varchar(10))+ ', ' from c
left join d
on d.deviceid=c.catalogid
left join dp
on dp.DeviceID=d.devicecatalogid
left join dd
on dd.DevicePartID=dp.DevicePartID
where c.catalogid=@char
group by dd.DeviceDetailID
RETURN @re
end
if(@type= '2.2 ')
begin
select @re=@re+cast(dd.DeviceDetailDesc as varchar(50))+ ', ' from c
left join d
on d.deviceid=c.catalogid
left join dp
on dp.DeviceID=d.devicecatalogid
left join dd
on dd.DevicePartID=dp.DevicePartID
where c.catalogid=@char
group by dd.DeviceDetailID,dd.DeviceDetailDesc
RETURN @re
end
RETURN @re
END
select c.catalogname,d.*,dbo.wyj(c.catalogid, '1.1 '),dbo.wyj(c.catalogid, '1.2 '),dbo.wyj(c.catalogid, '2.1 '),dbo.wyj(c.catalogid, '2.2 ')
from c
left join d
on d.deviceid=c.catalogid
---做这种题真郁闷~
/*
笔记本
11IBM高端配制Core2 2.0G/X1600显卡500.00001,3,内存,显示器,1,2,5,6,512G三星内存,1G三星内存,方正纯平显示器,128M三星内存,台式机
21IBM中端配置迅驰P4M1.6G/独立9500显卡800.00001,3,内存,显示器,1,2,5,6,512G三星内存,1G三星内存,方正纯平显示器,128M三星内存,
*/
