deaftstill commented on issue #18109:
URL: https://github.com/apache/doris/issues/18109#issuecomment-1503239146
> @deaftstill 超过32个 union all 稳定复现么，方便的话贴下SQL
之前其他人报过一样的栈，关闭profile后就没问题了，怀疑和频繁创建线程有关
select
B34C as source_data,
'B34C' as field_name,
55 as yy_id,
id as xx_id,
'215' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,B34C,
if(B34C regexp '^\\s$|^-$' or B34C regexp '^\\s*$' or B34C is
null,0,1) yy_flag,
if(B34C regexp '^\\s$|^-$' or B34C regexp '^\\s*$' or B34C is
null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
B34C as source_data,
'B34C' as field_name,
55 as yy_id,
id as xx_id,
'216' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B34C,
0 as yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and B34C not regexp '^\\s*$|^-$'
and B34C is not null
and B34C not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc019'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc019'
)t1
)
) rt1
union
select
B36C as source_data,
'B36C' as field_name,
55 as yy_id,
id as xx_id,
'212' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,B36C,
if(B36C regexp '^\\s*$' or B36C =' ' or B36C is null,0,1)
yy_flag,
if(B36C regexp '^\\s*$' or B36C =' ' or B36C is null,1,0) as
is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
B36C as source_data,
'B36C' as field_name,
55 as yy_id,
id as xx_id,
'213' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B36C,
0 as yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and B36C not regexp '^\\s*$|^-$'
and B36C is not null
and B36C not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc028'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc028'
)t1
)
) rt1
union
select
B37 as source_data,
'B37' as field_name,
55 as yy_id,
id as xx_id,
'214' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B37,
if(B36C = 2 and (B37 regexp '^\\s$|^-$' or B37 regexp '^\\s*$'
or B37 is null),0,1) yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
B35 as source_data,
'B35' as field_name,
55 as yy_id,
id as xx_id,
'217' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B35,
if(B34C in (2,3) and (B35 regexp '^\\s$|^-$' or B35 regexp
'^\\s*$' or B35 is null),0,1) yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C28 as source_data,
'C28' as field_name,
55 as yy_id,
id as xx_id,
'201' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C28,
if(C28 != '-',if(C28 regexp '^\\+?[0-9]*$' and C28 <
99999,1,0),1) yy_flag,
if(C28 regexp '^\\s*$' or C28=' ' or C28 is null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C29 as source_data,
'C29' as field_name,
55 as yy_id,
id as xx_id,
'202' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C29,
if(C29 != '-',if(C29 regexp '^\\+?[0-9]*$' and C29 < 24,1,0),1)
yy_flag,
if(C29 regexp '^\\s*$' or C29 =' ' or C29 is null,1,0) as
is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C30 as source_data,
'C30' as field_name,
55 as yy_id,
id as xx_id,
'203' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C30,
if(C30 != '-',if(C30 regexp '^\\+?[0-9]*$' and C30 < 60,1,0),1)
yy_flag,
if(C30 regexp '^\\s*$' or C30 =' ' or C30 is null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C31 as source_data,
'C31' as field_name,
55 as yy_id,
id as xx_id,
'204' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C31,
if(C31 != '-',if(C31 regexp '^\\+?[0-9]*$',1,0),1) yy_flag,
if(C31 regexp '^\\s*$' or C31 =' ' or C31 is null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C32 as source_data,
'C32' as field_name,
55 as yy_id,
id as xx_id,
'205' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C32,
if(C32 != '-',if(C32 regexp '^\\+?[0-9]*$' and C32 < 24,1,0),1)
yy_flag,
if(C32 regexp '^\\s*$' or C32 =' ' or C32 is null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C33 as source_data,
'C33' as field_name,
55 as yy_id,
id as xx_id,
'206' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
select
id,C33,
if(C33 != '-',if(C33 regexp '^\\+?[0-9]*$' and C33 < 60,1,0),1)
yy_flag,
if(C33 regexp '^\\s*$' or C33=' ' or C33 is null,1,0) as is_complete
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C47 as source_data,
'C47' as field_name,
55 as yy_id,
id as xx_id,
'207' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,C47,
if(C47 not regexp '^\\s$|^-$' and C47 not regexp '^\\s*$' and
C47 is not null and C47 not regexp '^\\+?[0-9]*$',0,1) yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
code as source_data,
'C06x01C' as field_name,
55 as yy_id,
id as xx_id,
'88' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,code,
if(c1 > 0 and c2 >0 and c3 = 0,0,1) yy_flag
from(
select
id,code,
sum(if(C03C regexp '^O8[0-4].*' or dia regexp
'^O8[0-4].*',1,0)) c1,
sum(if(dia regexp '^O0[0-8].*',1,0)) c2,
sum(if(dia regexp '^Z37.*',1,0)) c3
from(
select
id,code,dia,C03C
from(
select
id,code,C03C,dia
from (
select
id,C03C
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) m
join(
select
xx_id,code as dia
from dwd_xx_diagnosis
where type!=0
) modi
on m.id = modi.xx_id
join(
select
xx_id,code
from dwd_xx_diagnosis
where type=1
) modi1
on m.id = modi1.xx_id
)rt1
)rt2
group by id,code
)rts1
) rts2
union
select
C12C as source_data,
'C12C' as field_name,
55 as yy_id,
id as xx_id,
'102' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,C12C,
if(C03C regexp '^[S|T].*' and (C12C REGEXP '^\\s$|^-$' or C12C
is null),0,1) as yy_flag
from xxx m
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C12C as source_data,
'C12C' as field_name,
55 as yy_id,
m1.id as xx_id,
'103' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
if(m2.id is not null,0,1) as yy_flag,null as is_complete
from(
select
id
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)m1
left join(
select
id,C12C
from dwd_xx
where C12C not in (select ficdm from
ods_national_clinical_disease_codes)
and C12C is not null
and C12C not regexp '^\\s$|^-$'
and C12C not regexp '^\\s*$'
and B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)m2
on m1.id=m2.id
union
select
C12C as source_data,
'C12C' as field_name,
55 as yy_id,
id as xx_id,
'104' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,C12C,
if(C12C not regexp '^[V].*|^[W].*|^[X].*|^[Y].*',0,1) as yy_flag
from dwd_xx
where C12C not regexp '^\\s$|^-$'
and C12C not regexp '^\\s*$'
and C12C is not null
and C12C in (select ficdm from ods_national_clinical_disease_codes)
and B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
UNION
select
C13N as source_data,
'C13N' as field_name,
55 as yy_id,
id as xx_id,
'105' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,C13N,
if(C03C regexp '^[S|T].*' and (C13N REGEXP '^\\s$|^-$' or C13N
is null),0,1) as yy_flag
from xxx m
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C13N as source_data,
'C13N' as field_name,
55 as yy_id,
m1.id as xx_id,
'106' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
if(m2.id is not null,0,1) as yy_flag,null as is_complete
from(
select
id
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)m1
left join(
select
id,C13N
from dwd_xx
where C13N not in (select fjbname from
ods_national_clinical_disease_codes)
and C13N is not null
and C13N not regexp '^\\s$|^-$'
and C13N not regexp '^\\s*$'
and B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)m2
on m1.id=m2.id
union
select
F10 as source_data,
'F10' as field_name,
55 as yy_id,
id as xx_id,
'110' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,F10,0 as yy_flag
FROM dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and F10 not regexp '^\\s*$|^-$'
and F10 is not null
and F10 not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc007'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc007'
)t1
)
) rt1
union
select
F11 as source_data,
'F11' as field_name,
55 as yy_id,
id as xx_id,
'111' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,F11,0 yy_flag
FROM dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and F11 not regexp '^\\s*$|^-$'
and F11 is not null
and F11 not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc007'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc007'
)t1
)
) rt1
union
select
F12 as source_data,
'F12' as field_name,
55 as yy_id,
id as xx_id,
'112' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,F12,0 yy_flag
FROM dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and F12 not regexp '^\\s*$|^-$'
and F12 is not null
and F12 not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc007'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc007'
)t1
)
) rt1
union
select
B30C as source_data,
'B30C' as field_name,
55 as yy_id,
id as xx_id,
'128' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,B30C,0 as yy_flag
FROM dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and B30C not regexp '^\\s*$|^-$'
and B30C is not null
and B30C not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc011'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc011'
)t1
)
) rt1
union
select
B33 as source_data,
'B33' as field_name,
55 as yy_id,
id as xx_id,
'131' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B33,
if(B33 not regexp '^\\s$|^-$' and B33 is not null and B33 not
regexp '^([1-2][0-9][0-9][0-9]-[0-1]{0,1}[0-9]-[0-3]{0,1}[0-9])$',0,1) as
yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rts1
union
select
B33 as source_data,
'B33' as field_name,
55 as yy_id,
id as xx_id,
'132' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,B33,
if(B33 not regexp '^\\s$|^-$' and B33 is not null and B33
regexp '^([1-2][0-9][0-9][0-9]-[0-1]{0,1}[0-9]-[0-3]{0,1}[0-9])$' and B33 <
B12,0,1) as yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
C34C as source_data,
'C34C' as field_name,
55 as yy_id,
id as xx_id,
'133' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,C34C,
0 as yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and C34C not regexp '^\\s*$|^-$'
and C34C is not null
and C34C not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc016'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc016'
)t1
)
) rt1
union
select
C34C as source_data,
'C34C' as field_name,
55 as yy_id,
id as xx_id,
'134' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
id,C34C,
0 as yy_flag
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
and B34C in (
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc019'
and business_code in ('5')
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc019'
and business_code in ('5')
)t1
)
and C34C not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc016'
and business_code in ('1','2')
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc016'
and business_code in ('1','2')
)t1
)
) rt1
union
select
operation_time as source_data,
'C16x01' as field_name,
55 as yy_id,
id as xx_id,
'143' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,
is_complete
from(
SELECT
t1.id,operation_time,
if(code not regexp '^\\s$|^-$' and code is not null and
(operation_time regexp '^\\s$|^-$' or operation_time regexp '^\\s*$' or
operation_time is null),0,1) yy_flag,
if(operation_time regexp '^\\s$|^-$' or operation_time regexp
'^\\s*$' or operation_time is null,1,0) as is_complete
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
) rt1
union
select
operation_time as source_data,
'C16x01' as field_name,
55 as yy_id,
id as xx_id,
'145' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,operation_time,
if(operation_time not regexp '^\\s$|^-$' and operation_time not
regexp '^\\s*$' and operation_time is not null and operation_time not regexp
'^((\\d{2}(([02468][048])|([13579][26]))[\\-]?((((0[13578])|(1[02]))[\\-]?((0[1-9])|([1-2][0-9])|(3[01])))|(((0[469])|(11))[\\-]?((0[1-9])|([1-2][0-9])|(30)))|(02[\\-]?((0[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-]?((((0[13578])|(1[02]))[\\-]?((0[1-9])|([1-2][0-9])|(3[01])))|(((0[469])|(11))[\\-]?((0[1-9])|([1-2][0-9])|(30)))|(02[\\-]?((0[1-9])|(1[0-9])|(2[0-8]))))))
(((([0-1][0-9])|(2[0-3]))[\\:]?([0-5][0-9])[\\:]?((([0-5][0-9])))))$',0,1)
yy_flag
from(
select
id,B12
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
) rt1
union
select
level_code as source_data,
'C17x01' as field_name,
55 as yy_id,
id as xx_id,
'146' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,level_code,
if(level_code regexp '^\\s$|^-$' or level_code is null,0,1)
yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='介入治疗' or foplb='手术')
) rt1
union
select
level_code as source_data,
'C17x01' as field_name,
55 as yy_id,
id as xx_id,
'147' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,level_code,
0 as yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and level_code not regexp '^\\s*$|^-$'
and level_code is not null
and level_code not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc029'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc029'
)t1
)
) rt1
union
select
operator_name as source_data,
'C18x01' as field_name,
55 as yy_id,
id as xx_id,
'149' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,operator_name,
if(operator_name regexp '^\\s$|^-$' or operator_name is
null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='介入治疗' or foplb='手术')
) rt1
union
select
assistant1_name as source_data,
'C19x01' as field_name,
55 as yy_id,
id as xx_id,
'150' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,assistant1_name,
if(assistant1_name regexp '^\\s$|^-$' or assistant1_name is
null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='介入治疗' or foplb='手术')
) rt1
union
select
assistant2_name as source_data,
'C20x01' as field_name,
55 as yy_id,
id as xx_id,
'151' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,assistant2_name,
if(assistant2_name regexp '^\\s$|^-$' or assistant2_name is
null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from
ods_country_surgical_operation_code where foplb='介入治疗' or foplb='手术')
) rt1
union
select
incision_heal_level_code as source_data,
'C21x01C' as field_name,
55 as yy_id,
id as xx_id,
'152' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,incision_heal_level_code,
if(incision_heal_level_code regexp '^\\s$|^-$' or
incision_heal_level_code regexp '^\\s*$' or incision_heal_level_code is
null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='手术')
) rt1
union
select
incision_heal_level_code as source_data,
'C21x01C' as field_name,
55 as yy_id,
id as xx_id,
'153' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,incision_heal_level_code,
0 as yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and incision_heal_level_code not regexp '^\\s*$|^-$'
and incision_heal_level_code is not null
and incision_heal_level_code not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc014'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc014'
)t1
)
) rt1
union
select
anaesthesia_code as source_data,
'C22x01C' as field_name,
55 as yy_id,
id as xx_id,
'154' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,anaesthesia_code,
if(anaesthesia_code regexp '^\\s$|^-$' or anaesthesia_code is
null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='手术')
) rt1
union
select
anaesthesia_code as source_data,
'C22x01C' as field_name,
55 as yy_id,
id as xx_id,
'155' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,anaesthesia_code,
0 as yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and anaesthesia_code not regexp '^\\s*$|^-$'
and anaesthesia_code is not null
and anaesthesia_code not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc013'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc013'
)t1
)
) rt1
union
select
anaesthesia_level_code as source_data,
'F15' as field_name,
55 as yy_id,
id as xx_id,
'156' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,anaesthesia_level_code,
0 as yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and anaesthesia_level_code not regexp '^\\s*$|^-$'
and anaesthesia_level_code is not null
and anaesthesia_level_code not in(
SELECT
name
from(
select business_code as name
from standard_dict_type_contrast
where dict_type = 'rc024'
union all
select business_name as name
from standard_dict_type_contrast
where dict_type = 'rc024'
)t1
)
) rt1
union
select
anesthesiologist_name as source_data,
'C23x01' as field_name,
55 as yy_id,
id as xx_id,
'157' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
SELECT
t1.id,anesthesiologist_name,
if(anesthesiologist_name regexp '^\\s$|^-$' or
anesthesiologist_name is null,0,1) yy_flag
from(
select
id
from dwd_xx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
)t1 join dwd_xx_operation t2
on t1.id=t2.xx_id
where type=0
and code in (select fopcode from ods_country_surgical_operation_code
where foplb='手术')
) rt1
union
select
F17 as source_data,
'F17' as field_name,
55 as yy_id,
id as xx_id,
'177' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,F17,
if(F17 not regexp '^\\s$|^-$' and F17 is not null and F17 not
regexp '^\\+?[0-9]*$',0,1) yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
F18 as source_data,
'F18' as field_name,
55 as yy_id,
id as xx_id,
'178' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,F18,
if(F18 not regexp '^\\s$|^-$' and F18 is not null and F18 not
regexp '^\\+?[0-9]*$',0,1) yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
union
select
F19 as source_data,
'F19' as field_name,
55 as yy_id,
id as xx_id,
'179' as defect_rule_slave_id,
'0' as yy_type,
'2022-11-01 11:11:11' as yy_start,
'2022-11-30 11:11:11' as yy_end,
now() as gmt_create,
yy_flag,null as is_complete
from(
select
id,F19,
if(F19 not regexp '^\\s$|^-$' and F19 is not null and F19 not
regexp '^\\+?[0-9]*$',0,1) yy_flag
from xxx
where B15 >='2022-11-01 11:11:11'
and B15 < '2022-11-30 11:11:11'
) rt1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
