安装了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
分享到:
相关推荐
SQL查询经验分享SQL查询经验分享SQL查询经验分享
sql练习总结sql经验总结 sql练习总结sql经验总结
SQL优化经验总结34条
sql优化经验总结,还不错
oracle的SQL语句的一些经验总结,里边有很多大家和自己的东西。
里面有很多sql语句的使用技巧,对做数据库应用的朋友很有帮助
SQL SERVER实用经验技巧集,SQL SERVER实用经验技巧集,SQL SERVER实用经验技巧集,SQL SERVER实用经验技巧集,SQL SERVER实用经验技巧集
sql优化经验总结,共30多条,你都会么?
一些SQL开发的经验,真实的工作经验
SQL培训文档 SQL常用介绍 SQL经验积累 SQL基本介绍 SQL入门语法
sql server中 sql语句的一些调优经验。
SQL语句经验总结,包含了日常工作中易出错的地方,值得一看
sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送
sqlserver转PG经验总结及PG的一些特性,内容全面,适合作为参考资料
一些很好的经验总结一下,希望能给大家带来帮助
34条SQL语句的优化方案,让你轻松写出性能高效的SQL语句。
经典SQL语句集锦了SQL基础与高级语句的编写,是初学者学习SQL语句的最好途径,也适合有经验者的经验交流!
SQL数据库设计经验SQL数据库设计经验
SQL Server实用经验与技巧大汇集
SQLServer索引设计经验谈SQLServer索引设计经验谈