几种常用sql记录-优快云博客

本文介绍了多种SQL查询技巧，包括按月分组统计、行列转换、关联表更新、数据校验及分页查询等实用方法，适用于数据库管理和数据分析场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[b]日期按月分组[/b]


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

[b]行转列：[/b]


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

[b]关联更新：[/b]


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

[b]统计个数：[/b]


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

[b]快速校验数据SQL正确性：[/b]


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

[b]添加分页查询：[/b]


      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