Sql

[u010958237]

有一张表,我要按条件查询 条件A到条件B之间的数据 Sql语句该怎么写
表如下
加工编号 供应商 申请日期 加工金额
AA1 A 2010-1-1 30
AA2 A 2010-1-1 30
AA3 A 2010-1-1 30
AA4 A 2010-1-1 30
AA5 A 2010-1-1 30
AA6 A 2010-1-1 30
AA7 A 2010-1-1 30
AA8 A 2010-1-1 30
AA9 A 2010-1-1 30
AA10 A 2010-1-1 30
AA11 A 2010-1-1 30


查询 AA2 到 AA10 之间的数据

[Landa_Peter]

if object_id('temp') is not null drop table temp
go 
create table temp([加工编号] varchar(4),[供应商] varchar(1),[申请日期] datetime,[加工金额] int)
insert temp
select 'AA1','A','2010-1-1',30 union all
select 'AA2','A','2010-1-1',30 union all
select 'AA3','A','2010-1-1',30 union all
select 'AA4','A','2010-1-1',30 union all
select 'AA5','A','2010-1-1',30 union all
select 'AA6','A','2010-1-1',30 union all
select 'AA7','A','2010-1-1',30 union all
select 'AA8','A','2010-1-1',30 union all
select 'AA9','A','2010-1-1',30 union all
select 'AA10','A','2010-1-1',30 union all
select 'AA11','A','2010-1-1',30
--------------开始查询-------------------------- 
 select * from temp where convert(int,right(加工编号,LEN(加工编号)-2)) between convert(int,right('AA1',LEN('AA1')-2)) and convert(int,right('AA10',LEN('AA10')-2))
 

[让你望见影子的墙]

楼主应该说清楚数据筛选的规则，而不是然人yy。

[让你望见影子的墙]

select * from temp where right(加工编号,LEN(加工编号)-2) between 2 and 10

[Andy__Huang]

这个需要转换成数字

;with cte(加工编号,供应商,申请日期 ,加工金额) as
(
select 'AA1','A','2010-1-1',30 union all
select 'AA2','A','2010-1-1',30 union all
select 'AA3','A','2010-1-1',30 union all
select 'AA4','A','2010-1-1',30 union all
select 'AA5','A','2010-1-1',30 union all
select 'AA6','A','2010-1-1',30 union all
select 'AA7','A','2010-1-1',30 union all
select 'AA8','A','2010-1-1',30 union all
select 'AA9','A','2010-1-1',30 union all
select 'AA10','A','2010-1-1',30 union all
select 'AA11','A','2010-1-1',30
)
select 加工编号,供应商,申请日期 ,加工金额
from 
(
select *,rno=cast(REPLACE(加工编号,'AA','') as int)
from cte
)t
where rno between cast(REPLACE('AA2','AA','') as int) and cast(REPLACE('AA10','AA','') as int)

/*
加工编号	供应商	申请日期	加工金额
AA2	A	2010-1-1	30
AA3	A	2010-1-1	30
AA4	A	2010-1-1	30
AA5	A	2010-1-1	30
AA6	A	2010-1-1	30
AA7	A	2010-1-1	30
AA8	A	2010-1-1	30
AA9	A	2010-1-1	30
AA10	A	2010-1-1	30
*/

[u010958237]

引用 1 楼 DBA_Huangzj 的回复:

select * from tb where 加工编号 between 'AA2' AND 'AA10'

后面还有几个查询条件的 有个日期 也是查 两个日期之间的数据 还有个 供应商 跟 币种

[u010958237]

引用 2 楼 JimShu 的回复:

楼上不对吧。 AA2到AA10不是连续的。between 'AA2' AND 'AA10'结果是：AA10、AA11、AA2

后面还有几个查询条件的 有个日期 也是查 两个日期之间的数据 还有个 供应商 跟 币种 谢了 你们的答案我还没测试的

[u010958237]

引用 6 楼 fredrickhu 的回复:

select * from tb where cast(replace(加工编号,'AA','') as int) between 2 and 10

后面还有几个查询条件的 有个日期 也是查 两个日期之间的数据 还有个 供应商 跟 币种 谢了 你们的答案我还没测试的

[發糞塗牆]

select * from tb where 加工编号 between 'AA2' AND 'AA10'

[JimShu]

楼上不对吧。 AA2到AA10不是连续的。between 'AA2' AND 'AA10'结果是：AA10、AA11、AA2

[Cloud_Hero]

where 加工编号 between 'AA2' AND 'AA9' or 加工编号= 'AA10'

[發糞塗牆]

搞两个号干嘛？

[發糞塗牆]

2楼说的没错，我忽略了字符串中数字处理问题

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-24 11:31:00
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([加工编号] varchar(4),[供应商] varchar(1),[申请日期] datetime,[加工金额] int)
insert [huang]
select 'AA1','A','2010-1-1',30 union all
select 'AA2','A','2010-1-1',30 union all
select 'AA3','A','2010-1-1',30 union all
select 'AA4','A','2010-1-1',30 union all
select 'AA5','A','2010-1-1',30 union all
select 'AA6','A','2010-1-1',30 union all
select 'AA7','A','2010-1-1',30 union all
select 'AA8','A','2010-1-1',30 union all
select 'AA9','A','2010-1-1',30 union all
select 'AA10','A','2010-1-1',30 union all
select 'AA11','A','2010-1-1',30
--------------开始查询--------------------------

select * from [huang] 
where 加工编号 between 'AA2' AND 'AA9'
UNION ALL 
select * from [huang] 
where 加工编号 ='AA10'
----------------结果----------------------------
/* 
加工编号 供应商  申请日期                    加工金额
---- ---- ----------------------- -----------
AA2  A    2010-01-01 00:00:00.000 30
AA3  A    2010-01-01 00:00:00.000 30
AA4  A    2010-01-01 00:00:00.000 30
AA5  A    2010-01-01 00:00:00.000 30
AA6  A    2010-01-01 00:00:00.000 30
AA7  A    2010-01-01 00:00:00.000 30
AA8  A    2010-01-01 00:00:00.000 30
AA9  A    2010-01-01 00:00:00.000 30
AA10 A    2010-01-01 00:00:00.000 30
*/

[--小F--]

select * from tb where cast(replace(加工编号,'AA','') as int) between 2 and 10

[昵称90天可改一次]

select * 
from test
where convert(int,substring(加工编号,3,10)) between convert(int,substring('AA2',3,10))
	and convert(int,substring('AA10',3,10))