本文介绍: 单个包含可以使用 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 = 5120SELECT temp. product, complaint_Content A.
- 单个包含可以使用 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;
原文地址:https://blog.csdn.net/weixin_43891731/article/details/123937150
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_37416.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。