求一SQL转换

SQL code

INSERT INTO tab 
SELECT 1,100.00,2 UNION
SELECT 2,10.00,3 UNION
SELECT 3,200.00,2 UNION
SELECT 4,20.00,3 UNION
SELECT 5,2000.00,2 UNION
SELECT 6,100.00,3 
/*
tab_type=2,tab_amount为本金;tab_type=3,tab_amount为利息
结果：
tab_id          本金          利息          tab_type
1                 100.00      10.00        2           
3                 200.00      20.00        2            
5                 2000.00    100.00      2            
*/

SQL code

select tabl_id,tab_amount as tab_benjin,(select tab_amount from tab where id = (select min(id) from tab where tab_type = 3 and id > t.id)) as tab_lixi
from tb t
where tab_type = 2

SQL code

create table tab(id int,je decimal(10,2),type int)
INSERT INTO tab 
SELECT 1,100.00,2 UNION
SELECT 2,10.00,3 UNION
SELECT 3,200.00,2 UNION
SELECT 4,20.00,3 UNION
SELECT 5,2000.00,2 UNION
SELECT 6,100.00,3 
go
select a.id as tab_id,a.je as 本金,b.je as 利息,a.type as tab_type
from tab a inner join tab b on a.id=b.id-1 and a.type=2
go
drop table tab
/*
tab_id      本金                                      利息                                      tab_type
----------- --------------------------------------- --------------------------------------- -----------
1           100.00                                  10.00                                   2
3           200.00                                  20.00                                   2
5           2000.00                                 100.00                                  2

(3 行受影响)

*/

SQL code

CREATE TABLE tab
(
    tab_id int,
    tab_amount decimal(18,2),
    tab_type int
)

SQL code

select
   tab_id,
   max(case tab_type when 2 then tab_amount else 0 end) as '本金',
   max(case tab_type when 3 then tab_amount else 0 end) as '利息',
   tab_type 
from
   tb
group by
   tab_id,tab_type

SQL code

select tab_id,
sum(case when tab_type=2 then tab_amount else 0 end) as 本金,
sum(case when tab_type=3 then tab_amount else 0 end) as 利息
from tb
group by tab_id

后面那个tab_type是怎么来的？

SQL code

with cte as
(
    select *,rn = row_number() over (partition by tab_type order by id)
    from tab
)

select a.id,a.tab_amount as benjin,b.tab_amount as lixi
from cte a left join cte b on a.rn = b.rn

SQL code

create table tab(no int,mon numeric(18,2),ty int)
INSERT INTO tab 
SELECT 1,100.00,2 UNION
SELECT 2,10.00,3 UNION
SELECT 3,200.00,2 UNION
SELECT 4,20.00,3 UNION
SELECT 5,2000.00,2 UNION
SELECT 6,100.00,3 


select a.no,a.mon as 本金 ,b.mon as 利息,a.ty
from (select * from tab where ty=2)a join 
(select * from tab where ty=3) b on a.no=b.no-1

/*
no          本金                                      利息                                      ty
----------- --------------------------------------- --------------------------------------- -----------
1           100.00                                  10.00                                   2
3           200.00                                  20.00                                   2
5           2000.00                                 100.00                                  2

SQL code

select tab_id,tab_amount as 本金,
    (select tab_amount from tab a 
                where a.tab_id=tab.tab_id+1) as 利息,
       tab_type
from tab where tab_type=2

select
  tab_id,
  max(case tab_type when 2 then tab_amount else 0 end) as '本金',
  max(case tab_type when 3 then tab_amount else 0 end) as '利息',
  tab_type 
from
  tb
group by
  tab_id,tab_type