mysql笔记

MySQL笔记整理

查询一个字段值长度大于4的结果集

select *
from table_xxx where length(bank_name) > 4 ;

查询某个表是否有重复数据

select count(1) cu,
       sub_merchant_id
from table_xxx
group by sub_merchant_id
having cu > 1;

查询某个时间段两个时间的差值

SELECT TIMESTAMPDIFF(SECOND,r.create_date_time,r.complete_date_time),COUNT(1) 
FROM table_xxx r 
WHERE r.create_date_time >= '2020-03-16 21:30:00'
AND r.create_date_time >= '2020-03-16 21:40:00'
AND TIMESTAMPDIFF(SECOND,r.create_date_time,r.complete_date_time) > 600
ORDER BY TIMESTAMPDIFF(SECOND,r.create_date_time,r.complete_date_time) DESC

动态展示值

select if(s.status is null, 0, 1) as already
from table_xxx

多条结果拼接成一个字段展示

select GROUP_CONCAT(p.name SEPARATOR '|') as nameList
from t_profile p
where p.member_id = '10020098';

列表中查询多表的结果，作为列表中的一列

select 
       m.email,
       m.mobile,
       m.create_date_time,
      (select GROUP_CONCAT(p.name SEPARATOR ',') from t_profile p where p.member_id = m.member_id) as nameList,
from t_member m
order by m.create_date_time desc;

查询包含角色ID是31的用户

select u.email,
       u.username,
       (select GROUP_CONCAT(r.role_id SEPARATOR ',') from boss_sys_user_role r where r.user_id = u.id) as roleIds
from boss_sys_user u
where u.id in (select r.user_id from boss_sys_user_role r where r.role_id = 31)

case when 实现如果满足某个条件, 拼接另一个and条件

select t.*
from (
         select r.*,
                case when (r.trade_type ='REMIT') then r.fund_direction != 'receipt'
                     else r.fund_direction in ('receipt', 'refund') end as isShow
         from fc_fund_check_record r
     ) t
where t.isShow = 1

条件满足trade_type ='REMIT'， 则不显示 fund_direction != 'receipt' 条件的数据， 反之 显示r.fund_direction in ('receipt', 'refund') 的数据。

 统计每天每个身份证多少笔交易

select
       count(1) as 笔数,
       payer_id_card_no as 证件号,
       payer_name as 姓名,
       cast(payment_completed_date_time as date)as 日期
from transaction_order 
group by payer_id_card_no_md5, cast(payment_completed_date_time as date)
having count(1) > 10
order by cast(payment_completed_date_time as date), count(1) desc ;


select
       count(1) as 笔数,
       payer_id_card_no as 证件号,
       payer_name as 姓名,
       date_format(payment_completed_date_time,'%Y-%m-%d')as 日期
from transaction_order 
group by payer_id_card_no_md5, date_format(payment_completed_date_time,'%Y-%m-%d')
having count(1) > 10
order by date_format(payment_completed_date_time,'%Y-%m-%d'), count(1) desc;

coalesce函数

解释：返回参数中的第一个非空表达式（从左向右依次类推）

select coalesce(null,2,3); // Return 2

select coalesce(null,null,3); // Return 3

select coalesce(1,2,3); // Return 1