关于几家厂家价格选择的处理语句

create table tmp_lzw_db(rn int,amt1 numeric(10,2),amt2 numeric(10,2),amt3 numeric(10,2),amt4 numeric(10,2), amt5 numeric(10,2),amt6 numeric(10,2), amt7 numeric(10,2),amt8 numeric(10,2)) drop table tmp_lzw_db purge; select distinct rn From tmp_lzw_db where amt1=280 drop table tmp_lzw_test001 purge; create table tmp_lzw_test001 as select rn,amt1 amt,1 type from tmp_lzw_db; select * From tmp_lzw_test001 insert into tmp_lzw_test001 select rn,amt8,8 from tmp_lzw_db; drop table tmp_lzw_test002 purge create table tmp_lzw_test002 as select a.rn,min(amt) amt from (select rn from tmp_lzw_db) a left join tmp_lzw_test001 b on a.rn=b.rn and b.amt>0 group by a.rn; drop table tmp_lzw_test003 purge; create table tmp_lzw_test003 as select a.rn,a.amt,b.type from tmp_lzw_test002 a,tmp_lzw_test001 b where a.rn=b.rn and a.amt=b.amt; select count(1) from tmp_lzw_test005;

-------------------------------------------------------------- --行列互转 drop table tmp_lzw_test004 purge; create table tmp_lzw_test004 as select RN,AMT,type,ROW_NUMBER() OVER(partition by RN order by TYPE) RN2 FROM tmp_lzw_test003; drop table tmp_lzw_test005 purge; CREATE TABLE tmp_lzw_test005 AS SELECT A.RN,A.AMT,B1.TYPE TYPE1,B2.TYPE TYPE2,B3.TYPE TYPE3,B4.TYPE TYPE4,B5.TYPE TYPE5, B6.TYPE TYPE6,B7.TYPE TYPE7,B8.TYPE TYPE8 FROM (SELECT DISTINCT RN,AMT FROM tmp_lzw_test003) A LEFT JOIN tmp_lzw_test004 B1 ON A.RN=B1.RN AND B1.RN2=1 LEFT JOIN tmp_lzw_test004 B2 ON A.RN=B2.RN AND B2.RN2=2 LEFT JOIN tmp_lzw_test004 B3 ON A.RN=B3.RN AND B3.RN2=3 LEFT JOIN tmp_lzw_test004 B4 ON A.RN=B4.RN AND B4.RN2=4 LEFT JOIN tmp_lzw_test004 B5 ON A.RN=B5.RN AND B5.RN2=5 LEFT JOIN tmp_lzw_test004 B6 ON A.RN=B6.RN AND B6.RN2=6 LEFT JOIN tmp_lzw_test004 B7 ON A.RN=B7.RN AND B7.RN2=7 LEFT JOIN tmp_lzw_test004 B8 ON A.RN=B8.RN AND B8.RN2=8; SELECT RN,AMT,TYPE1||','||TYPE2||','||TYPE3||','||TYPE4||','||TYPE5||','||TYPE6||','||TYPE7||','||TYPE8 FROM tmp_lzw_test005; select * from tmp_lzw_db where rn=1;