Question

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

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 "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" =3and "TM_PTVN_PART_VENDOR"."CD_REGION" =3and "TM_PTVN_PART_VENDOR"."PC_ALLOCATION">=100 and CAST( to_char("TM_PTVN_PART_VENDOR"."CD_REGION") AS CHAR( 2 ) ) =3order by "Part_Lead_Time_" asc nulls last) "T0"where rownum <2

Answer 1

一般是语句用法不对。

如有以下数据：

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

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

结果如下：

Answer 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