笔记—》mysql语句随手记

-- 1，查询3天前的数据

SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 3 DAY),INTERVAL 0 HOUR)

--2，查询近七天的数据，若数据为空，以0填充

select v1.insurance_time ,ifnull(v2.incount,0) from  (
        SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 2 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 3 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 4 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 5 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 6 DAY),INTERVAL 0 HOUR) as insurance_time
    union all
    SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 7 DAY),INTERVAL 0 HOUR) as insurance_time
) v1  LEFT JOIN(
    SELECT insurance_time, incount from tablexxx
        GROUP BY insurance_time
) v2  on v1.insurance_time=v2.insurance_time

3，某个表的“消息”字段由逗号分隔组成，将逗号分隔一行数据分割成id相同，问题不同的多行数据（mysql.help_topic是mysql数据库下的原生表，非自建表）

SELECT a.id,SUBSTRING_INDEX(SUBSTRING_INDEX(a.fail_message,',',b.help_topic_id+1),',',-1) 
FROM
baoji_sendagainlog a
JOIN
mysql.help_topic b
ON b.help_topic_id < (LENGTH(a.fail_message) - LENGTH(REPLACE(a.fail_message,',',''))+1)
ORDER BY a.id; 
 

4，删除重复数据

场景：在一个表中， channel_id、data_pipe_id、owner_company_id、company_requirements_id、receive_media 这几个字段都含有重复的数据，我们将这些重复且相同的数据删掉，保留唯一一条。

（mysql的删除语句中的子查询如果涉及到被删除的表，需要使用中间表，oracle则不用）

delete FROM x_leads_count   WHERE  id IN ( SELECT b.id FROM
   (
    SELECT
     c.id
    FROM
     x_leads_count c
    WHERE
     c.insurance_time = '2021-01-12 00:00:00'
    GROUP BY
     c.channel_id,
     c.data_pipe_id,
     c.owner_company_id,
     c.company_requirements_id,
     c.receive_media
    HAVING
     count(c.id) > 1
   ) b
 );

5。统计本表数据及其比例

-- 查询本月每天的成功量及其比例
SELECT
    a.count_date '赠送日期',
  a.userCount '用户数',
  a.allPolicyCount '保单数',
  a.successPolicyCount '成功保单数',
  CONCAT(ROUND(a.successPolicyCount/a.allPolicyCount*100,3),'','%') '成功率'
FROM
    (
        SELECT
            count_date AS count_date,
            count(DISTINCT(mobile)) AS userCount,
            count(DISTINCT(mobile)) * 6 AS allPolicyCount,
            count(if(`status`='SUCCEEDED',TRUE,NULL)) AS successPolicyCount
         FROM
            temp_policy a
        WHERE
            count_date LIKE '2021-07%'
        GROUP BY
            count_date
    ) a;

6 时间相关

6.1：查询一年后的日期：下图中的now()可以换成 表中的日期字段

DATE_ADD(NOW() , INTERVAL b.policy_term YEAR)

6.2：计算年龄差： TIMESTAMPDIFF 函数

select  TIMESTAMPDIFF(YEAR,a.birthday, NOW()) from table a