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;