一些sql经验

安装了mysql5.7，用group by 查询时抛出如下异常：
https://www.cnblogs.com/jpfss/p/10401753.html

开发新客户和老客户激活的情况（下单客户中前90天没有下过单的客户）

select employees.name, companies.name_cn,
DATE_FORMAT(min(od.created_at),'%Y-%m-%d') as min_created_at,
count(case when od.created_at>= "#{@start_time}" then 1 else 0 end ) as count_order,
sum(case when od.created_at>= "#{@start_time}" then od.amount else 0 end ) as sum_amount
from order_details  as od
left join customer_orders as co
on co.id = od.customer_order_id
left join employees
on employees.id = co.trader_id
left join companies
on companies.id = od.company_id
where od.created_at >= date_sub("#{@start_time}",interval 3 month)  and detail_status != 7
#{@condition_end_time}
#{@condition_em_name}
group by od.company_id
having min_created_at >= "#{@start_time}"


业务线利润占比

select
            truncate( sum(source_data.c_amount),2) as sum_c_amount,
            truncate(
            sum(
                     (source_data.c_amount
            - source_data.v_amount
            - source_data.goods_service_fee
            - (case
                when (source_data.payment_type != 3 and source_data.order_type != 4) then 0
                when source_data.order_type = 4 then (source_data.c_amount * 0.05) / 1.05
                else source_data.customer_commision_fee end )
            - source_data.od_commision_fee
            ) * vm_rate * special_rate * reagent_rate)
            ,2) as real_gross
        from
        (select od.id,od.customer_order_no,co.payment_type,od.invoice_type,co.order_type,
          od.commision_fee as od_commision_fee ,od.customer_commision_fee,od.goods_service_fee,
         case when od.vendor_order_type in (1,4,12,13,14) then 0.5 else 1 end as vm_rate,
          case when co.special_type = 1 then 0.5 else 1 end as special_rate,
          case when companies.reagent = 1 then 0.9 else 1 end as reagent_rate,
          case  
               when  od.vendor_invoice_type = 2 && od.goods_ownership = 1 then od.vendor_amount * 1.16
               when  od.goods_ownership = 1 then od.vendor_amount
               when  od.vendor_invoice_type = 2 and od.previous_vendor_amount is not null and od.previous_vendor_amount != 0 and odd.previous_vendor_invoice_type = 2 then od.previous_vendor_amount * 1.16 + (od.vendor_invoice_amount - od.previous_vendor_amount)*1.16*0.3
               when  od.vendor_invoice_type = 2 and od.previous_vendor_amount is not null and od.previous_vendor_amount != 0 then od.previous_vendor_amount + (od.vendor_invoice_amount*1.16 - od.previous_vendor_amount)*0.3
               when  od.previous_vendor_amount is not null and od.previous_vendor_amount != 0 and odd.previous_vendor_invoice_type = 2 then od.previous_vendor_amount * 1.16 + (od.vendor_invoice_amount - od.previous_vendor_amount*1.16)*0.3
               when  od.previous_vendor_amount is not null and od.previous_vendor_amount != 0 then od.previous_vendor_amount + (od.vendor_invoice_amount - od.previous_vendor_amount)*0.3
               when  od.vendor_invoice_type = 2 then od.vendor_invoice_amount * 1.16
               else  od.vendor_invoice_amount
          end as v_amount,
        od.amount as c_amount,
        co.trader_id
        from order_details as od
        left join `customer_orders` as co on
        od.`customer_order_id` = co.id
        left join employees on
        employees.id = co.trader_id
        left join order_detail_deeps as odd
        on odd.order_detail_id = od.id
        left join companies
        on companies.id = co.company_id
        where
        od.`origin`!=6
        and companies.company_type = 3
        and od.received_at >= '#{start_time}'
        and od.received_at <= '#{end_time}'
        and od.received_amount >= od.amount
        and detail_status!=7 and od.vendor_amount !=0 and od.amount !=0 ) as source_data


一对多表关联sum1表uniq price

select
sum(count_order_items) as sum_order_items,
sum(sum_order_amount/count_order_items) as sum_amount,
sum(sum_fob_price),
sum(sum_vendor_price),
sum(sum_vendor_price),
sum(sum_profit),
sum(sum_profit) * 100 / sum(sum_order_amount) as profit_rate
from
(select count(order_items.id) as count_order_items,
sum(orders.amount) as sum_order_amount,
sum(order_items.fob_price) as sum_fob_price,
sum(order_items.vendor_price) as sum_vendor_price,
sum(order_items.profit) as sum_profit
from order_items
left join orders
on order_items.order_id = orders.id
where order_items.created_at >= "2018-10-01"
group by orders.id) as sount_data

－查找条件作为条件二次查询

select
week_data,
'Total' as bd_manager,
sum(count_order_items) as sum_order_items,
sum(sum_order_amount/count_order_items) as sum_amount,
sum(sum_fob_price) as sum_fob_price,
sum(sum_vendor_price) as sum_vendor_price,
sum(sum_profit) as sum_profit,
truncate( sum(sum_profit) * 100 / sum(sum_order_amount/count_order_items),2) as profit_rate,
(select sum(order_items.amount - order_items.received_amount) from order_items where UNIX_TIMESTAMP(order_items.created_at) <= UNIX_TIMESTAMP(last_date) and UNIX_TIMESTAMP(order_items.overdue_date) < UNIX_TIMESTAMP(last_date) and order_items.state != 1 ) as sum_overdue,
(select sum(order_items.amount - order_items.received_amount) from order_items where UNIX_TIMESTAMP(order_items.created_at) <= UNIX_TIMESTAMP(last_date) and (order_items.overdue_date is null or UNIX_TIMESTAMP(order_items.overdue_date) >= UNIX_TIMESTAMP(last_date) ) and order_items.state != 1 ) as sum_inoverdue,
(select sum(order_items.amount - order_items.received_amount) from order_items where UNIX_TIMESTAMP(order_items.created_at) <= UNIX_TIMESTAMP(last_date) and order_items.state != 1 ) as sum_received
from
(select
concat( date_sub(date_sub(date_format(DATE_FORMAT(order_items.created_at,'%Y-%m-%d'), '%y-%m-%d'),interval extract(day from DATE_FORMAT(order_items.created_at,'%Y-%m-%d')) - 1 day),interval 0 month), '  ' , last_day(order_items.created_at) ) as week_data,
last_day(order_items.created_at) as last_date,
count(order_items.id) as count_order_items,
sum(orders.amount) as sum_order_amount,
sum(order_items.fob_price) as sum_fob_price,
sum(order_items.vendor_price) as sum_vendor_price,
sum(order_items.profit) as sum_profit
from order_items
left join orders
on order_items.order_id = orders.id
where  order_items.state != 1
#{@condition_start_time}
#{@condition_end_time}
group by orders.id) as sount_data group by week_data


要的是每个月有几个定制产品（并且这类产品是以其没有成单过的）

select
customer_order_no,
amount,
chemicals.cas,
`package`,
package_unit ,
(select count(*) from order_details where chemical_id = od.chemical_id and order_details.id != od.id ) as count_uniq
from order_details as od
left join chemicals
on chemicals.id = od.`chemical_id`
where customization_type = 1 and od.detail_status != 7 and od.origin != 6
having count_uniq = 0