errCode = 2, detailMessage = Unknown column 'id' in 'zzt_record.t'
库：zzt_record 建表语句
create table tb_record_project_key_indicators
(
id varchar(300) null,
performance_type varchar(30) null,
contract_type varchar(30) null,
qualification_code varchar(192) null,
qualification_name varchar(192) null,
project_level varchar(60) null,
prj_num varchar(192) null,
tenant_id bigint null,
project_name varchar(765) null,
start_time datetime null,
end_time datetime null,
data_provider varchar(30) null,
prj_guid varchar(192) null,
perf_num varchar(150) null,
data_level varchar(3) null
)
engine = Doris
collate = `utf-8` partition by unpartitioned
(
NULL
) subpartition by null
(
NULL
);
库：zzt_business 建表语句
create table tb_log_table_operation
(
id bigint null,
data_row_guid varchar(765) null,
table_name varchar(765) null,
table_content string null,
tenant_id bigint null,
operation_type varchar(30) null,
fk_row_guid varchar(300) null
)
engine = Doris
collate = `utf-8` partition by unpartitioned
(
NULL
) subpartition by null
(
NULL
);
create view tb_project_key_indicators as
select (case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.id')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.id'))
else `internal`.`zzt_record`.`t`.`id`
end) as `id`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.performanceType')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.performanceType'))
else `internal`.`zzt_record`.`t`.`performance_type`
end) as `performance_type`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.contractType')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.contractType'))
else `internal`.`zzt_record`.`t`.`contract_type`
end) as `contract_type`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationCode')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationCode'))
else `internal`.`zzt_record`.`t`.`qualification_code`
end) as `qualification_code`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationName')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationName'))
else `internal`.`zzt_record`.`t`.`qualification_name`
end) as `qualification_name`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectLevel')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectLevel'))
else `internal`.`zzt_record`.`t`.`project_level`
end) as `project_level`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjNum')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjNum'))
else `internal`.`zzt_record`.`t`.`prj_num`
end) as `prj_num`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectName')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectName'))
else `internal`.`zzt_record`.`t`.`project_name`
end) as `project_name`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.startTime')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.startTime'))
else `internal`.`zzt_record`.`t`.`start_time`
end) as `start_time`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.endTime')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.endTime'))
else `internal`.`zzt_record`.`t`.`end_time`
end) as `end_time`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjGuid')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjGuid'))
else `internal`.`zzt_record`.`t`.`prj_guid`
end) as `prj_guid`,
(case
when ((`internal`.`zzt_business`.`lt`.`operation_type` is not null)
and (json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.dataLevel')) is not null)) then json_unquote(json_extract(
`internal`.`zzt_business`.`lt`.`table_content`,
'$.dataLevel'))
else `internal`.`zzt_record`.`t`.`data_level`
end) as `data_level`,
'[]' as `technical_index_item`,
`internal`.`zzt_record`.`t`.`data_provider` as `data_provider`,
`internal`.`zzt_record`.`t`.`tenant_id` as `tenant_id`
from `internal`.`zzt_record`.`tb_record_project_key_indicators` `t`
left join `internal`.`zzt_business`.`tb_log_table_operation` `lt` on
`internal`.`zzt_record`.`t`.`id` = `internal`.`zzt_business`.`lt`.`data_row_guid`
and `internal`.`zzt_business`.`lt`.`table_name` = 'tb_project_key_indicators'
union all
select `internal`.`zzt_business`.`lt`.`data_row_guid` as `id`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.performanceType')) as `performance_type`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.contractType')) as `contract_type`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationCode')) as `qualification_code`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.qualificationName')) as `qualification_name`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectLevel')) as `project_level`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjNum')) as `prj_num`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.projectName')) as `project_name`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.startTime')) as `start_time`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.endTime')) as `end_time`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.prjGuid')) as `prj_guid`,
json_unquote(json_extract(`internal`.`zzt_business`.`lt`.`table_content`,
'$.dataLevel')) as `data_level`,
'[]' as `technical_index_item`,
null as `data_provider`,
`internal`.`zzt_business`.`lt`.`tenant_id` as `tenant_id`
from `internal`.`zzt_business`.`tb_log_table_operation` `lt`
where ((`internal`.`zzt_business`.`lt`.`operation_type` = 'insert')
and (`internal`.`zzt_business`.`lt`.`table_name` = 'tb_project_key_indicators'));
查询语句
SELECT CASE
WHEN tpki.performance_type = '5' THEN '4'
WHEN tpki.performance_type = '7' THEN '3'
WHEN tpki.performance_type = '1' THEN '1'
WHEN tpki.performance_type = '2' THEN '2' END AS performance_type,
tpki.qualification_name,
tpki.project_level,
tpi.prj_name,
tpki.id,
tpi.row_guid AS prj_row_guid,
GROUP_CONCAT(DISTINCT CASE
WHEN tiru.primary_duty = '1' THEN '项目负责人'
WHEN tiru.primary_duty = '2' THEN '技术负责人'
WHEN tiru.primary_duty = '3' THEN '专业负责人'
WHEN tiru.primary_duty = '4' THEN '主要设计人'
WHEN tiru.primary_duty = '5' THEN '总监理工程师'
WHEN tiru.primary_duty = '6' THEN '项目经理' END) AS primary_duty,
tpki.data_provider
FROM tb_project_key_indicators tpki
JOIN tb_project_info tpi ON tpki.prj_guid = tpi.row_guid
JOIN tb_indicators_relation_user tiru
ON tiru.indicators_id = tpki.id AND tiru.person_name = 'xxx' AND tiru.idcard_dese = 'xxx'
WHERE (tpki.tenant_id = 18 OR tpki.tenant_id = 0 OR tpki.tenant_id IS NULL OR tpki.tenant_id IN (2))
GROUP BY tpki.id
ORDER BY tpi.prj_name
视图中的字段只要在group by以及GROUP_CONCAT中使用就会报Unknown column 'id' in 'zzt_record.t'
