sql 高性能 竖表转横表

SELECT null,s._room_name 包厢,a._session 账单号,COALESCE(s._amount_receivable,0)-COALESCE(s._flower_charge,0) 应收金额, a.amount 实收金额,a.xianjin 现金,a.bank 银行卡,a.qiandan 签单,a.zhaodai 招待,a.zhuguazhang 主挂账,a.fuguazhang 副挂账, s._value_discount 折率,_charged_by_name 埋单员工,_copy_count 打印次数, CASE s._check_out_mode WHEN 0 THEN '正常' WHEN 1 THEN '贵宾打折' WHEN 2 THEN '经理打折' WHEN 3 THEN '经理免单' WHEN 4 THEN '经理实收' WHEN 5 THEN '超级实收' WHEN 6 THEN '整单免单' END 买单方式 FROM ( SELECT srn._session,sum(srn._amount) amount, COALESCE(sum(CASE WHEN srn._type = '现金' AND srn._account_type IS NULL THEN srn._amount END),0) xianjin, COALESCE(sum(CASE WHEN srn._type = '银行卡' AND srn._account_type IS NULL THEN srn._amount END),0) bank, COALESCE(sum(CASE WHEN (srn._type = '经理挂账' OR srn._type = '员工挂账') AND srn._account_type IS NULL THEN srn._amount END),0) qiandan, COALESCE(sum(CASE WHEN (srn._type = '个人招待' OR srn._type = '公司招待') AND srn._account_type IS NULL THEN srn._amount END),0) zhaodai, COALESCE(sum(CASE WHEN srn._type = '会员挂账' AND srn._account_type = 0 THEN srn._amount END),0) zhuguazhang, COALESCE(sum(CASE WHEN srn._type = '会员挂账' AND srn._account_type <> 0 THEN srn._amount END),0) fuguazhang FROM _session_receipt_note srn WHERE 1=1 AND _type <> '免零' AND _handled_at >= '2012-08-01 08:00:00' AND _handled_at <'2012-09-12 08:00:00' AND (srn._purpose_detail = '酒水' OR srn._purpose_detail IS NULL) GROUP BY srn._session )a LEFT JOIN _session s ON s._id = a._session LEFT JOIN _room r ON r._id = s._room WHERE 1=1 AND s._status = 7