列转行的技巧：求租户六个月中的最高支付金额
有一个表的数据是这样的：
SQL> select * from tb_halfyear where rownum<20; USERID PAY1 PAY2 PAY3 PAY4 PAY5 PAY6 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 58972 28212 1861 10061 18060 23402 6482 15400 3643 10652 7258 20062 4009 17374 77468 25930 11279 15535 17379 4905 18634 13111 20085 15247 19790 22442 8886 22813 99274 17378 11625 11013 1468 9519 7945 54844 14820 25413 2777 24721 22930 14547 3724 26339 15024 28935 25647 8447 4697 30829 10373 17209 26303 12700 12108 12177 32982 4617 25208 24179 18292 4676 26188 68572 26515 26073 23818 10484 7451 14882 92319 17247 4809 4059 14611 2759 11173 25825 29283 18142 18066 12155 4519 23635 26640 11650 18506 4590 13568 6048 27161 54964 26773 28542 13488 11044 17087 7363 51879 6300 7184 1100 29654 1832 8114 93611 20111 19418 24163 2242 10397 6280 20660 24278 6772 25644 18662 1077 26924 89533 25974 6244 28506 19444 16181 22237 70409 16488 20378 11479 1262 25172 2684
userid是租户的id号，pay1~pay6则是半年来租户缴纳的金额。
明显这是一个中式表格，我们需要将其转化为真正的表数据。
先创建一个表格：
create table tb_halfyearflow( id number(8,0) primary key, userid number(8,0) not null, pay number(5,0) not null, month number(3,0) not null )
然后插值：
insert into tb_halfyearflow(id,userid,pay,month) select rownum,userid,pay1,1 from tb_halfyear; insert into tb_halfyearflow(id,userid,pay,month) select 100000+rownum,userid,pay2,2 from tb_halfyear; insert into tb_halfyearflow(id,userid,pay,month) select 200000+rownum,userid,pay3,3 from tb_halfyear; insert into tb_halfyearflow(id,userid,pay,month) select 300000+rownum,userid,pay4,4 from tb_halfyear; insert into tb_halfyearflow(id,userid,pay,month) select 400000+rownum,userid,pay5,5 from tb_halfyear; insert into tb_halfyearflow(id,userid,pay,month) select 500000+rownum,userid,pay6,6 from tb_halfyear;
插值后，tb_halfyearflow就是纯粹的流水表了，求每个租户单月最高缴纳金额就方便了：
select userid,max(pay) from tb_halfyearflow group by userid order by userid
让我们看看id从1~9的用户最高缴纳了多少：
SQL> select * from ( 2 select userid,max(pay) from tb_halfyearflow group by userid order by userid 3 ) 4 where rownum<10; USERID MAX(PAY) ---------- ---------- 1 24931 2 27306 3 25578 4 27853 5 29227 6 26800 7 23628 8 22326 9 26010 已选择9行。
再看看userid=2的租户其它消费情况：
SQL> select * from tb_halfyearflow where userid=2 order by month; ID USERID PAY MONTH ---------- ---------- ---------- ---------- 50656 2 27050 1 150656 2 19124 2 250656 2 6690 3 350656 2 27306 4 450656 2 5520 5 550656 2 20530 6 已选择6行。
果然是27306最高。
而下面这个代码能直接输出用户2的最高消费金额和消费月：
select a.userid,a.pay,a.month from tb_halfyearflow a right join ( select * from ( select userid,max(pay) as maxpay from tb_halfyearflow group by userid order by userid ) tb where tb.userid=2 ) b on a.userid=b.userid and a.pay=b.maxpay order by a.userid desc
SQL> select a.userid,a.pay,a.month from tb_halfyearflow a 2 right join ( 3 select * from ( 4 select userid,max(pay) as maxpay from tb_halfyearflow group by userid order by userid 5 ) tb 6 where tb.userid=2 ) b 7 on a.userid=b.userid and a.pay=b.maxpay 8 order by a.userid desc; USERID PAY MONTH ---------- ---------- ---------- 2 27306 4
也可以换一种写法：
SQL> select a.userid,a.pay,a.month from ( 2 select * from ( 3 select userid,max(pay) as maxpay from tb_halfyearflow group by userid order by userid 4 ) tb 5 where tb.userid=2 ) b left join tb_halfyearflow a 6 on a.userid=b.userid and a.pay=b.maxpay; USERID PAY MONTH ---------- ---------- ---------- 2 27306 4
好了，到此完成需求。
--2020年2月16日--
给tb_halfyear建表：
create table tb_halfyear( userid number(8,0) primary key, pay1 number(5,0), pay2 number(5,0), pay3 number(5,0), pay4 number(5,0), pay5 number(5,0), pay6 number(5,0) )
给tb_halfyear充值：
insert into tb_halfyear select rownum, dbms_random.value(1000,30000), dbms_random.value(1000,30000), dbms_random.value(1000,30000), dbms_random.value(1000,30000), dbms_random.value(1000,30000), dbms_random.value(1000,30000) from dual connect by level<=100000 order by dbms_random.random
--END--
