一、多个模糊条件查询 regexp
1、group_concat 连接 distinct 去重 SEPARATOR ’,‘ 逗号分割
select FIELD_NAME 字段中文名,GROUP_CONCAT(DISTINCT FIELD_CODE SEPARATOR ‘,’) 字段英文名 from da_dp_table_field where FIELD_NAME regexp ‘用户编码|客户编码’ and DEL_FLAG=0 group by FIELD_NAME;
2、if(type=‘’,1,null)
select crt_user,crt_name,count(IF(type =‘更新’,1, null)) ‘更新’,
count(IF(type =‘订阅’,1, null)) ‘订阅’,count(IF(type =‘上架’,1, null)) ‘上架’,
count(IF(type =‘注册’,1, null)) ‘注册’ from da_assets_operation_log_book where tenant_id in (‘’) and create_timeBETWEEN ‘2023-10-08 23:59:59’ AND ‘2023-10-16 23:59:59’ group by tenant_id ;
if(条件，满足，不满足)
SELECT
t2.TENANT_ID as tenantId,
t4.TITLE as title,
t6.DATA_CODE as dataCode,
t4.ASSETS_ID as assetsId,
t4.ASSETS_STAGE
from da_assets t2
inner join da_dp_field t1 on t1.ID = t2.TABLE_OID AND t2.DEL_FLAG = ‘0’ AND t1.DEL_FLAG = ‘0’
inner join (SELECT IF (ISNULL( t1.ASSETS_VERSION_ID ),( SELECT tt.ID FROM da_assets_version tt WHERE t1.ID = tt.ASSETS_ID AND tt.ASSETS_STAGE = ‘0’ ),t1.ASSETS_VERSION_ID) AS “versionId”,t1.ID AS “id” FROM da_assets t1 WHERE t1.CLASSES_ID = ‘7’) t3 on t2.ID = t3.id
inner join da_assets_version t4 on t3.versionId = t4.ID
inner join da_dataset t5 on t4.DATASET_ID = t5.ID
inner join da_dp_field_current t6 on t5.DP_VERSION = t6.ID
inner join da_assets t7 on t1.ASSETS_ID = t7.ID
inner join da_dp_table t8 on t8.OID = t7.TABLE_OID GROUP BY t4.ASSETS_ID;
3、 json解析
t5.CONTENT ->> ‘
.
P
R
I
V
C
O
L
0
01
9
′
j
s
o
n
包着解析
t
3.
C
O
N
T
E
N
T
?
>
>
′
.PRIV_COL_0019' json包着解析 t3.CONTENT ->> '
.PRIVC?OL0?019′json包着解析t3.CONTENT?>>′.indexInfo.code’
4、查询
SELECT t3.*
FROM(SELECT t1.*,
IF (FIND_IN_SET(parent_id, @pids) > 0,@pids := CONCAT(@pids, ‘,’, id),‘0’) AS ischild
FROM(SELECT t.id,t.parent_id,t.NAME,t.TITLE
FROM da_tag_dim AS t
ORDER BY t.id ASC) t1,
(SELECT @pids := ‘zb2023033100113’) t2) t3
WHERE ischild != ‘0’;
5、更新json
JSON_SET(t5.content,‘
.
P
R
I
V
C
O
L
0
02
0
′
,
′
2022092501
2
′
)
多个：
a
.
c
o
n
t
e
n
t
=
J
S
O
N
S
E
T
(
a
.
c
o
n
t
e
n
t
,
′
.PRIV_COL_0020',' 20220925012') 多个：a.content = JSON_SET(a.content,'
.PRIVC?OL0?020′,′20220925012′)多个：a.content=JSONS?ET(a.content,′.senCode’,‘20211221004’,‘
.
u
p
d
a
t
e
C
y
c
l
e
I
d
′
,
′
2023091800
1
′
,
′
.updateCycleId','20230918001','
.updateCycleId′,′20230918001′,′.hasDate’,‘08:00’,‘$.maxAccount’,‘1’ )
UPDATE da_dataset_table t5 INNER join da_assets_version t4 on t4.DATASET_ID=t5.DATASET_ID INNER join da_assets a on a.ASSETS_VERSION_ID=t4.id SET t5.content = JSON_SET(t5.content,‘$.PRIV_COL_0020’,’
20220925012’) WHERE a.id in (‘1020005101168750593’);