业务场景
需要查询某数据,由于需要三张表关联查询,查询结果如下:
原始SQL语句
SELECT
D.ORDER_NUM AS "申请单号" ,
D.CREATE_TIME ,
D.EMP_NAME AS "申请人",
(SELECT extractvalue(t1.row_data,'/root/row/FI13_wasteName')
FROM dat_table_row t1
WHERE d.document_id = t1.document_id
AND t1.table_id = 'dynamicRowsIdPTFLXX'
) AS "废料名称",
(SELECT extractvalue(t1.row_data,'/root/row/FI13_units')
FROM dat_table_row t1
WHERE d.document_id = t1.document_id
AND t1.table_id = 'dynamicRowsIdPTFLXX'
) AS "单位",
(SELECT extractvalue(t1.row_data,'/root/row/FI13_estimate')
FROM dat_table_row t1
WHERE d.document_id = t1.document_id
AND t1.table_id = 'dynamicRowsIdPTFLXX'
) AS "预估数量",
(SELECT extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
FROM dat_table_row t1
WHERE d.document_id = t1.document_id
AND t1.table_id = 'dynamicRowsIdPTFLXX'
) AS "累计出库数量",
(SELECT extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
FROM dat_table_row t1
WHERE d.document_id = t1.document_id
AND t1.table_id = 'dynamicRowsIdCGYTX'
) AS "收购方收货时间",
(SELECT extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
FROM dat_table_row t2
WHERE d.document_id = t2.document_id
AND t2.table_id = 'dynamicRowsIdPTSJSKSJ'
) AS "实际收款时间"
FROM dat_document d,
dat_table_row dtr
WHERE d.form_name ='FI14'
AND d.document_id =dtr.document_id
AND (D.DOCUMENT_STATUS != 'deleted'
OR D.DOCUMENT_STATUS IS NULL )
--AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
AND d.order_num = 'FI1420210708002' --FI1420210708002
ORDER BY d.CREATE_TIME DESC;
一、distinct去重
select
distinct d.order_num as "申请单号" ,
d.create_time ,
d.emp_name as "申请人",
(select extractvalue(t1.row_data,'/root/row/FI13_wasteName')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "废料名称",
(select extractvalue(t1.row_data,'/root/row/FI13_units')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "单位",
(select extractvalue(t1.row_data,'/root/row/FI13_estimate')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "预估数量",
(select extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "累计出库数量",
(select extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdCGYTX'
) as "收购方收货时间",
(select extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
from dat_table_row t2
where d.document_id = t2.document_id
and t2.table_id = 'dynamicRowsIdPTSJSKSJ'
) as "实际收款时间"
from dat_document d,
dat_table_row dtr
where d.form_name ='FI14'
and d.document_id =dtr.document_id
and (d.document_status != 'deleted'
or d.document_status is null )
--AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
and d.order_num = 'FI1420210708002' --FI1420210708002
order by d.create_time desc;
注意:DISTINCT后面要跟ORDER BY的字段,Oracle先执行 DISTINCT去重后,再使用ORDER BY进行排序的。所以如果在ORDER BY需要排序的字段,没有在 distinct 后的字段中,自然会抛错。
报错信息如下
二、row_number() over()
语法格式
select * from
(select A.*, row_number() over(partition by A.name1 order by A.name12 desc) rn from A)
where rn = 1
select * from (
select
d.order_num as "申请单号" ,
d.create_time ,
d.emp_name as "申请人",
(select extractvalue(t1.row_data,'/root/row/FI13_wasteName')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "废料名称",
(select extractvalue(t1.row_data,'/root/row/FI13_units')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "单位",
(select extractvalue(t1.row_data,'/root/row/FI13_estimate')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "预估数量",
(select extractvalue(t1.row_data,'/root/row/FI13_stockRemoval')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdPTFLXX'
) as "累计出库数量",
(select extractvalue(t1.row_data,'/root/row/FI13_receivingTime')
from dat_table_row t1
where d.document_id = t1.document_id
and t1.table_id = 'dynamicRowsIdCGYTX'
) as "收购方收货时间",
(select extractvalue(t2.row_data,'/root/row/FI13_collectionTime')
from dat_table_row t2
where d.document_id = t2.document_id
and t2.table_id = 'dynamicRowsIdPTSJSKSJ'
) as "实际收款时间",
row_number() over(partition by d.order_num order by d.create_time desc) rn
from dat_document d,
dat_table_row dtr
where d.form_name ='FI14'
and d.document_id =dtr.document_id
and (d.document_status != 'deleted'
or d.document_status is null )
--AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2021-03-26'
and d.order_num = 'FI1420210708002' --FI1420210708002
) where rn = 1;
查询结果
参考文章
https://www.cnblogs.com/fina1ly/p/9509795.html
https://blog.csdn.net/deniro_li/article/details/79024099