mysql 复杂查询

, 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 order1
left join order_products as op on op.order_id = order1.id 
left join users as u on u.id = order1.user_id
group by user_id,product_id
order by user_id
)

UNION All

select 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 order1

left join order_products as op on op.order_id = order1.id 

group by user_id,product_id
order by user_id
) as new
left join users as u on u.id = new.user_id
GROUP BY new.user_id

 

mysql 复杂查询

标签：rom   div   group   查询   nio   mount   pre   apply   products