有一张表,我要按条件查询 条件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 之间的数据 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 之间的数据 sql
from tb
where 加工编号 between 'AA2' AND 'AA10'
AA2到AA10不是连续的。between 'AA2' AND 'AA10'结果是:AA10、AA11、AA2
-- 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
*/
select *
from test
where convert(int,substring(加工编号,3,10)) between convert(int,substring('AA2',3,10))
and convert(int,substring('AA10',3,10))
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))
from temp
where right(加工编号,LEN(加工编号)-2) between 2 and 10
;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
*/