【拿分题】紧急救援，一道SQL问题。

renyiqiu 2014-11-20 01:01:01

是这样的，表 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代码，解决立马结贴。

...全文

382 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

rfq 2014-11-27

打赏
举报

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

山寨DBA 2014-11-21

打赏
举报

引用 10 楼 ky_min 的回复:

[quote=引用 9 楼 hwhmh2010 的回复:] [quote=引用 4 楼 renyiqiu 的回复:] [quote=引用 3 楼 hwhmh2010 的回复:] 加个排序

我想排除原来有bg3的pid记录呢[/quote] 不明白你说的排除原来有bg3的pid记录是什么意思，如果仅仅是删除的话就原来的最后一句查询加个条件就可以： select * from test where subject<>'bg3' order by pid,id [/quote]

[/quote] ？？？

还在加载中灬 2014-11-21

打赏
举报

引用 9 楼 hwhmh2010 的回复:

[quote=引用 4 楼 renyiqiu 的回复:] [quote=引用 3 楼 hwhmh2010 的回复:] 加个排序

山寨DBA 2014-11-21

打赏
举报

引用 4 楼 renyiqiu 的回复:

[quote=引用 3 楼 hwhmh2010 的回复:] 加个排序

我想排除原来有bg3的pid记录呢[/quote] 不明白你说的排除原来有bg3的pid记录是什么意思，如果仅仅是删除的话就原来的最后一句查询加个条件就可以： select * from test where subject<>'bg3' order by pid,id

山寨DBA 2014-11-21

打赏
举报

引用 5 楼 ky_min 的回复:

[quote=引用 4 楼 renyiqiu 的回复:] [quote=引用 3 楼 hwhmh2010 的回复:] 加个排序

我想排除原来有bg3的pid记录呢[/quote]我的不行吗?还是ID有什么要求,是自增吗[/quote] 没看你的，呵呵，ID一般都是自增ID，或者是自增ID主键

renyiqiu 2014-11-20

打赏
举报

引用 3 楼 hwhmh2010 的回复:

加个排序

我想排除原来有bg3的pid记录呢

山寨DBA 2014-11-20

打赏
举报

加个排序

山寨DBA 2014-11-20

打赏
举报



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

结果：

看看这个行不？

还在加载中灬 2014-11-20

打赏
举报

INSERT INTO paper_list
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

大概这样吧，如果id是自增列，那很好办，把上面Select 中的T1.id去掉就可以了如果不是自增，而且不可以重复，那就要进一步处理了

lzw_0736 2014-11-20

打赏
举报


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

lzw_0736 2014-11-20

打赏
举报


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
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

还在加载中灬 2014-11-20