各位高手，请教下如何用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
------解决方案--------------------
- SQL code
create table Info(序号 int, 样本编号 int, 工序 int, 参数 decimal(5,1), 时间 date)insert into Infoselect 1, 1, 1, 1.2, '2012-8-1' union allselect 2, 2, 1, 1.4, '2012-8-1' union allselect 3, 3, 1, 1.8, '2012-8-3' union allselect 4, 1, 1, 1.6, '2012-8-4' union allselect 5, 2, 1, 1.2, '2012-8-5' union allselect 6, 3, 1, 1.4, '2012-8-5' union allselect 7, 1, 2, 2.1, '2012-8-7' union allselect 8, 2, 2, 2.3, '2012-8-8' union allselect 9, 3, 2, 2.8, '2012-8-9' union allselect 10, 1, 2, 1.2, '2012-8-10' union allselect 11, 2, 2, 1.4, '2012-8-11' union allselect 12, 3, 2, 2.1, '2012-8-12' union allselect 13, 1, 2, 2.3, '2012-8-13' union allselect 14, 2, 2, 1.6, '2012-8-14' union allselect 15, 3, 2, 1.2, '2012-8-15' union allselect 16, 1, 3, 1.4, '2012-8-16' union allselect 17, 2, 3, 2.1, '2012-8-17' union allselect 18, 3, 3, 2.3, '2012-8-18' union allselect 19, 1, 3, 2.8, '2012-8-19' union allselect 20, 2, 3, 1.2, '2012-8-20' union allselect 21, 3, 3, 1.6, '2012-8-21' union allselect 22, 1, 3, 1.2, '2012-8-22' union allselect 23, 2, 3, 1.4, '2012-8-23' union allselect 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 ainner join(select 样本编号,工序,max(序号) md from Info group by 样本编号,工序) bon a.样本编号=b.样本编号 and a.序号=b.mdgroup 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)*/
------解决方案--------------------
- SQL code
create table Info(序号 int, 样本编号 int, 工序 int, 参数 decimal(5,1), 时间 date)insert into Infoselect 1, 1, 1, 1.2, '2012-8-1' union allselect 2, 2, 1, 1.4, '2012-8-1' union allselect 3, 3, 1, 1.8, '2012-8-3' union allselect 4, 1, 1, 1.6, '2012-8-4' union allselect 5, 2, 1, 1.2, '2012-8-5' union allselect 6, 3, 1, 1.4, '2012-8-5' union allselect 7, 1, 2, 2.1, '2012-8-7' union allselect 8, 2, 2, 2.3, '2012-8-8' union allselect 9, 3, 2, 2.8, '2012-8-9' union allselect 10, 1, 2, 1.2, '2012-8-10' union allselect 11, 2, 2, 1.4, '2012-8-11' union allselect 12, 3, 2, 2.1, '2012-8-12' union allselect 13, 1, 2, 2.3, '2012-8-13' union allselect 14, 2, 2, 1.6, '2012-8-14' union allselect 15, 3, 2, 1.2, '2012-8-15' union allselect 16, 1, 3, 1.4, '2012-8-16' union allselect 17, 2, 3, 2.1, '2012-8-17' union allselect 18, 3, 3, 2.3, '2012-8-18' union allselect 19, 1, 3, 2.8, '2012-8-19' union allselect 20, 2, 3, 1.2, '2012-8-20' union allselect 21, 3, 3, 1.6, '2012-8-21' union allselect 22, 1, 3, 1.2, '2012-8-22' union allselect 23, 2, 3, 1.4, '2012-8-23' union allselect 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 ainner join(select 样本编号,工序,max(序号) md from Info group by 样本编号,工序) bon a.样本编号=b.样本编号 and a.序号=b.mdgroup by a.样本编号select * from Result/*序号 样本编号 工序1_参数 工序2_参数----------- ----------- -------------- -------------4 1 1.6 2.35 2 1.2 1.66 3 1.4 1.2(3 row(s) affected)*/