CAST(s.register_capital AS 数字)
时间: 2024-04-01 21:36:54 浏览: 11
这是一条 SQL 语句,用于将字符串类型的 s.register_capital 转换为数字类型。CAST 是 SQL 中的一个函数,用于将一个数据类型转换成另一个数据类型。在这个例子中,s.register_capital 是一个字符串类型的字段,通过 CAST 函数将其转换为数字类型。具体的转换规则取决于所使用的数据库管理系统。
相关问题
SELECT * FROM ( SELECT a.POLICY_NO AS businessNo, a.ENDORSE_SEQ_NO AS businessSerialNo, a.TOTAL_SERIAL_NO AS totalSerialNo, a.BILL_TYPE AS billType, a.ISSUE_COMPANY AS companyCode, a.PLAN_CCY AS currency, a.EXCHANGE_RATE AS exchangeRate, a.PLAN_FEE AS totalAmount, a.VAT AS taxAmount, a.BUSINESS_NO AS proposalNo, CONVERT(decimal(16, 2), round(a.PLAN_FEE * a.EXCHANGE_RATE, 2)) AS exchangeTotalAmount, CONVERT(decimal(16, 2), round(a.VAT * a.EXCHANGE_RATE, 2)) AS exchangeTaxAmount, 'P' AS certiType, (CASE a.VAT WHEN '0' THEN 'N' ELSE 'Y' END) AS taxExemptFlag, a.PAY_NO AS payNo, ( SELECT top 1 g.LOSS_NO FROM GPLOSSFEE g WHERE g.POLICY_NO = a.POLICY_NO) AS lossNo FROM GPPOLICYPLAN a LEFT JOIN ( SELECT t.POLICY_NO, t.BUSINESS_SEQNO, t.PAY_NO, t.FEE_TYPE_CODE, t.TOTAL_SERIAL_NO FROM GPINPUTVATINVOICEREL t, GPPOLICYPLAN b WHERE t.BUSINESS_NO = b.POLICY_NO AND t.BUSINESS_SEQNO = b.ENDORSE_SEQ_NO AND t.PAY_NO = b.PAY_NO AND t.FEE_TYPE_CODE = b.BILL_TYPE AND t.TOTAL_SERIAL_NO = b.TOTAL_SERIAL_NO) p ON a.POLICY_NO = p.POLICY_NO AND a.ENDORSE_SEQ_NO = p.BUSINESS_SEQNO AND a.PAY_NO = p.PAY_NO AND a.BILL_TYPE = p.FEE_TYPE_CODE AND a.TOTAL_SERIAL_NO = p.TOTAL_SERIAL_NO WHERE (a.PAY_NO != p.PAY_NO OR p.PAY_NO IS NULL) AND a.PLAN_FEE < 0 AND a.BILL_TYPE != 'R10' AND a.COMPANY_CODE IN ( '729021' , '729020' , '729023' , '729022' , '729000' , '729061' , '729060' , '729041' , '729040' , '729081' , '729080' , '729079' , '729010' , '729031' , '729078' , '729077' , '729011' , '729050' , '729072' , '729071' , '729074' , '729052' , '729030' , '729051' , '729073' , '729070' , '86' ) ) WHERE rownum <= 5 oracl怎么改写
这个SQL查询语句可以改写为以下形式:
```
SELECT *
FROM (
SELECT a.POLICY_NO AS businessNo,
a.ENDORSE_SEQ_NO AS businessSerialNo,
a.TOTAL_SERIAL_NO AS totalSerialNo,
a.BILL_TYPE AS billType,
a.ISSUE_COMPANY AS companyCode,
a.PLAN_CCY AS currency,
a.EXCHANGE_RATE AS exchangeRate,
a.PLAN_FEE AS totalAmount,
a.VAT AS taxAmount,
a.BUSINESS_NO AS proposalNo,
CAST(ROUND(a.PLAN_FEE * a.EXCHANGE_RATE, 2) AS DECIMAL(16, 2)) AS exchangeTotalAmount,
CAST(ROUND(a.VAT * a.EXCHANGE_RATE, 2) AS DECIMAL(16, 2)) AS exchangeTaxAmount,
'P' AS certiType,
CASE a.VAT
WHEN '0' THEN 'N'
ELSE 'Y'
END AS taxExemptFlag,
a.PAY_NO AS payNo,
(
SELECT TOP 1 g.LOSS_NO
FROM GPLOSSFEE g
WHERE g.POLICY_NO = a.POLICY_NO
) AS lossNo
FROM GPPOLICYPLAN a
LEFT JOIN (
SELECT t.POLICY_NO,
t.BUSINESS_SEQNO,
t.PAY_NO,
t.FEE_TYPE_CODE,
t.TOTAL_SERIAL_NO
FROM GPINPUTVATINVOICEREL t
INNER JOIN GPPOLICYPLAN b ON t.BUSINESS_NO = b.POLICY_NO
AND t.BUSINESS_SEQNO = b.ENDORSE_SEQ_NO
AND t.PAY_NO = b.PAY_NO
AND t.FEE_TYPE_CODE = b.BILL_TYPE
AND t.TOTAL_SERIAL_NO = b.TOTAL_SERIAL_NO
) p ON a.POLICY_NO = p.POLICY_NO
AND a.ENDORSE_SEQ_NO = p.BUSINESS_SEQNO
AND a.PAY_NO = p.PAY_NO
AND a.BILL_TYPE = p.FEE_TYPE_CODE
AND a.TOTAL_SERIAL_NO = p.TOTAL_SERIAL_NO
WHERE (a.PAY_NO != p.PAY_NO OR p.PAY_NO IS NULL)
AND a.PLAN_FEE < 0
AND a.BILL_TYPE != 'R10'
AND a.COMPANY_CODE IN ('729021', '729020', '729023', '729022', '729000', '729061', '729060', '729041', '729040', '729081', '729080', '729079', '729010', '729031', '729078', '729077', '729011', '729050', '729072', '729071', '729074', '729052', '729030', '729051', '729073', '729070', '86')
ORDER BY ROWNUM
)
WHERE ROWNUM <= 5
```
主要的改动包括:
- 将转换数据类型的函数 CONVERT 改写为 CAST。
- 将子查询的 TOP 1 改成了 Oracle 中的 ROWNUM。
- 将 LEFT JOIN 改为 INNER JOIN,并将 JOIN 条件移到了 ON 子句中。
insert overwrite table discountdw.dwd_sd_adds_order_bill_inc partition(dt = '2023-06-06') select t1.order_bill_id, t1.counterfoil_no, t1.acceptor, date_format(to_utc_timestamp(cast(t1.expiry_date as bigint) ,'GMT-8'),'YYYY-MM-dd'), t2.company_id, t1.cert_no, t1.company_name, t1.third_order_id, t1.counterfoil_amt/10000, t1.transaction_amt/10000, t1.rate, '3bp' as service_tariffing, ((DATEDIFF(to_utc_timestamp(t1.expiry_date ,'GMT-8'),to_utc_timestamp(t1.transaction_date ,'GMT-8') ) + adjust_days)* 0.0003 *(counterfoil_amt))/ 360 as service_fee, 360 as total_days, DATEDIFF(to_utc_timestamp(t1.expiry_date ,'GMT-8'),to_utc_timestamp(t1.transaction_date ,'GMT-8') ) + adjust_days as modulation_date, t3.channel_type, t3.bank_name, date_format(to_utc_timestamp(cast(t1.transaction_date as bigint) ,'GMT-8'),'YYYY-MM-dd'), t1.order_status_code, t1.order_status_msg, t4.fee_amt, t4.status, t1.tenant_id, t5.revenue, to_utc_timestamp(cast(t1.create_date as bigint) ,'GMT-8'), to_utc_timestamp(cast(t1.update_date as bigint) ,'GMT-8') from (select * from discountdw.ods_adds_order_bill_inc where dt ='2023-06-06' and channel_id=101110004 )t1 left join (select * from mecdw.ods_company_full where platform_id='sdpjw')t2 on t1.cert_no=t2.cert_no and t1.tenant_id=t2.tenant_id left join discountdw.dim_adds_product_full t3 on t1.partner_id=t3.partner_id and t1.product_id=t3.product_id left join (select * from mecdw.dwd_sc_fee_record_full where dt='2023-06-06' and biz_type=2 ) t4 on t1.order_bill_id=t4.third_id left join (select * from discountdw.ods_sd_order_ext_inc where dt='2023-06-06') t5 on t1.order_bill_id=t5.order_bill_id left join sdpjwdw.dim_holiday_info_full t6 on date_format(to_utc_timestamp(t1.expiry_date ,'GMT-8'),'YYYY-MM-dd') = t6.civic_holiday ;
这是一条 SQL 语句,看起来比较长。以下是一些可以考虑优化的建议:
1. 将表名、字段名等缩短,以减少字符数和提高可读性。
2. 将复杂的表连接操作拆分成多个子查询,以降低查询复杂度。
3. 对于一些计算操作,可以考虑使用内置函数或自定义函数,以简化代码并提高可维护性。
4. 对于一些重复的操作,可以将其提取成变量或常量,以减少重复代码。
综上所述,优化后的 SQL 可能类似于以下代码(仅供参考):
```
-- 定义常量
SET adjust_days = 1;
-- 查询订单表
WITH order_data AS (
SELECT
o.order_bill_id,
o.counterfoil_no,
o.acceptor,
DATE_FORMAT(TO_UTC_TIMESTAMP(CAST(o.expiry_date AS BIGINT), 'GMT-8'), 'YYYY-MM-dd') AS expiry_date,
o.cert_no,
o.company_name,
o.third_order_id,
o.counterfoil_amt / 10000 AS counterfoil_amt,
o.transaction_amt / 10000 AS transaction_amt,
o.rate,
'3bp' AS service_tariffing,
((DATEDIFF(TO_UTC_TIMESTAMP(o.expiry_date, 'GMT-8'), TO_UTC_TIMESTAMP(o.transaction_date, 'GMT-8')) + adjust_days) * 0.0003 * (o.counterfoil_amt)) / 360 AS service_fee,
360 AS total_days,
DATEDIFF(TO_UTC_TIMESTAMP(o.expiry_date, 'GMT-8'), TO_UTC_TIMESTAMP(o.transaction_date, 'GMT-8')) + adjust_days AS modulation_date,
o.order_status_code,
o.order_status_msg,
o.tenant_id,
TO_UTC_TIMESTAMP(CAST(o.create_date AS BIGINT), 'GMT-8') AS create_date,
TO_UTC_TIMESTAMP(CAST(o.update_date AS BIGINT), 'GMT-8') AS update_date
FROM discountdw.ods_adds_order_bill_inc o
WHERE o.dt = '2023-06-06' AND o.channel_id = 101110004
),
-- 查询公司表
company_data AS (
SELECT
c.company_id,
c.cert_no,
c.tenant_id
FROM mecdw.ods_company_full c
WHERE c.platform_id = 'sdpjw'
),
-- 查询产品表
product_data AS (
SELECT
p.partner_id,
p.product_id,
p.channel_type,
p.bank_name
FROM discountdw.dim_adds_product_full p
),
-- 查询费用记录表
fee_data AS (
SELECT
f.third_id,
f.fee_amt,
f.status
FROM mecdw.dwd_sc_fee_record_full f
WHERE f.dt = '2023-06-06' AND f.biz_type = 2
),
-- 查询订单扩展信息表
order_ext_data AS (
SELECT
e.order_bill_id,
e.revenue
FROM discountdw.ods_sd_order_ext_inc e
WHERE e.dt = '2023-06-06'
),
-- 查询节假日表
holiday_data AS (
SELECT
h.civic_holiday
FROM sdpjwdw.dim_holiday_info_full h
)
-- 合并数据
INSERT OVERWRITE TABLE discountdw.dwd_sd_adds_order_bill_inc PARTITION(dt = '2023-06-06')
SELECT
o.order_bill_id,
o.counterfoil_no,
o.acceptor,
o.expiry_date,
c.company_id,
o.cert_no,
o.company_name,
o.third_order_id,
o.counterfoil_amt,
o.transaction_amt,
o.rate,
o.service_tariffing,
o.service_fee,
o.total_days,
o.modulation_date,
p.channel_type,
p.bank_name,
DATE_FORMAT(TO_UTC_TIMESTAMP(CAST(o.transaction_date AS BIGINT), 'GMT-8'), 'YYYY-MM-dd'),
o.order_status_code,
o.order_status_msg,
f.fee_amt,
f.status,
o.tenant_id,
e.revenue,
o.create_date,
o.update_date
FROM order_data o
LEFT JOIN company_data c ON o.cert_no = c.cert_no AND o.tenant_id = c.tenant_id
LEFT JOIN product_data p ON o.partner_id = p.partner_id AND o.product_id = p.product_id
LEFT JOIN fee_data f ON o.order_bill_id = f.third_id
LEFT JOIN order_ext_data e ON o.order_bill_id = e.order_bill_id
LEFT JOIN holiday_data h ON o.expiry_date = DATE_FORMAT(TO_UTC_TIMESTAMP(h.civic_holiday, 'GMT-8'), 'YYYY-MM-dd');
```
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)