几种常用sql记录

日期按月分组

select count(id) as quo_count,date_format(created_at,'%Y-%m') as quo_month from inquiries where send_quotation_at is not null and created_at>='2017-01-10' group by quo_month

行转列：

select a.bd_id,sum(count_id) sum_ids,GROUP_CONCAT(a.reg_trader_start_at,"@",a.count_id) from (select count(id) as count_id,bd_id,DATE_FORMAT(trader_start_at,'%Y-%m') as reg_trader_start_at from companies where trader_start_at > "2017-01-01" group by reg_trader_start_at ,bd_id ) as a group by a.bd_id

关联更新：

update a join b on a.b_id=b.id  set a.aa=b.aa where b.cc = “1111”

统计个数：

SELECT co.trader_id as dealer_id, emp.trader_level ,companies.id as client_id, companies.frequency as buy_level ,
      count(*) as order_detail_total,
      SUM(CASE WHEN o_detail.detail_status!=7 THEN 1 END) as uncancel_order_total,
      SUM(CASE WHEN o_detail.ship_status=2 and o_detail.detail_status!=7 THEN 1 END) as effective_order_total
from order_details o_detail 
left join customer_orders co on co.id = o_detail.customer_order_id
left join companies on companies.id = co.company_id
left join employees emp on emp.id = co.trader_id
where o_detail.created_at >= "#{report_month}" and  o_detail.created_at < "#{end_month}" and co.trader_id is not null
group by client_id

快速校验数据SQL正确性：

      message="select o.* from ("+message+")o where 1=2" 

添加分页查询：

      message="SELECT * FROM (SELECT A.*, ROWNUM RN  FROM ("+message+")A
              WHERE ROWNUM <= #{(params[:page].to_i)*30})
              WHERE RN >= #{(params[:page].to_i-1)*30+1}" if params[:page]
      message