怎样查出字段中间的数字最大的哪一条记录？

Question

字段名称为box_id，该字段的值如下：
tub201-2011
tub32-2010
tub57-2009
tub668-2011
tub17-2011
...
这些值的特征都是tub开头的，后面都是数字，最后一部分是年份
我想找出当前年份（2011年）中间数字最大的那一个，即
select ... where box_id = 'tub668-2011' 的这一条记录，请问用sql能否实现? 还是必须用程序代码来实现？两者效率一样吗？ 

Answer 1

--> --> (Roy)生成測試數據
 
declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub201-2011' union all
select N'tub32-2010' union all
select N'tub57-2009' union all
select N'tub668-2011' union all
select N'tub17-2011'
 
declare @year nvarchar(10)
 set @year=2011
Select  top 1 * from @T where [box_id] like '%'+@year 
order by cast(SUBSTRING(box_id,4,charindex('-',box_id)-4) as int)desc
/*
tub668-2011
*/

Answer 2

或用子查询
--> --> (Roy)生成測試數據
 
declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub201-2011' union all
select N'tub32-2010' union all
select N'tub57-2009' union all
select N'tub668-2011' union all
select N'tub17-2011'
 
declare @year nvarchar(10)
 set @year=2011
Select  
* 
from @T as a
where [box_id] like '%'+@year 
and not exists(select 1 from @T  where RIGHT(box_id,4)=RIGHT(a.box_id,4) 
and cast(SUBSTRING(box_id,4,charindex('-',box_id)-4) as int)>SUBSTRING(a.box_id,4,charindex('-',a.box_id)-4))/*
tub668-2011
*/

Answer 3

原来记得需要效率，改设计吧，拆开个年份字段和数量字段，这样查询很简单而且能利用索引，效率高select top 1 *
from tab
where 年份字段 = 2011
order by 数量字段 desc

Answer 4

create table tb(box_id varchar(20))
insert into tb select 'tub201-2011'
insert into tb select 'tub32-2010'
insert into tb select 'tub57-2009'
insert into tb select 'tub668-2011'
insert into tb select 'tub17-2011'
go
select MAX(convert(int,substring(box_id,4,charindex('-',box_id)-4))) from tb
/*
-----------
668(1 行受影响)*/
go
drop table tb

Answer 5

没看懂，为什么要吧每一条数据写成sql呢？数据有上万条的呀，怎么这么长的sql呀？

Answer 6

先谢谢你，不好意思，我写的值有误，应该是这样的：
box_id 的值
tub27-235-2011
tub131-204-2011
tub7213-75-2008
...
这些值的规律应该是中间那个数字是位于 两个-之间的，这样的话应该怎样写sql 啊？ 还有当前年份的sql应该怎样写啊？我想通过   like ‘-当前年份%’  这样的条件来过滤，不知道怎样获取当前年份呀？

Answer 7

--> --> (Roy)生成測試數據
 
declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub27-235-2011' union all
select N'tub131-204-2011' union all
select N'tub7213-75-2008'
 
declare @year nvarchar(10)
 set @year=2011
Select  
    *
from @T as a
where [box_id] like '%'+@year 
and not exists(select 1 from @T  where RIGHT(box_id,4)=RIGHT(a.box_id,4) 
and cast(SUBSTRING(box_id,charindex('-',box_id)+1,charindex('-',box_id,charindex('-',box_id)+1)-charindex('-',box_id)-1) as int)>SUBSTRING(a.box_id,charindex('-',a.box_id)+1,charindex('-',a.box_id,charindex('-',a.box_id)+1)-charindex('-',a.box_id)-1) )

Answer 8

还有个问题，怎样获取当前年份呀？ 就是  like '%当前年份' 这段应该怎样写啊？获取年份的函数是什么？

Answer 9

create table tb(box_id varchar(20))
insert into tb select 'tub27-235-2011'
insert into tb select 'tub131-204-2011'
insert into tb select 'tub7213-75-2008'
go
select MAX(convert(int,substring(box_id,charindex('-',box_id)+1,LEN(box_id)-5-charindex('-',box_id))))
from tb where RIGHT(box_id,4)=LTRIM(YEAR(getdate()))
/*
-----------
235(1 行受影响)*/
go
drop table tb

Answer 10

like '%'+rtrim(year(getdate()))

Answer 11

declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub27-235-2011' union all
select N'tub131-204-2011' union all
select N'tub7213-75-2008'select max(cast(parsename(replace(box_id , '-' , '.'),2) as int)) from @t where box_id like '%-2011'/*
            
----------- 
235（所影响的行数为 1 行）
*/

Answer 12

declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub27-235-2011' union all
select N'tub131-204-2011' union all
select N'tub7213-75-2008'--2011年
select max(cast(parsename(replace(box_id , '-' , '.'),2) as int)) from @t where box_id like '%-2011'
/*
            
----------- 
235（所影响的行数为 1 行）
*/--当前年份
select max(cast(parsename(replace(box_id , '-' , '.'),2) as int)) from @t where box_id like '%-' + datename(yy,getdate())/*
            
----------- 
235（所影响的行数为 1 行）
*/

Answer 13

将 varchar 值 '1-01' 转换为数据类型为 int 的列时发生语法错误。

Answer 14

我觉得用
right(box_id ,5) = '-2011'
right(box_id ,5) = '-' + datename(yy,getdate())
可能更好.

Answer 15

试了一下，年份的函数可以了，但执行你那段代码还是有点错误，如下：
向 substring 函数传递了无效的 length 参数。

Answer 16

你这段代码很简洁啊，查出来结果也对，但我不是要那个235的数字，我要box_id=tub27-235-2011的这个记录，怎样写啊？

Answer 17

declare @T table([box_id] nvarchar(20))
Insert @T
select N'tub27-235-2011' union all
select N'tub131-204-2011' union all
select N'tub7213-75-2008'--2011年
select top 1 * from @t where box_id like '%-2011' order by cast(parsename(replace(box_id , '-' , '.'),2) as int) desc
/*
box_id               
-------------------- 
tub27-235-2011（所影响的行数为 1 行）
*/select top 1 * from @t where right(box_id,5) = '-2011' order by cast(parsename(replace(box_id , '-' , '.'),2) as int) desc/*
box_id               
-------------------- 
tub27-235-2011（所影响的行数为 1 行）
*/--当前年份
select top 1 * from @t where box_id like '%-' + datename(yy,getdate()) order by cast(parsename(replace(box_id , '-' , '.'),2) as int) desc/*
box_id               
-------------------- 
tub27-235-2011（所影响的行数为 1 行）
*/select top 1 * from @t where right(box_id,5) = '-' + datename(yy,getdate()) order by cast(parsename(replace(box_id , '-' , '.'),2) as int) desc
/*
box_id               
-------------------- 
tub27-235-2011（所影响的行数为 1 行）
*/