有点难度的SQL题^^^^^^^^^^^^^^^^^^^^^
DeviceID DeviceCatalogID DeviceBrand DeviceModel
----------- --------------- ----------------------------------
1 1 IBM 高端配制
2 1 IBM 中端配置
DevicePartID DeviceID DevicePartName
------------ ----------- --------------------
1 1 内存
2 2 光驱
3 1 显示器
DeviceDetailID DevicePartID DeviceDetailDesc DeviceDetailPrice
-------------- ------------ --------------------------------------
2 1 1G三星内存 800.00
3 2 128MPPT光驱 300.00
5 3 方正纯平显示器 700.00
6 1 128M三星内存 300.00
7 1 512M三星内存 500.00
现有三张表A,B,C 数据分别如上所示(关系是A表的DeviceID 对应 B表的DeviceID,B表的DevicePartID 对应C表的DevicePartID)
要求做个复制操作即我要求原样复制一份 条件是A表的DeviceID = 1的记录 (注解:DeviceID =1 时对应B表有两条 对应C表有三条记录)
想要的结果如下图所示:
DeviceID DeviceCatalogID DeviceBrand DeviceModel
----------- --------------- ----------------------------------
1 1 IBM 高端配制
2 1 IBM 中端配置
3 1 IBM 高端配制 ---这是复制的结
DevicePartID DeviceID DevicePartName
------------ ----------- --------------------
1 1 内存
2 2 光驱
3 1 显示器
4 1 内存 -----这两条
5 1 显示器 ----
DeviceDetailID DevicePartID DeviceDetailDesc DeviceDetailPrice
-------------- ------------ --------------------------------------
2 1 1G三星内存 800.00
3 2 128MPPT光驱 300.00
5 3 方正纯平显示器 700.00
6 1 128M三星内存 300.00
7 1 512M三星内存 500.00
8 4 1G三星内存 800.00 --- 这四条
9 4 128M三星内存 300.00 ---
10 4 512M三星内存 500.00 ---
11 5 方正纯平显示器 700.00 ---
[解决办法]
Create Table A
(DeviceIDInt Identity(1, 1),
DeviceCatalogIDInt,
DeviceBrandVarchar(10),
DeviceModelNvarchar(50))
GO
Insert A Select 1, 'IBM ', N '高端配制 '
Union All Select 1, 'IBM ', N '中端配置 '
Create Table B
(DevicePartIDInt Identity(1, 1),
DeviceIDInt,
DevicePartNameNvarchar(50))
Insert B Select 1, N '内存 '
Union All Select 2, N '光驱 '
Union All Select 1, N '显示器 '
Create Table C
(DeviceDetailIDInt Identity(1, 1),
DevicePartIDInt,
DeviceDetailDescNvarchar(50),
DeviceDetailPriceNumeric(10, 2))
SET IDENTITY_INSERT C ON
Insert C(DeviceDetailID, DevicePartID, DeviceDetailDesc,DeviceDetailPrice) Select 2, 1, N '1G三星内存 ', 800.00
Union All Select 3, 2, N '128MPPT光驱 ', 300.00
Union All Select 5, 3, N '方正纯平显示器 ', 700.00
Union All Select 6, 1, N '128M三星内存 ', 300.00
Union All Select 7, 1, N '512M三星内存 ', 500.00
SET IDENTITY_INSERT C OFF
GO
Insert A Select DeviceCatalogID, DeviceBrand, DeviceModel From A Where DeviceID = 1
Insert B Select IDENT_CURRENT( 'A '), DevicePartName From B Where DeviceID = 1
Insert C Select D.DevicePartID, C.DeviceDetailDesc, C.DeviceDetailPrice From C Inner Join B On B.DevicePartID = C.DevicePartID Inner Join (Select Max(DevicePartID) As DevicePartID, DevicePartName From B Group By DevicePartName) D On B.DevicePartName = D.DevicePartName Where B.DeviceID = 1 Order By D.DevicePartID
Select * From A
Select * From B
Select * From C
GO
Drop table A, B, C
--Result
/*
DeviceIDDeviceCatalogIDDeviceBrandDeviceModel
11IBM高端配制
21IBM中端配置
31IBM高端配制
DevicePartIDDeviceIDDevicePartName
11内存
22光驱
31显示器
43内存
53显示器
DeviceDetailIDDevicePartIDDeviceDetailDescDeviceDetailPrice
211G三星内存800.00
32128MPPT光驱300.00
53方正纯平显示器700.00
61128M三星内存300.00
71512M三星内存500.00
841G三星内存800.00
94128M三星内存300.00
104512M三星内存500.00
115方正纯平显示器700.00
*/