不常见的sql查询

列转行

select t.客户姓名,sum(case when t.收款类型='首款' then t.金额 else 0 end as '首款'),sum(case when t.收款类型='尾款' then t.金额 else 0 end as '尾款') from table t
group by t.客户姓名

这段sql的意思 是 查询出所有客户收款信息 然后按客户分组    分组后  然后将这个客户的所有首款的金额放在一组 然后聚合 组成首款字段   所有尾款金额 放在一组 然后sum 聚合 组成尾款字段

最终查出 

统计的时候 或者 做运算的时候非常有用

 

 

行转列

如果需要行转列 可以使用union方式 比如我们上面这个表

select t.客户姓名,t.首款 from table t where type='首款'
union 
select t.客户姓名,t.尾款 from table t where type='尾款'

2个时间段搜索

     started                  `end_at` 
2022-11-15 02:39:18      2022-11-21 02:44:10


          搜索条件(可搜索表达式)                     sql条件表达式
1.开始时间11-15 结束时间11-16    started<'11-16'  and  end_at>'11-15'
2.开始时间11-14 结束时间11-16    started<'11-16'  and  end_at>'11-14'
3.开始时间11-16 结束时间11-21    started<'11-21'  and  end_at>'11-16'
4.开始时间11-15 结束时间11-21    started<'11-21'  and  end_at>'11-15'
5.开始时间11-16 结束时间11-18    started<'11-18'  and  end_at>'11-16'
5.开始时间11-21 结束时间11-21    started<'11-21'  and  end_at>'11-21'


    搜索条件(不可搜索表达式)                     sql条件表达式
6.开始时间11-13 结束时间11-14    started<'11-14'  and  end_at>'11-13'
7.开始时间11-22 结束时间11-23    started<'11-23'  and  end_at>'11-22'

explain SELECT um.`user_id`,
       um.`type`,
       SUM(TIMESTAMPDIFF(SECOND, GREATEST(um.`started_at`, '2022-11-21'), LEAST(IFNULL(um.`end_at`, NOW()), '2022-11-21 23:59:59'))) as duration_second
  FROM `user_state_duration_metric` um
 WHERE um.provider_id= 432
   AND um.started_at<= '2022-11-21 23:59:59'
   AND um.end_at>= '2022-11-21'
   AND um.`type`in('CHAT_OVERFLOW', 'CHAT_SET_BUSYNESS')
 GROUP BY um.`user_id`,
         um.`type`

 

 

1对多关系自定义字段同时满足搜索

搜索 勾选了指定条件 同时 选择了某个选项的工单

以下sql搜是不是出来的 如果改为OR 又不是同时满足 

 

 

 修改后sql

=2则表示 几个条件搜索

利用group+select 运算函数+ DISTINCT 进行统计

记录一下 虽然不是最优sql只是复杂统计的思路

SELECT ug.id,
       ug.`name`,
       COUNT(DISTINCT u.id) countUser,#总用户数数
       COUNT(DISTINCT CASE WHEN u.created_at>= '2022-10-12'
   AND u.created_at<= '2022-10-18 23:59:59' THEN u.id ELSE NULL END)  AS addUser, #期间新增用户
         COUNT(DISTINCT t.id)  countTicket, #总工单数
         COUNT(DISTINCT CASE WHEN t.created_at>= '2022-10-12'
   AND t.created_at<= '2022-10-18 23:59:59' THEN t.id ELSE NULL END) AS addTicket,#期间新增工单数
         COUNT(DISTINCT te.id) countEvaluate,#总参与评价数
         COUNT(DISTINCT te.suggestion, IF(te.suggestion= '', NULL, te.suggestion)) AS suggestionCount,#评分
         FORMAT(SUM(te.score)  /COUNT(DISTINCT te.id), 2) AS avgScore,#平均
         FORMAT(SUM(te.loyalty)  /COUNT(DISTINCT te.id), 2) AS avgLoyalty,
         FORMAT(SUM(te.recommend)  /COUNT(DISTINCT te.id), 2) AS avgRecommend
  FROM user_group ug
  LEFT JOIN `user` u ON ug.id= u.user_group_id #提前left
   AND u.type= 'customer'
   AND u.valid= 1
   AND u.deleted<> 1
   AND u.`status`= 1
  LEFT JOIN ticket t ON u.id= t.requester_id #提前left
   AND(t.deleted IS NULL
    OR t.deleted= 0)
   AND t.`status`!= 'suspended'
  LEFT JOIN ticket_evaluate te ON t.evaluate_id= te.id
   AND(te.system IS NULL
    OR te.system= 0)
 WHERE ug.provider_id= 7899
 GROUP BY ug.id
 ORDER BY ug.created_at DESC

 

 

 

 

巧用group by获取最新和最旧的一条数据

现在有2条时间先后的数据,mysql默认是根据id升序排序

select t.id,t.`ticket_id` ,t.`created_at`  from `ticket_time_axis` t
where `ticket_status` ='solved' and t.`ticket_id` =2619573

 

 

 

mysql group by 不加聚合函数默认会取第一条数据 就取到最老的一条

select t.id,t.`ticket_id` ,t.`created_at`  from `ticket_time_axis` t
where `ticket_status` ='solved' and t.`ticket_id` =2619573

 

如果要取最新的一条怎么办

很简单 排序后再group by

select * from (select t.id,t.`ticket_id` ,t.`created_at`  from `ticket_time_axis` t
where `ticket_status` ='solved' and t.`ticket_id` =2619573
order by `created_at` desc)tab
group by tab.`ticket_id`

 当然如果ticket_id只有一个还可以用以下方式取最新最旧

select t.id,t.`ticket_id` ,t.`created_at`  from `ticket_time_axis` t
where `ticket_status` ='solved' and t.`ticket_id` =2619573
order by `created_at`  desc 
limit 1

 

select t.id,t.`ticket_id` ,t.`created_at`  from `ticket_time_axis` t
where `ticket_status` ='solved' and t.`ticket_id` =2619573
order by `created_at`  asc 
limit 1

 针对left join 多条数据 去重求和

select SUM(DISTINCT CASE WHEN d.id THEN d.uv_count else 0 END) AS uv_count from question_metric_item_detail d
left join question_metric_item qi on d.question_id =qi.question_id

 新方案

SELECT id, updated_name, updated_time, batch_no, activity_record_no, before_value, remark, version, after_value, business_no, deleted, created_time, created_name, operation_type, business_type, business_item_no
FROM (
    SELECT id, updated_name, updated_time, batch_no, activity_record_no, before_value, remark, version, after_value, business_no, deleted, created_time, created_name, operation_type, business_type, business_item_no,
           ROW_NUMBER() OVER (PARTITION BY business_no ORDER BY created_time DESC) as rn
    FROM cold_chain_order_activity_record
    WHERE business_no IN ('SO1805333940672567041')
          AND business_type = 10
          AND operation_type = 20
          AND deleted = 1
) tab
WHERE rn = 1;