• mysql一些使用心得


    • 单个包含可以使用 LOCATE(要查找的字符串,字段)
    • 多个可以使用 字段 REGEXP '字符串1|字符串2|....'
    • 替换函数 REPLACE(字段,原字符串,新字符串)
    • group_concat会被截取
      SET SESSION group_concat_max_len=5120;
      SET GLOBAL group_concat_max_len=5120;
      group_concat_max_len = 5120
    SELECT  
      temp. product, complaint_Content  AS  complaintContent, itemType, yy, COUNT( DATE_SUB( acceptance_Time, INTERVAL  -8  HOUR) = '2021-6-16' 
      OR  NULL) AS  dayCount, 
      COUNT( YEARWEEK( DATE_SUB( acceptance_Time, INTERVAL  -8  HOUR), 
      1) = YEARWEEK( '2021-6-16', 
      1) 
      OR  NULL) AS  weekCount, gy, IF( COUNT( tl. id) > 0, '1', 
      '0'
    ) as  press
     
    FROM  
      (
    SELECT  
      product, acceptance_Time, complaint_Content, itemType, 
      IFNULL( GROUP_CONCAT( yy. yy
     
    ORDER BY  
      yy. yy  DESC  SEPARATOR  ''
    ), 
    ''
    ) AS  yy, 
    IFNULL( GROUP_CONCAT( gy. gy
     
    ORDER BY  
      gy. gy  DESC  SEPARATOR  ''
    ), 
    ''
    ) AS  gy  
    FROM  
      tb_c_complaint  c
     
      LEFT JOIN  (
    SELECT  
      swift_number, 
      CONCAT_WS( ' ', 
      create_Date, add_comment, u. chinese_name) AS  gy
     
    FROM  
      tb_c_fun_reason  tr
     
      LEFT JOIN  tb_m_user  u
     ON  u. user_Id  = tr. user_Id
    ) gy
     ON  gy. swift_number  = c. swift_Number
     
    LEFT JOIN  (
    SELECT  
      swift_number, 
      CONCAT_WS( ' ', 
      create_Date, add_comment, u. chinese_name) AS  yy
     
    FROM  
      tb_c_reason  tr
     
      LEFT JOIN  tb_m_user  u
     ON  u. user_Id  = tr. user_Id
    ) yy
     ON  c. swift_number  = yy. swift_number
     
    WHERE  
      product  IN  (
    SELECT  
      group_name
     
    FROM  
      tb_b_fn_group_mem
     
    WHERE  
      group_type  = 'yw' 
      AND  depart_id  = 0
    ) 
    AND  acceptance_Time  BETWEEN  DATE_SUB( SUBDATE( '2021-6-16', 
    IF( DATE_FORMAT( '2021-6-16', 
    '%w'
    )= 0, 7, DATE_FORMAT( '2021-6-16', 
    '%w'
    )
    )-1), 
    INTERVAL  8  HOUR) 
    AND  DATE_SUB( SUBDATE( '2021-6-16', 
    IF( DATE_FORMAT( '2021-6-16', 
    '%w'
    )= 0, 7, DATE_FORMAT( '2021-6-16', 
    '%w'
    )
    )-7), 
    INTERVAL  -16  HOUR) 
    GROUP BY  
      c. swift_Number
     
    ORDER BY  
      acceptance_Time  DESC
    ) temp
     
    LEFT JOIN  tb_b_press_log  tl  on  tl. item_type  = temp. itemType  
    AND  tl. press_time> DATE_ADD( CURDATE(
    ), 
    INTERVAL  - DAY( CURDATE(
    )
    )+ 1  DAY) 
    AND  (ac_msg!= '' 
    OR  st_msg!= '' 
    OR  cbc_msg!= ''
    ) 
    WHERE  
      1= 1  
    GROUP BY  
      temp. itemType;
    
  • 相关阅读:
    Ubuntu 16 安装ElasticSearch
    二叉树
    归并排序
    快速排序
    Git、Github使用
    445. 两数相加 II
    141. 环形链表
    92. 反转链表 II
    19. 删除链表的倒数第N个节点
    2. 两数相加
  • 原文地址:https://www.cnblogs.com/JaminYe/p/16095855.html
Copyright © 2020-2023  润新知