[求教] － SQL 语句,也许你们觉得很容易。

[雷肿么了]

--求一SQL
CREATE TABLE #TMP
(
  COLUMN1 VARCHAR(20)
)

INSERT INTO #TMP SELECT 'A1'
INSERT INTO #TMP SELECT 'A10'
INSERT INTO #TMP SELECT 'A22'
INSERT INTO #TMP SELECT 'B5'
INSERT INTO #TMP SELECT 'B6'
INSERT INTO #TMP SELECT 'B55'
INSERT INTO #TMP SELECT '01'
INSERT INTO #TMP SELECT '05'
INSERT INTO #TMP SELECT '15'

--DROP TABLE #TMP
SELECT * FROM #TMP

-----查询选择有 A,B,0 ----
--选择 A 查询COLUMN1 中以A 开头的数据
--选择 B 查询COLUMN1 中以B 开头的数据
--选择 0 查询COLUMN1 中以0 开头的数据

--比如  选择A 查询 1~10 之间的数据，那么就只能查出  A1 ,A10  其他的同样道理

[快溜]

select * from tb where left(COLUMN1,1)='A'
              and cast(right(COLUMN1,len(COLUMN1)-1) as int) between 1 and 10

[找茬党]

楼主没说明白，如果有A22，那选A要不要查出来？

[cd731107]

select * from #TMP 
where left(COLUMN1,1)='A' 
and cast(replace(COLUMN1,'A','') as int) between 1 and 10

/*
COLUMN1              
-------------------- 
A1
A10

（所影响的行数为 2 行）
*/

[找茬党]

还是仅仅是
select * from tb where COLUMN1 like 'A%'

[找茬党]

left(COLUMN1,1)='A' 这种写法最好写成COLUMN1 like 'A%'

[chuanzhang5687]

select * from tb where left(COLUMN1,1)='A'
              and cast(right(COLUMN1,len(COLUMN1)-1) as int) between 1 and 10

[shadowdudu]

CREATE PROCEDURE P_#TMP
@i varchar (10),--查询A B 0
@k int, --取值上限
@l int --取值下限
AS
EXEC('select * from #TMP 
where left(COLUMN1,1)='''+@i+'''
and cast(replace(COLUMN1,'''+@i+''','''') as int) between '+@k+' and '+@l+'')
--调用
EXEC P_#TMP 'A',1,10

--结果
COLUMN1
A1
A10

[雷肿么了]

[Quote=引用 2 楼 i_element 的回复:]

楼主没说明白，如果有A22，那选A要不要查出来？
[/Quote]

如果选择查 A类型 查询 1~10之内的数据的话，A22是不能查出来的。

楼上已经贴出代码：
查A类型：
select * from tb where left(COLUMN1,1)='A'
and cast(right(COLUMN1,len(COLUMN1)-1) as int) between 1 and 10


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
如果我只查不是已A、B开头的数据，范围1~10 的要怎么查 ?
INSERT INTO #TMP SELECT 'A1'
INSERT INTO #TMP SELECT 'A10'
INSERT INTO #TMP SELECT 'A22'
INSERT INTO #TMP SELECT 'B5'
INSERT INTO #TMP SELECT 'B6'
INSERT INTO #TMP SELECT 'B55'
INSERT INTO #TMP SELECT '1'
INSERT INTO #TMP SELECT '5'
INSERT INTO #TMP SELECT '15'

上面的数据就只能查出 1、5

[cd731107]

CREATE TABLE #TMP
(
  COLUMN1 VARCHAR(20)
)

INSERT INTO #TMP SELECT 'A1'
INSERT INTO #TMP SELECT 'A10'
INSERT INTO #TMP SELECT 'A22'
INSERT INTO #TMP SELECT 'B5'
INSERT INTO #TMP SELECT 'B6'
INSERT INTO #TMP SELECT 'B55'
INSERT INTO #TMP SELECT '1'
INSERT INTO #TMP SELECT '5'
INSERT INTO #TMP SELECT '15'


select * from #TMP 
where ISNUMERIC(COLUMN1)=1  
and cast(COLUMN1 as int) between 1 and 10

/*
COLUMN1
--------------------
1
5

(2 行受影响)
*/

[叶子]

CREATE TABLE #TMP
(
  COLUMN1 VARCHAR(20)
)

INSERT INTO #TMP SELECT 'A1'
INSERT INTO #TMP SELECT 'A10'
INSERT INTO #TMP SELECT 'A22'
INSERT INTO #TMP SELECT 'B5'
INSERT INTO #TMP SELECT 'B6'
INSERT INTO #TMP SELECT 'B55'
INSERT INTO #TMP SELECT '01'
INSERT INTO #TMP SELECT '05'
INSERT INTO #TMP SELECT '15'

declare @param varchar(10)
set @param='A'

SELECT * FROM #TMP where left(COLUMN1,1)=@param
and replace(COLUMN1,@param,'')+0 between 1 and 10
/*
COLUMN1
--------------------
A1
A10
*/
drop table #TMP