在SQL Server中选择Top 1。

Please help me in select top 1

请帮我选择top 1

The data like this

这样的数据

Code    Amp      Price
-----------------------
00001   10       1000
00002   75-100   1500
00003   50-60    1200
00004   15       1100

Note : datatype for column Amp is VarChar

注意:列Amp的数据类型为VarChar

I want to select with Amp 75 and I want get the price is 1500

我想用Amp 75来选择，我想要的价格是1500

So I use this statement:

所以我用了这句话:

SELECT TOP 1 * 
FROM Cable 
WHERE (Amp <= '75') 
ORDER BY Amp DESC

but the result price I get is 1200 is record with code : 00003 (wrong), actually I want the result is code : 00002 and the price is 1500

但是我得到的结果价格是1200是用代码记录的:00003(错误)，实际上我想要的结果是代码:00002，价格是1500

But if I want to select with Amp 76 the result is true with the syntax :

但是如果我想用Amp 76来选择结果是正确的

SELECT TOP 1 * 
FROM Cable 
WHERE (Amp <= '75') 
ORDER BY Amp DESC

What is the true select for my case? Please help me

对我来说，什么是真正的选择?请帮我

5 个解决方案

#1

Just about any parse/split function will do, and combined with a Cross Apply, it becomes a small matter

几乎任何解析/拆分函数都可以，并且与交叉应用结合，这就变成了一件小事

-- Easy to do without a parse function

——不使用解析函数很容易

Declare @Cable table (Code varchar(25),Amp varchar(50),Price int)
Insert Into @Cable values
('00001','10',    1000),
('00002','75-100',1500),
('00003','50-60', 1200),
('00004','15',    1100)

Select Top 1 A.*
 From  @Cable A
 Cross Apply [dbo].[udf-Str-Parse](A.Amp,'-') B
 Where RetVal<=76              --<< Notice we are testing for 76
 Order By Price Desc

Returns

返回

Code    Amp     Price
00002   75-100  1500

The UDF if interested

UDF如果感兴趣

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

#2

If you have to work with this existing datatype and table structure then below query may work for you.

如果您必须使用现有的数据类型和表结构，那么下面的查询可能适合您。

SELECT TOP 1 *
FROM Cable
WHERE (SUBSTRING(Amp,1,IIF((CHARINDEX('-',Amp)-1)>0,(CHARINDEX('-',Amp)-1),0 ) ) <=75)
ORDER BY Amp DESC

#3

The problem is that SQL Server is not going to sort a varchar column like an int.

问题是SQL Server不会像int那样对varchar列进行排序。

Sorting issue example:

排序问题的例子:

select *
from (
    select '125' as nbr
    union all
    select '24' as nbr
    ) as a
order by a.nbr asc

1 is less than 2 (the first character in each nbr), so it will sort thinking that 125 < 24 (not true), even though it looks pretty simple to anyone that 24 should show up first, which is how it would be sorted if the datatype of the column were an int.

1小于2(每个nbr中第一个字符),因此它会认为125 < 24(不正确),即使它看起来很简单的人,24应该先出现,这是它将排序如果该列的数据类型是int。

What needs to happen is to split the amp column into ranges, or max and min. Using the - as the delimeter, you can use charindex to split the numbers up and cast them as ints instead.

需要做的是将amp列分割成范围，或者max和min，使用-作为递归表，您可以使用charindex将数字分割开来，并将它们转换为int类型。

Sample Data Setup:

样本数据设置:

declare @cable table
    (
        code char(5) not null
        , amp varchar(10) not null
        , price int not null
    )

insert into @cable
values 
        ('00001','10'       ,10000),
        ('00002','75-100'   ,15000),
        ('00003','50-60'    ,12000),
        ('00004','15'       ,11000)

Answer:

答:

declare @amp_nbr int = 75

select top 1 *
from (  
    select c.code
    , cast(iif(charindex('-', c.amp, 0) > 0, left(c.amp, charindex('-', c.amp, 0) - 1), c.amp) as int) as amp_min
    , cast(iif(charindex('-', c.amp, 0) > 0, right(c.amp, len(c.amp) - charindex('-', c.amp, 0)), c.amp) as int) as amp_max
    , c.price
    from @cable as c
    ) as a
where 1=1
and @amp_nbr between a.amp_min and a.amp_max
order by a.amp_min desc

After that, a simple between constraint in the where clause will do the trick.

在此之后，where子句中的一个简单的between约束将发挥作用。

#4

Thank's for all answers. I decides to type all data and change it with two field.

感谢的所有答案。我决定输入所有的数据，并用两个字段进行更改。

Code Amp1 Amp2 Price

00001 10 10 1000 00002 75 100 1500 00003 50 60 1200 00004 15 15 1100

00001 10 10 1000 00002 75 100 1500 00003 50 60 1200 00004 15 15 1100。

The single value i type same in field Amp2 and then i use the syntax : SELECT * FROM Cable WHERE (65 BETWEEN Amp1 AND Amp2)

在字段Amp2中输入相同的单值，然后使用语法:从Cable中选择*(在Amp1和Amp2之间为65)

#5

If you are using SQL Server 2008 and later, try something like this:

如果您正在使用SQL Server 2008或更高版本，请尝试以下操作:

SELECT TOP 1 * 
FROM Cable 
WHERE isnumeric(left(Amp, 2)) = 1 and cast(left(Amp, 2) as int) <= 75
and Price = 1500 
ORDER BY Amp DESC

Note: This will work only if you have no records with Amp less than 10.

注意:只有在没有Amp小于10的记录时，这个操作才会有效。