mysql 複雜查詢

標籤：rom   div   group   查詢   nio   mount   pre   apply   products   

 

 

-- (select null, product_id, product_name, count(op.number ) as number,-- sum(total_amount + point_total_amount/10) as total_amount,-- sum(point_total_amount) as point_total_amount  -- from -- -- (select * from orders where (status = ‘completed‘ and (refund_status in (‘refunded‘,‘not_apply‘))) or refund_status = ‘refunded‘) as o-- -- left join order_products as op on op.order_id = o.id -- left join users as u on u.id = o.user_id-- -- -- group by product_id-- -- o.user_id-- -- group by user_id-- order by product_id-- )

 

(select order1.user_id as user_id, product_id, product_name,count(op.number ) as number, sum(total_amount + convert( point_total_amount/10, decimal)) as total_amount,sum(point_total_amount) as point_total_amount  from (select * from orders where (status = ‘completed‘ and (refund_status in (‘refunded‘,‘not_apply‘))) or refund_status = ‘refunded‘) as order1left join order_products as op on op.order_id = order1.id left join users as u on u.id = order1.user_idgroup by user_id,product_idorder by user_id)UNION Allselect null,user_id, realname, IFNULL(null,‘合計‘)  ,count(*) as number, sum(total_amount) as total_amount from(select order1.user_id as user_id, product_id, product_name,count(op.number ) as number, sum(total_amount + convert( point_total_amount/10, decimal)) as total_amount,sum(point_total_amount) as point_total_amount  from (select * from orders where (status = ‘completed‘ and (refund_status in (‘refunded‘,‘not_apply‘))) or refund_status = ‘refunded‘) as order1left join order_products as op on op.order_id = order1.id group by user_id,product_idorder by user_id) as newleft join users as u on u.id = new.user_idGROUP BY new.user_id

 

mysql 複雜查詢