各位高手,请教下如何用SQL实现将两个查询结果插入到一个表中,需查询每个样本编号的最后一个工序的参数信息组合成一个表,请问如何实现,最好执行时间短一点的,因为数据量可能会很大,谢谢啦!
例如表 Info序号 样本编号 工序 参数 时间
1 1 1 1.2 2012-8-1
2 2 1 1.4 2012-8-1
3 3 1 1.8 2012-8-3
4 1 1 1.6 2012-8-4
5 2 1 1.2 2012-8-5
6 3 1 1.4 2012-8-5
7 1 2 2.1 2012-8-7
8 2 2 2.3 2012-8-8
9 3 2 2.8 2012-8-9
10 1 2 1.2 2012-8-10
11 2 2 1.4 2012-8-11
12 3 2 2.1 2012-8-12
13 1 2 2.3 2012-8-13
14 2 2 1.6 2012-8-14
15 3 2 1.2 2012-8-15
16 1 3 1.4 2012-8-16
17 2 3 2.1 2012-8-17
18 3 3 2.3 2012-8-18
19 1 3 2.8 2012-8-19
20 2 3 1.2 2012-8-20
21 3 3 1.6 2012-8-21
22 1 3 1.2 2012-8-22
23 2 3 1.4 2012-8-23
24 3 3 2.1 2012-8-23结果
表 Result样本编号 工序1_参数 工序2_参数
1 1.6 2.3
2 1.2 1.6
3 1.4 1.2
例如表 Info序号 样本编号 工序 参数 时间
1 1 1 1.2 2012-8-1
2 2 1 1.4 2012-8-1
3 3 1 1.8 2012-8-3
4 1 1 1.6 2012-8-4
5 2 1 1.2 2012-8-5
6 3 1 1.4 2012-8-5
7 1 2 2.1 2012-8-7
8 2 2 2.3 2012-8-8
9 3 2 2.8 2012-8-9
10 1 2 1.2 2012-8-10
11 2 2 1.4 2012-8-11
12 3 2 2.1 2012-8-12
13 1 2 2.3 2012-8-13
14 2 2 1.6 2012-8-14
15 3 2 1.2 2012-8-15
16 1 3 1.4 2012-8-16
17 2 3 2.1 2012-8-17
18 3 3 2.3 2012-8-18
19 1 3 2.8 2012-8-19
20 2 3 1.2 2012-8-20
21 3 3 1.6 2012-8-21
22 1 3 1.2 2012-8-22
23 2 3 1.4 2012-8-23
24 3 3 2.1 2012-8-23结果
表 Result样本编号 工序1_参数 工序2_参数
1 1.6 2.3
2 1.2 1.6
3 1.4 1.2
insert into Result([工序1_参数],[工序2_参数]) select [工序1_参数],[工序2_参数] from Info
能不能说具体些,表Info中只有一个参数字段呀?某一样本编号的的某个工序还存在参数为空的情况,怎么写SQL语句啊?
谢谢啦!
既要查询又要将查询结果插入到表中,
是想查询每个样本编号的工序为1的最后一个记录的参数值和工序为2的最后一个记录的参数值,然后将两个值存到另一个表中,像下面这样:表 Result:样本编号 工序1_参数 工序2_参数
1 1.6 2.3
2 1.2 1.6
3 1.4 1.2
谢谢啦
create table Info
(序号 int, 样本编号 int, 工序 int, 参数 decimal(5,1), 时间 date)insert into Info
select 1, 1, 1, 1.2, '2012-8-1' union all
select 2, 2, 1, 1.4, '2012-8-1' union all
select 3, 3, 1, 1.8, '2012-8-3' union all
select 4, 1, 1, 1.6, '2012-8-4' union all
select 5, 2, 1, 1.2, '2012-8-5' union all
select 6, 3, 1, 1.4, '2012-8-5' union all
select 7, 1, 2, 2.1, '2012-8-7' union all
select 8, 2, 2, 2.3, '2012-8-8' union all
select 9, 3, 2, 2.8, '2012-8-9' union all
select 10, 1, 2, 1.2, '2012-8-10' union all
select 11, 2, 2, 1.4, '2012-8-11' union all
select 12, 3, 2, 2.1, '2012-8-12' union all
select 13, 1, 2, 2.3, '2012-8-13' union all
select 14, 2, 2, 1.6, '2012-8-14' union all
select 15, 3, 2, 1.2, '2012-8-15' union all
select 16, 1, 3, 1.4, '2012-8-16' union all
select 17, 2, 3, 2.1, '2012-8-17' union all
select 18, 3, 3, 2.3, '2012-8-18' union all
select 19, 1, 3, 2.8, '2012-8-19' union all
select 20, 2, 3, 1.2, '2012-8-20' union all
select 21, 3, 3, 1.6, '2012-8-21' union all
select 22, 1, 3, 1.2, '2012-8-22' union all
select 23, 2, 3, 1.4, '2012-8-23' union all
select 24, 3, 3, 2.1, '2012-8-23'
select a.样本编号,
max(case when a.工序=1 then 参数 else -1 end) '工序1_参数',
max(case when a.工序=2 then 参数 else -1 end) '工序2_参数'
into Result
from Info a
inner join
(select 样本编号,工序,max(序号) md
from Info
group by 样本编号,工序) b
on a.样本编号=b.样本编号 and a.序号=b.md
group by a.样本编号select * from Result/*
样本编号 工序1_参数 工序2_参数
----------- ----------------- -------------------
1 1.6 2.3
2 1.2 1.6
3 1.4 1.2(3 row(s) affected)
*/
如果工序1_参数不为空,则序号值为工序1_参数对应的序号值,
如果工序1_参数为空,则序号值为工序2_参数对应的序号值,对应的SQL语句如何修改呀,
谢谢啦!序号 样本编号 工序1_参数 工序2_参数
----------- ----------------- -------------------
4 1 1.6 2.3
5 2 1.2 1.6
6 3 1.4 1.2
create table Info
(序号 int, 样本编号 int, 工序 int, 参数 decimal(5,1), 时间 date)insert into Info
select 1, 1, 1, 1.2, '2012-8-1' union all
select 2, 2, 1, 1.4, '2012-8-1' union all
select 3, 3, 1, 1.8, '2012-8-3' union all
select 4, 1, 1, 1.6, '2012-8-4' union all
select 5, 2, 1, 1.2, '2012-8-5' union all
select 6, 3, 1, 1.4, '2012-8-5' union all
select 7, 1, 2, 2.1, '2012-8-7' union all
select 8, 2, 2, 2.3, '2012-8-8' union all
select 9, 3, 2, 2.8, '2012-8-9' union all
select 10, 1, 2, 1.2, '2012-8-10' union all
select 11, 2, 2, 1.4, '2012-8-11' union all
select 12, 3, 2, 2.1, '2012-8-12' union all
select 13, 1, 2, 2.3, '2012-8-13' union all
select 14, 2, 2, 1.6, '2012-8-14' union all
select 15, 3, 2, 1.2, '2012-8-15' union all
select 16, 1, 3, 1.4, '2012-8-16' union all
select 17, 2, 3, 2.1, '2012-8-17' union all
select 18, 3, 3, 2.3, '2012-8-18' union all
select 19, 1, 3, 2.8, '2012-8-19' union all
select 20, 2, 3, 1.2, '2012-8-20' union all
select 21, 3, 3, 1.6, '2012-8-21' union all
select 22, 1, 3, 1.2, '2012-8-22' union all
select 23, 2, 3, 1.4, '2012-8-23' union all
select 24, 3, 3, 2.1, '2012-8-23'
select max(case when a.工序=1 then a.序号 else -1 end) '序号',
a.样本编号,
max(case when a.工序=1 then 参数 else -1 end) '工序1_参数',
max(case when a.工序=2 then 参数 else -1 end) '工序2_参数'
into Result
from Info a
inner join
(select 样本编号,工序,max(序号) md
from Info
group by 样本编号,工序) b
on a.样本编号=b.样本编号 and a.序号=b.md
group by a.样本编号select * from Result/*
序号 样本编号 工序1_参数 工序2_参数
----------- ----------- -------------- -------------
4 1 1.6 2.3
5 2 1.2 1.6
6 3 1.4 1.2(3 row(s) affected)
*/
create table Info
(序号 int, 样本编号 int, 工序 int, 参数 decimal(5,1), 时间 date)insert into Info
select 1, 1, 1, 1.2, '2012-8-1' union all
select 2, 2, 1, 1.4, '2012-8-1' union all
select 3, 3, 1, 1.8, '2012-8-3' union all
select 4, 1, 1, 1.6, '2012-8-4' union all
select 5, 2, 1, 1.2, '2012-8-5' union all
select 6, 3, 1, 1.4, '2012-8-5' union all
select 7, 1, 2, 2.1, '2012-8-7' union all
select 8, 2, 2, 2.3, '2012-8-8' union all
select 9, 3, 2, 2.8, '2012-8-9' union all
select 10, 1, 2, 1.2, '2012-8-10' union all
select 11, 2, 2, 1.4, '2012-8-11' union all
select 12, 3, 2, 2.1, '2012-8-12' union all
select 13, 1, 2, 2.3, '2012-8-13' union all
select 14, 2, 2, 1.6, '2012-8-14' union all
select 15, 3, 2, 1.2, '2012-8-15' union all
select 16, 1, 3, 1.4, '2012-8-16' union all
select 17, 2, 3, 2.1, '2012-8-17' union all
select 18, 3, 3, 2.3, '2012-8-18' union all
select 19, 1, 3, 2.8, '2012-8-19' union all
select 20, 2, 3, 1.2, '2012-8-20' union all
select 21, 3, 3, 1.6, '2012-8-21' union all
select 22, 1, 3, 1.2, '2012-8-22' union all
select 23, 2, 3, 1.4, '2012-8-23' union all
select 24, 3, 3, 2.1, '2012-8-23'
select isnull(max(case when a.工序=1 then a.序号 else null end),
max(case when a.工序=2 then a.序号 else null end)) '序号',
a.样本编号,
max(case when a.工序=1 then 参数 else -1 end) '工序1_参数',
max(case when a.工序=2 then 参数 else -1 end) '工序2_参数'
into Result
from Info a
inner join
(select 样本编号,工序,max(序号) md
from Info
group by 样本编号,工序) b
on a.样本编号=b.样本编号 and a.序号=b.md
group by a.样本编号select * from Result/*
序号 样本编号 工序1_参数 工序2_参数
----------- ----------- -------------- -------------
4 1 1.6 2.3
5 2 1.2 1.6
6 3 1.4 1.2(3 row(s) affected)
*/