Dataease自定义sql部分为什么在sql编译部分能够正常运行并且可以获取数据标签，但是保存sql后在选取数据集标签时会报错无法使用

user-yc5o2weitay · 2025 年10 月 9 日 05:40

我在自定义sql编辑时能跑，还能看到数据，但是等我保存退出来就会显示报错

user-fomw1qgd6jy · 2025 年10 月 9 日 05:52

请问是数据库是什么版本,de是什么版本?可以在dataease中配置一下 DEBUG 日志,看一下问题出在哪里,参考以下链接配置文件
DataEase 配置文件参数汇总 - FIT2CLOUD 知识库

user-yc5o2weitay · 2025 年10 月 9 日 07:06

用的de是2.10.12的，数据库是PostgreSQL 16.3

user-yc5o2weitay · 2025 年10 月 9 日 08:04

2025-10-09 07:57:30.323 ERROR — [-8100-exec-4185] i.d.exception.GlobalExceptionHandler : Method[deExceptionHandler][ERROR: syntax error at end of input
Position: 15221]这个是日志里面写的

user-fomw1qgd6jy · 2025 年10 月 10 日 02:35

docker logs dataease | grep calcite看一下没有问题吗,方便发一下写的sql语句吗

user-yc5o2weitay · 2025 年10 月 10 日 05:07

我感觉不是sql的问题，他这个sql是能运行出来的，我在idea里面运行过一遍的

user-yc5o2weitay · 2025 年10 月 10 日 05:43

WITH all_weeks AS (
    -- 提取所有周数据并转换为可排序的格式
    -- 提取所有周数据并转换为可排序的格式
    (SELECT

         dwd.get_factory(orgcode) as factory,
         npimp_tag as tpyes,
         substr(entdate_wk,5,4) entdate_wk,
         rtrim(moqiejiadong_rate, '%')  AS  moqiejiadong_rate_new,
     -- 提取周数字用于排序（WK01→1, WK10→10）
     CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
     FROM dws.dws_mqjiadong_rate_week where period ='2' and substr(entdate_wk,1,4) ='2025' and dwd.get_factory(orgcode) not like '%ccc%')

    union all
    (
        select  dwd.get_factory(orgcode) as factory,
                'xxxx' as tpyes,
                substr(entdate_wk,5,4) entdate_wk,
                rtrim(p_moqiejiadong_rate, '%')  AS  moqiejiadong_rate_new,
        -- 提取周数字用于排序（WK01→1, WK10→10）
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
        FROM dws.dws_mqjiadong_rate_week where period ='2' and substr(entdate_wk,1,4) ='2025' and dwd.get_factory(orgcode) not like '%ccc%' and npimp_tag ='ALL'

    )

    union all
    (
        select  dwd.get_factory(orgcode) as factory,
                'AAAA' as tpyes,
                substr(entdate_wk,5,4) entdate_wk,
                rtrim(y_moqiejiadong_rate, '%')  AS  moqiejiadong_rate_new,
        -- 提取周数字用于排序（WK01→1, WK10→10）
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
        FROM dws.dws_mqjiadong_rate_week where period ='2' and substr(entdate_wk,1,4) ='2025' and dwd.get_factory(orgcode) not like '%ccc%' and npimp_tag ='ALL'

    )

    union all
    (
        select  dwd.get_factory(orgcode) as factory,
                'BBBB' as tpyes,
                substr(entdate_wk,5,4) entdate_wk,
                rtrim(py_moqiejiadong_rate, '%')  AS  moqiejiadong_rate_new,
        -- 提取周数字用于排序（WK01→1, WK10→10）
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
        FROM dws.dws_mqjiadong_rate_week
        where period ='2' and substr(entdate_wk,1,4) ='2025' and dwd.get_factory(orgcode) not like '%ccc%' and npimp_tag ='ALL'

    )
),

 current_week_info AS (
         -- 获取当前周（最大的entdate_wk）
         SELECT
             MAX(entdate_wk) AS current_wk,
             MAX(wk_number) AS current_wk_num
         FROM all_weeks
     ),
     week_comparison AS (
         -- 使用窗口函数获取历史数据，避免子查询多行问题
         SELECT
             *,
             -- 获取前4周数据（同一工厂和类型）
             LAG(moqiejiadong_rate_new, 4) OVER (
                 PARTITION BY factory, tpyes
                 ORDER BY wk_number
                 ) AS prev4_rate,
             -- 获取前8周数据（同一工厂和类型）
             LAG(moqiejiadong_rate_new, 8) OVER (
                 PARTITION BY factory, tpyes
                 ORDER BY wk_number
                 ) AS prev8_rate
         FROM all_weeks
     ),



     current_week_comparison AS (
         -- 筛选出当前周的对比结果
         SELECT
             factory,
             tpyes,
             (coalesce(moqiejiadong_rate_new :: numeric,0) - coalesce(prev4_rate :: numeric,0) ):: varchar || '%' as current_vs_prev4,
             (coalesce(moqiejiadong_rate_new :: numeric,0) - coalesce(prev8_rate :: numeric,0) ):: varchar || '%' as current_vs_prev8
         -- 当前周与前4周对比
         /* CASE
              WHEN prev4_rate IS NULL THEN '无前4周数据'
              WHEN moqiejiadong_rate > prev4_rate THEN '上涨'
              WHEN moqiejiadong_rate < prev4_rate THEN '下降'
              ELSE '持平'
              END AS current_vs_prev4,
          -- 当前周与前8周对比
          CASE
              WHEN prev8_rate IS NULL THEN '无前8周数据'
              WHEN moqiejiadong_rate > prev8_rate THEN '上涨'
              WHEN moqiejiadong_rate < prev8_rate THEN '下降'
              ELSE '持平'
              END AS current_vs_prev8*/
         FROM week_comparison
         WHERE entdate_wk = (SELECT current_wk FROM current_week_info)
     )
