是这样的，表 paper_list
id pid subject answer
1 1 bg1 20:30
2 1 bg2 20:40
3 1 bg3 10
4 2 bg1 20:45
5 2 bg2 20:50
6 2 bg3 5
7 3 bg1 19:45
8 3 bg2 19:55
9 4 bg1 21:45
10 4 bg2 21:55
pid为产品的编号，一般产品有三条记录，bg1，bg2，bg3，而bg3则是bg2减去bg1的时间差，单位为分钟，现在的问题是，因为数据接口的问题导致了部分产品的bg3丢失了，如产品3和产品4，现在要补回去，各位大神能否帮忙写下可行的SQL代码，解决立马结贴。
------解决思路----------------------
INSERT INTO paper_list大概这样吧，如果id是自增列，那很好办，把上面Select 中的T1.id去掉就可以了
SELECT
T1.id,T1.pid,'bg3',CAST(DATEDIFF(MINUTE,CAST(T1.answer AS DATETIME),CAST(T2.answer AS DATETIME))AS VARCHAR(10))
FROM paper_list T1
JOIN paper_list T2 ON T1.pid=T2.pid
LEFT JOIN paper_list T3 ON T1.pid=T3.pid AND T3.subject='bg3'
WHERE T1.subject='bg1'AND T2.subject='bg2'AND T3.id IS NULL
如果不是自增，而且不可以重复，那就要进一步处理了
------解决思路----------------------
create table test(
ID int identity(1,1) not null,
pid int not null,
subject varchar(10) not null,
answer varchar(10) not null
)
insert into test
select 1,'bg1','20:30' union all
select 1,'bg2','20:40' union all
select 1,'bg3','10' union all
select 2,'bg1','20:45' union all
select 2,'bg2','20:50' union all
select 2,'bg3','5' union all
select 3,'bg1','19:45' union all
select 3,'bg2','19:55' union all
select 4,'bg1','21:45' union all
select 4,'bg2','21:55'
select * from test
insert into test
select a.pid,'bg3',datediff(mi,a.answer,b.answer) from test a inner join test b on a.pid=b.pid and b.id>a.id
inner join
(
select pid,max(subject) maxsubject from test group by pid having(max(subject))='bg2'
)c on a.pid=c.pid
select * from test
结果：
看看这个行不？
------解决思路----------------------
create table #test(
ID int identity(1,1) not null,
pid int not null,
subject varchar(10) not null,
answer varchar(10) not null
)
insert into #test
select 1,'bg1','20:30' union all
select 1,'bg2','20:40' union all
select 1,'bg3','10' union all
select 2,'bg1','20:45' union all
select 2,'bg2','20:50' union all
select 2,'bg3','5' union all
select 3,'bg1','19:45' union all
select 3,'bg2','19:55' union all
select 4,'bg1','21:45' union all
select 4,'bg2','21:55'
select * from #test
insert into #test (pid,subject,answer)
SELECT a.pid,'bg3',datediff(mi,a.answer,b.answer)
FROM #test a
JOIN #test b ON a.pid=b.pid
WHERE a.subject='bg1' AND b.subject='bg2' AND NOT EXISTS(SELECT 1 FROM #test WHERE subject='bg3' AND pid=a.pid)
select * from #test ORDER BY 2,3
------解决思路----------------------
insert into test(pid,'bg3',answer)
select a.pid,DATEDIFF(mi,a.answer,b.answer) from test a,test b where a.subject='bg1' and b.subject='bg2'
and a.pid=b.pid and a.pid in
(
select distinct pid from dbo.test a where not exists(select * from dbo.test b where a.pid=b.pid and b.subject='bg3' )
)
select pid,'bg3',DATEDIFF(mi,answer1,answer2) from
(
select a.pid,a.answer as answer1,b.answer As answer2,c.ID from test a inner join test b on a.pid=b.pid left join test c on
a.pid=c.pid and c.subject='bg3' where a.subject='bg1' and b.subject='bg2'
) as 结果 where id is null