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;
作者: JaminYe
版权声明:本文原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。