表p
Pid P1 P1 P3
1 TTT TTT TTT
2 CCC CCC CCC
3 AAA AAA AAA
表c
ID PID C1 C2
1 1 AAA AAA
2 1 BBB BBB
3 1 CCC CCC
4 2 DDD DDD
5 2 DDD DDD
6 3 EEE EEE结果
表c
ID PID C1 C2
1 1 AAA AAA
2 1 BBB BBB
4 2 aaa aaa
5 2 bbb bbb
6 3 aaa aaa
7 3 aaa aaa
如何实现这样的结果?P表有2千条记录
Pid P1 P1 P3
1 TTT TTT TTT
2 CCC CCC CCC
3 AAA AAA AAA
表c
ID PID C1 C2
1 1 AAA AAA
2 1 BBB BBB
3 1 CCC CCC
4 2 DDD DDD
5 2 DDD DDD
6 3 EEE EEE结果
表c
ID PID C1 C2
1 1 AAA AAA
2 1 BBB BBB
4 2 aaa aaa
5 2 bbb bbb
6 3 aaa aaa
7 3 aaa aaa
如何实现这样的结果?P表有2千条记录
就是根据P表的ID值分别在C表中插入此ID对应的两条固定记录
原有的P表跟C表是一对多的关系,P表的ID对应C表的PID可能是一条或多条值.现在我想更改为P表的ID在C表中固定为三条记录。其它的都删除,也可以一次性删除再插入三条固定值,前提是要根据P表的ID来的。
没看明白...
update c set c1=... ,c2=.. from c join p on p.id=c.pid
declare @P table(PID int identity(1,1),P1 varchar(8),P2 varchar(8),P3 varchar(8))
insert into @P(P1,P2,P3) select 'TTT','TTT','TTT'
insert into @P(P1,P2,P3) select 'CCC','CCC','CCC'
insert into @P(P1,P2,P3) select 'AAA','AAA','AAA'
insert into @P(P1,P2,P3) select 'FFF','FFF','FFF' declare @C table(ID int identity(1,1),PID int,C1 varchar(8),C2 varchar(8))
insert into @c(PID,C1,C2) select 1,'AAA','AAA'
insert into @c(PID,C1,C2) select 1,'BBB','BBB'
insert into @c(PID,C1,C2) select 1,'CCC','CCC'
insert into @c(PID,C1,C2) select 2,'DDD','DDD'
insert into @c(PID,C1,C2) select 2,'DDD','DDD'
insert into @c(PID,C1,C2) select 3,'EEE','EEE' --按降序删除@C表中同一PID所对应记录数大于2的记录
delete t from @C t where 1<(select count(1) from @C where PID=t.PID and ID<t.ID)--补足@C表同一PID分组对应记录不足两条的数据
insert into @C(PID,C1,C2)
select
a.PID,a.P1,a.P2
from
(select 1 as Num,PID,P1,P2 from @P union select 2,PID,P1,P2 from @p) a,
(select PID,isnull((select count(1) from @C where PID=t.PID),0) as Num from @P t) b
where
a.PID=b.PID and a.Num<=(2-b.Num)
order by a.PID,a.Num
select * from @C order by ID/*
ID PID C1 C2
----------- ----------- -------- --------
1 1 AAA AAA
2 1 BBB BBB
4 2 DDD DDD
5 2 DDD DDD
6 3 EEE EEE
7 3 AAA AAA
8 4 FFF FFF
9 4 FFF FFF
*/
declare @P table(PID int identity(1,1),P1 varchar(8),P2 varchar(8),P3 varchar(8))
insert into @P(P1,P2,P3) select 'TTT','TTT','TTT'
insert into @P(P1,P2,P3) select 'CCC','CCC','CCC'
insert into @P(P1,P2,P3) select 'AAA','AAA','AAA'
insert into @P(P1,P2,P3) select 'FFF','FFF','FFF' declare @C table(ID int identity(1,1),PID int,C1 varchar(8),C2 varchar(8))
insert into @C(PID,C1,C2) select 1,'AAA','AAA'
insert into @C(PID,C1,C2) select 1,'BBB','BBB'
insert into @C(PID,C1,C2) select 1,'CCC','CCC'
insert into @C(PID,C1,C2) select 2,'DDD','DDD'
insert into @C(PID,C1,C2) select 2,'DDD','DDD'
insert into @C(PID,C1,C2) select 3,'EEE','EEE' --删除@C表中的记录
delete @C--在@C表中对应每一个PID值 insert 2条记录;如果需要固定3条记录,则在子查询的末尾增加一个 union all select PID,P1,P2 from @P
insert into @C(PID,C1,C2)
select
*
from
(select PID,P1,P2 from @P
union all
select PID,P1,P2 from @P) t
order by
PIDselect * from @C order by ID/*
ID PID C1 C2
----------- ----------- -------- --------
7 1 TTT TTT
8 1 TTT TTT
9 2 CCC CCC
10 2 CCC CCC
11 3 AAA AAA
12 3 AAA AAA
13 4 FFF FFF
14 4 FFF FFF
*/