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 -- )

 

(<span style="color: #0000ff">select</span><span style="color: #000000"> 
order1.</span><span style="color: #ff00ff">user_id</span> <span style="color: #0000ff">as</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">, 
product_id, 
product_name,
</span><span style="color: #ff00ff">count</span>(op.<span style="color: #0000ff">number</span> ) <span style="color: #0000ff">as</span> <span style="color: #0000ff">number</span><span style="color: #000000">, 
</span><span style="color: #ff00ff">sum</span>(total_amount <span style="color: #808080">+</span> <span style="color: #ff00ff">convert</span>( point_total_amount<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">10</span>, <span style="color: #0000ff">decimal</span>)) <span style="color: #0000ff">as</span><span style="color: #000000"> total_amount,
</span><span style="color: #ff00ff">sum</span>(point_total_amount) <span style="color: #0000ff">as</span><span style="color: #000000"> point_total_amount  
</span><span style="color: #0000ff">from</span><span style="color: #000000"> 
(</span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> orders <span style="color: #0000ff">where</span> (status <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">completed</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> (refund_status <span style="color: #808080">in</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">refunded</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">not_apply</span><span style="color: #ff0000">‘</span>))) <span style="color: #808080">or</span> refund_status <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">refunded</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">as</span><span style="color: #000000"> order1
</span><span style="color: #808080">left</span> <span style="color: #808080">join</span> order_products <span style="color: #0000ff">as</span> op <span style="color: #0000ff">on</span> op.order_id <span style="color: #808080">=</span><span style="color: #000000"> order1.id 
</span><span style="color: #808080">left</span> <span style="color: #808080">join</span> users <span style="color: #0000ff">as</span> u <span style="color: #0000ff">on</span> u.id <span style="color: #808080">=</span> order1.<span style="color: #ff00ff">user_id</span>
<span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">,product_id
</span><span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">
)
</span><span style="color: #0000ff">UNION</span> <span style="color: #808080">All</span>
<span style="color: #0000ff">select</span> <span style="color: #0000ff">null</span>,<span style="color: #ff00ff">user_id</span>, realname, IFNULL(<span style="color: #0000ff">null</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">合计</span><span style="color: #ff0000">‘</span>)  ,<span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">as</span> <span style="color: #0000ff">number</span>, <span style="color: #ff00ff">sum</span>(total_amount) <span style="color: #0000ff">as</span><span style="color: #000000"> total_amount 
</span><span style="color: #0000ff">from</span><span style="color: #000000">
(
</span><span style="color: #0000ff">select</span><span style="color: #000000"> 
order1.</span><span style="color: #ff00ff">user_id</span> <span style="color: #0000ff">as</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">, 
product_id, 
product_name,
</span><span style="color: #ff00ff">count</span>(op.<span style="color: #0000ff">number</span> ) <span style="color: #0000ff">as</span> <span style="color: #0000ff">number</span><span style="color: #000000">, 
</span><span style="color: #ff00ff">sum</span>(total_amount <span style="color: #808080">+</span> <span style="color: #ff00ff">convert</span>( point_total_amount<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">10</span>, <span style="color: #0000ff">decimal</span>)) <span style="color: #0000ff">as</span><span style="color: #000000"> total_amount,
</span><span style="color: #ff00ff">sum</span>(point_total_amount) <span style="color: #0000ff">as</span><span style="color: #000000"> point_total_amount  
</span><span style="color: #0000ff">from</span><span style="color: #000000"> 
(</span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> orders <span style="color: #0000ff">where</span> (status <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">completed</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> (refund_status <span style="color: #808080">in</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">refunded</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">not_apply</span><span style="color: #ff0000">‘</span>))) <span style="color: #808080">or</span> refund_status <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">refunded</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">as</span><span style="color: #000000"> order1
</span><span style="color: #808080">left</span> <span style="color: #808080">join</span> order_products <span style="color: #0000ff">as</span> op <span style="color: #0000ff">on</span> op.order_id <span style="color: #808080">=</span><span style="color: #000000"> order1.id 
</span><span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">,product_id
</span><span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">
) </span><span style="color: #0000ff">as</span><span style="color: #000000"> new
</span><span style="color: #808080">left</span> <span style="color: #808080">join</span> users <span style="color: #0000ff">as</span> u <span style="color: #0000ff">on</span> u.id <span style="color: #808080">=</span> new.<span style="color: #ff00ff">user_id</span>
<span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> new.<span style="color: #ff00ff">user_id</span>

 

mysql 复杂查询

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