--select * from current_week_comparison

-- 最终行转列结果
select
    factory,
    tpyes,
    -- 所有周的数据列
    "WK01", "WK02", "WK03", "WK04", "WK05", "WK06", "WK07", "WK08", "WK09", "WK10",
    "WK11", "WK12", "WK13", "WK14", "WK15", "WK16", "WK17", "WK18", "WK19", "WK20",
    "WK21", "WK22", "WK23", "WK24", "WK25", "WK26", "WK27", "WK28", "WK29", "WK30",
    "WK31", "WK32", "WK33", "WK34", "WK35", "WK36", "WK37", "WK38", "WK39", "WK40",
    "WK41", "WK42", "WK43", "WK44", "WK45", "WK46", "WK47", "WK48", "WK49", "WK50",
    "WK51", "WK52", "WK53",
    -- 当前周信息
    当前周,
    -- 对比结果
    "当前周与前4周比较",
    "当前周与前8周比较"
from
    (SELECT
         p.factory,
         p.tpyes,
         -- 所有周的数据列
         "WK01", "WK02", "WK03", "WK04", "WK05", "WK06", "WK07", "WK08", "WK09", "WK10",
         "WK11", "WK12", "WK13", "WK14", "WK15", "WK16", "WK17", "WK18", "WK19", "WK20",
         "WK21", "WK22", "WK23", "WK24", "WK25", "WK26", "WK27", "WK28", "WK29", "WK30",
         "WK31", "WK32", "WK33", "WK34", "WK35", "WK36", "WK37", "WK38", "WK39", "WK40",
         "WK41", "WK42", "WK43", "WK44", "WK45", "WK46", "WK47", "WK48", "WK49", "WK50",
         "WK51", "WK52", "WK53",
         -- 当前周信息
         (SELECT current_wk FROM current_week_info) AS 当前周,
         -- 对比结果
         c.current_vs_prev4 AS "当前周与前4周比较",
         c.current_vs_prev8 AS "当前周与前8周比较"
     --  case when c.factory = '模切BG' then 1 else 2 end as rk
     FROM (
              -- 行转列查询
              SELECT
                  factory,
                  tpyes,
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK01' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK01",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK02' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK02",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK03' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK03",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK04' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK04",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK05' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK05",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK06' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK06",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK07' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK07",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK08' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK08",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK09' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK09",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK10' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK10",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK11' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK11",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK12' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK12",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK13' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK13",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK14' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK14",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK15' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK15",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK16' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK16",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK17' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK17",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK18' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK18",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK19' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK19",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK20' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK20",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK21' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK21",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK22' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK22",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK23' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK23",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK24' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK24",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK25' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK25",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK26' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK26",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK27' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK27",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK28' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK28",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK29' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK29",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK30' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK30",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK31' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK31",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK32' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK32",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK33' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK33",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK34' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK34",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK35' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK35",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK36' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK36",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK37' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK37",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK38' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK38",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK39' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK39",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK40' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK40",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK41' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK41",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK42' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK42",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK43' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK43",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK44' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK44",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK45' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK45",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK46' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK46",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK47' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK47",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK48' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK48",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK49' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK49",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK50' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK50",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK51' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK51",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK52' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK52",
                  coalesce(MAX(CASE WHEN entdate_wk = 'WK53' THEN moqiejiadong_rate_new :: numeric END) ,0 ) :: varchar || '%' AS "WK53"
              FROM all_weeks
              GROUP BY factory, tpyes
          ) p
              JOIN current_week_comparison c
                   ON p.factory = c.factory
                       AND p.tpyes = c.tpyes
-- ORDER BY length(p.factory) ,p.factory desc
    ) tt
