关于SQL中竖表边横表

横表的结构

select

m.USER_ID,m.USER_NAME,m.ACCOUNT_ID,m.PORTRAIT,

s.SCORE,c.coupon_value_a,c.coupon_value_b

from member m left join score_member s on m.USER_ID = s.USER_ID

left join (

select

sum(case when a.coupon_type = 0 then a.coupon_value else 0 end) as coupon_value_a ,

sum(case when a.coupon_type = 1 then a.coupon_value else 0 end) as coupon_value_b,

b.BELONG_USER

from coupon a LEFT JOIN coupon_sn b on a.coupon_id = b.COUPON_ID

where a.END_TIME > SYSDATE() group by b.BELONG_USER


)c on m.USER_ID =c.BELONG_USER where m.USER_ID =3

;

用sum 来讲横表改为竖表

select sum(a.coupon_value) as coupon_value,a.COUPON_TYPE as COUPON_TYPE ,b.BELONG_USER

from coupon a,coupon_sn b

where a.coupon_id = b.coupon_id

and a.END_TIME > SYSDATE()

GROUP BY a.COUPON_TYPE,b.BELONG_USER;