select max(col1),max(col2) from a where col1<@s1 and col2<@s2
select top 1 * from tb where col1<(case when s1 is not null then s1 else col1+1 end) and col2<(case when s2 is not null then s2 else col2+1 end) order by col1 desc,col2 desc 两个都是最大的"一条记录"取不出来,因为我不能保证在一条记录里,它俩都是最大值.
select * from tb where col1<isnull(s1,col1+1) and col2<isnull(s2,col2+1) order by col1 desc,col2 desc
select max(col1),max(col2) from a where col1<isnull(@s1,col1+1) or col2<isnull(s2,col2+1)
where col1<(case when s1 is not null then s1 else col1+1 end)
and col2<(case when s2 is not null then s2 else col2+1 end)
order by col1 desc,col2 desc
两个都是最大的"一条记录"取不出来,因为我不能保证在一条记录里,它俩都是最大值.
where col1<isnull(s1,col1+1) and col2<isnull(s2,col2+1)
order by col1 desc,col2 desc
where col1<isnull(@s1,col1+1) or col2<isnull(s2,col2+1)
[CCMD_ID] [int] IDENTITY(1,1) NOT NULL,
[S_ID] [int] NULL,
[CM_ID] [int] NULL,
[CCDM_Name] [nvarchar](50) NULL,
[CMP_Mil] [float] NULL,
[CMP_Time] [float] NULL,
[CCMD_Time] [datetime] NULL,
[CCMD_UserID] [bigint] NULL)SET IDENTITY_INSERT [CarCustomMaintenanceData_tbl] ONINSERT [CarCustomMaintenanceData_tbl] ([CCMD_ID],[S_ID],[CM_ID],[CCDM_Name],[CMP_Mil],[CMP_Time]) VALUES ( 1,1,1,N'A套餐',5000,100)
INSERT [CarCustomMaintenanceData_tbl] ([CCMD_ID],[S_ID],[CM_ID],[CCDM_Name],[CMP_Mil],[CMP_Time]) VALUES ( 2,1,1,N'B套餐',10000,200)
INSERT [CarCustomMaintenanceData_tbl] ([CCMD_ID],[S_ID],[CM_ID],[CCDM_Name],[CMP_Mil],[CMP_Time]) VALUES ( 3,1,1,N'C套餐',15000,300)
INSERT [CarCustomMaintenanceData_tbl] ([CCMD_ID],[S_ID],[CM_ID],[CCDM_Name],[CMP_Mil],[CMP_Time]) VALUES ( 4,1,1,N'D套餐',20000,400)SET IDENTITY_INSERT [CarCustomMaintenanceData_tbl] OFF给出2个参数 分别对应 CMP_Mil 和 CMP_Time 其中一个参数可能为空(null or '')
条件1 对应的值必须比给定的参数小
条件2 在所有满足了条件1的记录中取 CMP_Mil 和CMP_Time 最大的记录