不常见的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
