原表：
id qi_hao dui_ying
1 2011001 8
2 2011001 9
3 2011002 5
4 2011002 6
将上表的数据取出，插入到新表是：
id qi_hao dui_one dui_two
1 2011001 8 9
2 2011002 5 6
这个sql语句怎么写
------解决方案--------------------
insert into new_table(qi_hao,dui_one,dui_two ) as
select qi_hao ,max(dui_ying),min(dui_ying) from your_table group by qi_hao
;
INSERT INTO的语法,视您具体的数据库,自己试一下.
------解决方案--------------------
试一下：
- SQL code
declare @t table([id] [int] IDENTITY (1, 1), qi_hao varchar(10), dui_one int, dui_two int)insert into @t(qi_hao, dui_one, dui_two)select qi_hao, min(dui_ying), max(dui_ying)from 原表group by qi_hao--select * from @t
------解决方案--------------------
用游标吧。
------解决方案--------------------
- C# code
string col[]string vs_sql_select,vs_sql_select1,vssql_UPDATEinteger i,jstring vs_qi_hao,vs_tempcol[1] = "dui_one"col[2] = "dui_two"col[3] = "dui_three"DECLARE dml Dynamic Cursor FOR sqlsa;DECLARE dml1 Dynamic Cursor FOR sqlsa;vs_sql_select = "SELECT qi_hao FROM 老表 GROUP BY qi_hao "PREPARE sqlsa From :vs_sql_select Using sqlca;OPEN Dynamic dml;i = 1DO WHILE sqlca.SQLCode = 0 i = i+1 vs_qi_hao = "" FETCH dml INTO :vs_qi_hao; if vs_qi_hao = "" then continue //INSERT Into 新表 (id,qi_hao) Values(:i,:vs_qi_hao) ; j = 1 vs_sql_select1 = "SELECT dui_ying FROM 老表 WHERE qi_hao = '"+vs_qi_hao+"' " PREPARE sqlsa From :vs_sql_select1 Using sqlca; OPEN Dynamic dml1; DO WHILE sqlca.SQLCode = 0 j ++ FETCH dml INTO :vs_temp; vssql_UPDATE = "UPDATE 新表 SET "+col[j]+" = '"+vs_temp+"' WHERE qi_hao = '"+vs_qi_hao+"' " EXECUTE Immediate :vssql_UPDATE Using sqlca; LOOPLOOP