有个Target表有TargetID ,
Title 列另一个为Target_future 表,记录将来的时间,有这些列:TargetID PK
sProdDate PK
iShift PK
iHour1
iHour2
iHour3
iHour4
iHour5
iHour6
iHour7
iHour8
现在希望把iHour 分离出来成为另一个表存放对应于iHour的值(即Hour 以后可以设置n个,不止8个),如何分啊? 划分后,应该如何定义主键? 谢谢了
Title 列另一个为Target_future 表,记录将来的时间,有这些列:TargetID PK
sProdDate PK
iShift PK
iHour1
iHour2
iHour3
iHour4
iHour5
iHour6
iHour7
iHour8
现在希望把iHour 分离出来成为另一个表存放对应于iHour的值(即Hour 以后可以设置n个,不止8个),如何分啊? 划分后,应该如何定义主键? 谢谢了
)insert into tb3
select TargetID,sProdDate,iShift,iHour1 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour2 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour3 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour4 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour5 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour6 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour7 as iHour from Target_future
union all
select TargetID,sProdDate,iShift,iHour8 as iHour from Target_future
CREATE TABLE tb3 (TargetID PK,sProdDate PK,iShift PK,iHourType)
CREATE TABLE tb4 (iHourType, [value])
--Move data
INSERT INTO tb4
select iHourType, [value] from
(
SELECT * FROM Target_future
UNPIVOT
([value] FOR iHourType IN([iHour1], [iHour2], [iHour3], [iHour4], [iHour5], [iHour6], [iHour7], [iHour8])) b
) T
sTargetID nvarchar(50),sProdDate nvarchar(50),iShift int,iHourType nvarchar(50), ihourValue)
那么主键需要设置为sTargetID,sProdDate,iShift ,iHourType ? 这样感觉设计的主键太长了