各位高手，请教下查询结果插入表中的问题
各位高手，请教下如何用SQL实现将两个查询结果插入到一个表中，需查询每个样本编号的最后一个工序的参数信息组合成一个表，请问如何实现，最好执行时间短一点的，因为数据量可能会很大,谢谢啦！
例如
表 Info
序号样本编号工序参数时间
1111.22012-8-1
2211.42012-8-1
3311.82012-8-3
4111.62012-8-4
5211.22012-8-5
6311.42012-8-5
7122.12012-8-7
8222.32012-8-8
9322.82012-8-9
10121.22012-8-10
11221.42012-8-11
12322.12012-8-12
13122.32012-8-13
14221.62012-8-14
15321.22012-8-15
16131.42012-8-16
17232.12012-8-17
18332.32012-8-18
19132.82012-8-19
20231.22012-8-20
21331.62012-8-21
22131.22012-8-22
23231.42012-8-23
24332.12012-8-23
结果
表 Result
样本编号工序1_参数工序2_参数
11.62.3
21.21.6
31.41.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)*/
[解决办法]
- 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 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 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)*/