Oracle SQL 排序后取第一条记录 没有记录,怎么回事?

2024-12-05 07:49:59
推荐回答(2个)
回答1:

一般是语句用法不对。

如有以下数据:

现在要求按sal列,也就是工资列,取出最小的一条。sql语句如下:

select t.* from
(select emp.*,row_number() over (order by sal) rn from emp) t
where rn=1;

结果如下:

回答2:

你的rownum用法不对,这样写

select
"T0"."Part_Number" "Part_Number",
"T0"."Vendor_Code" "Vendor_Code",
"T0"."Vendor_Name" "Vendor_Name",
"T0"."Region_Code_" "Region_Code_",
"T0"."Allocation__" "Allocation__",
"T0"."Part_Lead_Time_" "Part_Lead_Time_"
from
(select rownum rn,
"TM_PTVN_PART_VENDOR"."CD_PART" "Part_Number", 
"TM_PTVN_PART_VENDOR"."CD_VENDOR" "Vendor_Code", 
"TM_PTVN_PART_VENDOR"."DS_VENDOR_NAME" "Vendor_Name", 
CAST( to_char("TM_PTVN_PART_VENDOR"."CD_REGION") AS CHAR( 2 ) ) "Region_Code_", 
"TM_PTVN_PART_VENDOR"."PC_ALLOCATION" "Allocation__", 
case  when "TM_PTVN_PART_VENDOR"."NR_PART_LT"<0 
then 0 
else "TM_PTVN_PART_VENDOR"."NR_PART_LT"
end  "Part_Lead_Time_"
from "DDWH00_EDW_ALL"."TM_PTVN_PART_VENDOR" "TM_PTVN_PART_VENDOR"
where "TM_PTVN_PART_VENDOR"."FL_VALIDITY"='Y' 
and decode("TM_PTVN_PART_VENDOR"."FL_DUMMY", 'Y', "TM_PTVN_PART_VENDOR"."FL_DUMMY", 'N')='N' 
and "TM_PTVN_PART_VENDOR"."ID_REGN" =3
and "TM_PTVN_PART_VENDOR"."CD_REGION" =3
and "TM_PTVN_PART_VENDOR"."PC_ALLOCATION">=100 
and CAST( to_char("TM_PTVN_PART_VENDOR"."CD_REGION") AS CHAR( 2 ) ) =3
order by "Part_Lead_Time_" asc nulls last
) "T0"
where rn <2