ORDER BY
    CASE WHEN factory = 'xxxx' THEN 0 ELSE 1 END,
    factory,
    tpyes
````预先格式化的文本``预先格式化的文本`预先格式化的文本

user-fomw1qgd6jy · 2025 年10 月 10 日 06:53

原因是DataEase 依赖的 JSQLParser 对该语法支持不足,做一下类型转换试一下

user-fomw1qgd6jy · 2025 年10 月 10 日 06:56

WITH all_weeks AS (
– 提取所有周数据并转换为可排序的格式
(SELECT
dwd.get_factory(orgcode) as factory,
npimp_tag as tpyes,
substr(entdate_wk,5,4) entdate_wk,
rtrim(moqiejiadong_rate, ‘%’) AS moqiejiadong_rate_new,
CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
FROM dws.dws_mqjiadong_rate_week
WHERE period =‘2’
AND substr(entdate_wk,1,4) =‘2025’
AND dwd.get_factory(orgcode) NOT LIKE ‘%ccc%’)

UNION ALL
(
    SELECT  
        dwd.get_factory(orgcode) as factory,
        'xxxx' as tpyes,
        substr(entdate_wk,5,4) entdate_wk,
        rtrim(p_moqiejiadong_rate, '%')  AS moqiejiadong_rate_new,
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
    FROM dws.dws_mqjiadong_rate_week 
    WHERE period ='2' 
      AND substr(entdate_wk,1,4) ='2025' 
      AND dwd.get_factory(orgcode) NOT LIKE '%ccc%' 
      AND npimp_tag ='ALL'
)

UNION ALL
(
    SELECT  
        dwd.get_factory(orgcode) as factory,
        'AAAA' as tpyes,
        substr(entdate_wk,5,4) entdate_wk,
        rtrim(y_moqiejiadong_rate, '%')  AS moqiejiadong_rate_new,
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
    FROM dws.dws_mqjiadong_rate_week 
    WHERE period ='2' 
      AND substr(entdate_wk,1,4) ='2025' 
      AND dwd.get_factory(orgcode) NOT LIKE '%ccc%' 
      AND npimp_tag ='ALL'
)

UNION ALL
(
    SELECT  
        dwd.get_factory(orgcode) as factory,
        'BBBB' as tpyes,
        substr(entdate_wk,5,4) entdate_wk,
        rtrim(py_moqiejiadong_rate, '%')  AS moqiejiadong_rate_new,
        CAST(RIGHT(entdate_wk, 2) AS INT) AS wk_number
    FROM dws.dws_mqjiadong_rate_week
    WHERE period ='2' 
      AND substr(entdate_wk,1,4) ='2025' 
      AND dwd.get_factory(orgcode) NOT LIKE '%ccc%' 
      AND npimp_tag ='ALL'
)

),

current_week_info AS (
– 获取当前周（最大的entdate_wk）
SELECT
MAX(entdate_wk) AS current_wk,
MAX(wk_number) AS current_wk_num
FROM all_weeks
),

week_comparison AS (
– 使用窗口函数获取历史数据，避免子查询多行问题
SELECT
*,
– 获取前4周数据（同一工厂和类型）
LAG(moqiejiadong_rate_new, 4) OVER (
PARTITION BY factory, tpyes
ORDER BY wk_number
) AS prev4_rate,
– 获取前8周数据（同一工厂和类型）
LAG(moqiejiadong_rate_new, 8) OVER (
PARTITION BY factory, tpyes
ORDER BY wk_number
) AS prev8_rate
FROM all_weeks
),

current_week_comparison AS (
– 筛选出当前周的对比结果
SELECT
factory,
tpyes,
(COALESCE(CAST(moqiejiadong_rate_new AS NUMERIC), 0) - COALESCE(CAST(prev4_rate AS NUMERIC), 0))::VARCHAR || ‘%’ AS current_vs_prev4,
(COALESCE(CAST(moqiejiadong_rate_new AS NUMERIC), 0) - COALESCE(CAST(prev8_rate AS NUMERIC), 0))::VARCHAR || ‘%’ AS current_vs_prev8
FROM week_comparison
WHERE entdate_wk = (SELECT current_wk FROM current_week_info)
)

– 最终行转列结果
SELECT
factory,
tpyes,
– 所有周的数据列
“WK01”, “WK02”, “WK03”, “WK04”, “WK05”, “WK06”, “WK07”, “WK08”, “WK09”, “WK10”,
“WK11”, “WK12”, “WK13”, “WK14”, “WK15”, “WK16”, “WK17”, “WK18”, “WK19”, “WK20”,
“WK21”, “WK22”, “WK23”, “WK24”, “WK25”, “WK26”, “WK27”, “WK28”, “WK29”, “WK30”,
“WK31”, “WK32”, “WK33”, “WK34”, “WK35”, “WK36”, “WK37”, “WK38”, “WK39”, “WK40”,
“WK41”, “WK42”, “WK43”, “WK44”, “WK45”, “WK46”, “WK47”, “WK48”, “WK49”, “WK50”,
“WK51”, “WK52”, “WK53”,
– 当前周信息
当前周,
– 对比结果
“当前周与前4周比较”,
“当前周与前8周比较”
FROM
(SELECT
p.factory,
p.tpyes,
– 所有周的数据列
“WK01”, “WK02”, “WK03”, “WK04”, “WK05”, “WK06”, “WK07”, “WK08”, “WK09”, “WK10”,
“WK11”, “WK12”, “WK13”, “WK14”, “WK15”, “WK16”, “WK17”, “WK18”, “WK19”, “WK20”,
“WK21”, “WK22”, “WK23”, “WK24”, “WK25”, “WK26”, “WK27”, “WK28”, “WK29”, “WK30”,
“WK31”, “WK32”, “WK33”, “WK34”, “WK35”, “WK36”, “WK37”, “WK38”, “WK39”, “WK40”,
“WK41”, “WK42”, “WK43”, “WK44”, “WK45”, “WK46”, “WK47”, “WK48”, “WK49”, “WK50”,
“WK51”, “WK52”, “WK53”,
– 当前周信息
(SELECT current_wk FROM current_week_info) AS 当前周,
– 对比结果
c.current_vs_prev4 AS “当前周与前4周比较”,
c.current_vs_prev8 AS “当前周与前8周比较”
FROM (
– 行转列查询
SELECT
factory,
tpyes,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK01’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK01”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK02’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK02”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK03’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK03”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK04’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK04”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK05’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK05”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK06’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK06”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK07’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK07”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK08’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK08”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK09’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK09”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK10’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK10”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK11’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK11”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK12’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK12”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK13’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK13”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK14’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK14”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK15’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK15”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK16’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK16”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK17’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK17”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK18’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK18”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK19’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK19”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK20’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK20”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK21’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK21”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK22’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK22”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK23’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK23”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK24’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK24”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK25’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK25”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK26’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK26”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK27’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK27”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK28’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK28”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK29’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK29”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK30’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK30”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK31’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK31”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK32’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK32”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK33’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK33”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK34’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK34”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK35’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK35”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK36’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK36”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK37’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK37”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK38’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK38”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK39’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK39”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK40’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK40”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK41’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK41”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK42’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK42”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK43’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK43”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK44’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK44”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK45’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK45”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK46’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK46”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK47’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK47”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK48’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK48”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK49’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK49”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK50’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK50”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK51’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK51”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK52’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK52”,
COALESCE(MAX(CASE WHEN entdate_wk = ‘WK53’ THEN CAST(moqiejiadong_rate_new AS NUMERIC) END), 0)::VARCHAR || ‘%’ AS “WK53”
FROM all_weeks
GROUP BY factory, tpyes
) p
JOIN current_week_comparison c
ON p.factory = c.factory
AND p.tpyes = c.tpyes
) tt
ORDER BY
CASE WHEN factory = ‘xxxx’ THEN 0 ELSE 1 END,
factory,
tpyes

我这边是用 AI 跑了一下做了一下类型转换,可以参考一下哈