在其他平台可以运行，但永洪本身是数字的显示成文本

强制转换不了，应该是加了case...when之后的原因，应该怎么把金额的列改成数字格式
-- 第一部分：主订单数据查询（全链条强制数字类型）
SELECT
    md."学员ID" "用户id",
    md."订单编号" "订单编号1",
    md."原始支付时间",
    md."原始订单编号" "原始订单编号1",
    ed.first_level_department_name AS "一级部门",
    ed.second_level_department_name AS "二级部门",
    ed.third_level_department_name AS "三级部门",
    md."归属团队",
    md.refund_time,
    md.real_price "实付金额",
    md.refund_price "退款金额",
    md.promit_price "净收金额",
    md.employee_email_name1,
    md.is_full_refund_order_xin,
    0 AS data_source
FROM (
    SELECT
        t.user_number AS "学员ID",
        t.order_number AS "订单编号",
        t.old_paid_timestamp AS "原始支付时间",
        t.original_order_number as "原始订单编号",
        jg.zuzhang AS "归属团队",
        CASE WHEN t.is_full_refund_order = '0' THEN ''
             ELSE DATE_FORMAT(a.rt, '%Y-%m-%d %H:%i:%s')
        END AS refund_time,
        -- 1. 实付金额：最终转换为DOUBLE，确保无文本残留
        CAST(
            CASE WHEN t.user_number = duihuan.uid THEN duihuan.real
                 ELSE t.order_price  -- t.order_price已确保是数字
            END AS DOUBLE
        ) AS real_price,
        -- 2. 退款金额：处理a.refund_amount的文本污染
        CAST(
            CASE WHEN t.user_number = duihuan.uid THEN duihuan.refund
                 ELSE COALESCE(CAST(a.refund_amount AS DOUBLE), 0.0)  -- 先转a的字段为数字
            END AS DOUBLE
        ) AS refund_price,
        -- 3. 净收金额：同理处理a.profit_amount
        CAST(
            CASE WHEN t.user_number = duihuan.uid THEN duihuan.promit
                 ELSE COALESCE(CAST(a.profit_amount AS DOUBLE), 0.0)  -- 先转a的字段为数字
            END AS DOUBLE
        ) AS promit_price,
        CASE
            WHEN t.user_number = xy.uid THEN xy.assistant
            WHEN t.user_number = duihuan.uid AND duihuan.promit = 300 THEN '定金'
            ELSE t.employee_email_name
        END AS employee_email_name1,
        CASE
            WHEN (CASE WHEN t.user_number = duihuan.uid THEN duihuan.refund
                       ELSE COALESCE(CAST(a.refund_amount AS DOUBLE), 0.0)
                  END) > 0 THEN '1'
            ELSE t.is_full_refund_order
        END AS is_full_refund_order_xin
    FROM (
        SELECT
            user_number, order_number, employee_email_name,
            -- 确保order_price是纯数字（先转DOUBLE再运算）
            CAST(CAST(order_price AS DOUBLE) / 100 AS DOUBLE) AS order_price,
            clazz_name, old_paid_timestamp,
            is_full_refund_order, original_order_number
        FROM finance_dw.dim_finance_order_extend_df
        WHERE dt = FORMAT_DATETIME(DATE_ADD('day',-1, CURRENT_DATE), 'yyyyMMdd')
          AND goods_first_level_department_name IN ('H业务线')
          AND course_biz_number IN ('31ZY25Q15R3', '31ZY25CR081', '32ZY25CY1P1', '31ZY25CMZO1', '31ZY25CHTQ3')
          AND goods_second_level_department_name IN ('市场二部', '精品班学部')
          AND order_number = last_order_number
          AND is_pay_success_order = '1'
          AND clazz_name NOT LIKE '%测试%'
          AND clazz_name NOT LIKE '%体验%'
    ) t
    LEFT JOIN (
        SELECT
            original_order_number,
            -- 聚合后立即转为DOUBLE，避免字符串
            CAST(SUM(profit_amount) AS DOUBLE) AS profit_amount,
            MAX(trade_timestamp) AS rt,
            CAST(SUM(refund_amount) AS DOUBLE) AS refund_amount
        FROM bdg_ba.dwd_finance_acc_control_mbr_cashflow_df
        WHERE dt = FORMAT_DATETIME(DATE_ADD('day',-1, CURRENT_DATE), 'yyyyMMdd')
          AND course_second_level_department_name = '市场二部'
        GROUP BY original_order_number
    ) a ON t.original_order_number = a.original_order_number
    LEFT JOIN (
        SELECT DISTINCT employ, zuzhang
        FROM temp_table.chenwanying04_jiagou
    ) jg ON t.employee_email_name = jg.employ
    LEFT JOIN (
        SELECT
            uid,
            -- 确保duihuan的字段是纯数字
            CAST(real AS DOUBLE) AS real,
            CAST(refund AS DOUBLE) AS refund,
            CAST(promit AS DOUBLE) AS promit
        FROM temp_table.chenwanying04_duihuan
    ) duihuan ON t.user_number = duihuan.uid
    LEFT JOIN (
        SELECT uid, assistant
        FROM temp_table.chenwanying04_xueyuan1v1
    ) xy ON t.user_number = xy.uid
    WHERE t.clazz_name NOT LIKE '%专家指导课%' OR a.profit_amount IS NOT NULL
) md
LEFT JOIN (
    SELECT
        DISTINCT employee_email_name,
        first_level_department_name,
        second_level_department_name,
        third_level_department_name
    FROM finance_dw.dim_finance_employee_df
    WHERE dt = format_datetime(date_add('day',-1,current_date),'yyyyMMdd')
) ed ON md.employee_email_name1 = ed.employee_email_name

UNION ALL

-- 第二部分：额外数据（与主数据类型严格一致）
SELECT
    duihuan.uid AS "学员ID",
    '' AS "订单编号",
    NULL AS "原始支付时间",
    '' AS "原始订单编号",
    '' AS "一级部门",
    '' AS "二级部门",
    '' AS "三级部门",
    '' AS "归属团队",
    NULL AS refund_time,
    -- 强制转换为DOUBLE，与主数据保持一致
    CAST(duihuan.real AS DOUBLE) AS real_price,
    CAST(duihuan.refund AS DOUBLE) AS refund_price,
    CAST(duihuan.promit AS DOUBLE) AS promit_price,
    '额外处理' AS employee_email_name1,
    CASE WHEN CAST(duihuan.refund AS DOUBLE) > 0 THEN '1' ELSE '0' END AS is_full_refund_order_xin,
    1 AS data_source
FROM temp_table.chenwanying04_duihuan duihuan
WHERE duihuan.uid IN ('5965748450', '3756093952', '4420049790', '4983795249')