Doris 2.1.10
以下SQL是BI软件生成的，执行时遇到错误：
[Code: 1054, SQL State: 42S22] errCode = 2, detailMessage = Unknown column 'code' in 'sg_bg_db'
其中的sg_bg_db是数据库名
注释掉SQL最后面任意一个and 条件，都能执行过去。
select
cast(`T_1B395D8671544D8CBA503`.`部门` as varchar) as `__fcol_1`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-年目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2025-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-01-01')
) then `T_1B395D8671544D8CBA503`.`实收目标`
else null
end) as `__fcol_4`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2027-01-01')
) then `T_1B395D8671544D8CBA503`.`实收金额`
else null
end) as `__fcol_5`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-月目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-02-01')
) then `T_1B395D8671544D8CBA503`.`实收目标`
else null
end) as `__fcol_6`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-02-01')
) then `T_1B395D8671544D8CBA503`.`实收金额`
else null
end) as `__fcol_7`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-周目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-19')
) then `T_1B395D8671544D8CBA503`.`实收目标`
else null
end) as `__fcol_8`,
sum(case
when `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-19') then `T_1B395D8671544D8CBA503`.`实收金额`
else null
end) as `__fcol_9`,
sum(case
when (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-年目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2027-01-01')
) then `T_1B395D8671544D8CBA503`.`营收目标`
else null
end) as `__fcol_10`,
sum(case
when `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-19') then `T_1B395D8671544D8CBA503`.`营收金额`
else null
end) as `__fcol_11`
from (select
cast(date_wid as date) as `日期`,
contract_id as `合同ID`,
dbl_sign_date as `双签时间`,
statistics_dbl_sign_date as `统计双签时间`,
customer_id as `客户ID`,
customer_name as `客户名称`,
employee_id as `员工工号`,
employee_name as `员工名称`,
department as `部门`,
branch_name as `分支`,
region_name as `大区`,
yysr_amount as `营收金额`,
ss_amount as `实收金额`,
contract_amount as `合同金额`,
yysr_target as `营收目标`,
ss_target as `实收目标`,
contract_target as `合同目标`,
target_flag as `目标-标签`,
hire_status as `在职状态`,
t.MONTH_WEEK_DESCP as `月周描述`,
t.MONTH_WEEK as `月周`,
substr(dim.MONTH_WEEK,1,4) as plan_year,
cast(dim.season as string) as plan_season,
substr(dim.MONTH_WEEK,5,2) as plan_month,
substr(dim.MONTH_WEEK,7,1) as plan_week
from v_fy25_three_way_data_amount_target t
left join date_dim dim on t.date_wid = dim.DATE_TIME
-- 关联权限表
inner join
(
select username ,real_name ,hr_id ,branch_num ,b.name as branch,region_num ,r.name as region,org_type
from g_organization_user t
left join g_organization b on b.code = t.branch_num
left join g_organization r on r.code = t.region_num
where primary_flag=1 and end_time is null -- and org_type in ('BRANCH','REGION')
) p
on
case when p.org_type ='BRANCH' then p.branch
-- when p.org_type ='REGION' then '施工客群' -- 全国权限 p.region
else '施工客群'
end =
case when p.org_type ='BRANCH' then t.branch_name
-- when p.org_type ='REGION' then '施工客群' -- 全国权限 t.region_name
else '施工客群'
end
where upper(p.username) = upper('dongsp')
-- 测试 '10000894' 三部 10018546 东北区域
) as `T_1B395D8671544D8CBA503`
where (
(
`T_1B395D8671544D8CBA503`.`目标-标签` is null
or `T_1B395D8671544D8CBA503`.`目标-标签` in (
'部门-月目标', '部门-年目标', '部门-季目标', '部门-周目标', '分支-月目标', '分支-年目标', '分支-周目标'
)
)
and `T_1B395D8671544D8CBA503`.`大区` in (
'管理一部', '北京独立区', '管理三部', '管理二部', '分支管理三部', '分支管理四部', '分支管理二部', '分支管理一部'
)
and `T_1B395D8671544D8CBA503`.`分支` in ('北京区域')
and `T_1B395D8671544D8CBA503`.`部门` is not null
and `T_1B395D8671544D8CBA503`.`部门` not in (
'北京区域实施', ''
)
and (
(
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-年目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2025-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-01-01')
)
or (
`T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2027-01-01')
)
or (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-月目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-02-01')
)
or (
`T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2026-02-01')
)
or (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-周目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-19')
)
or `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-19')
or (
`T_1B395D8671544D8CBA503`.`目标-标签` in ('部门-年目标')
and `T_1B395D8671544D8CBA503`.`日期` >= date('2026-01-01')
and `T_1B395D8671544D8CBA503`.`日期` < date('2027-01-01')
)
)
)
group by 1
