--求一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 其他的同样道理
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 其他的同样道理
解决方案 »
- 新手请教(存储过程返回临时表)
- 讨论一下字符串的问题
- 有一句很怪的SQL,去掉一些条件就可以正常运行,如果把update改成select 也可以,不知什么原因?特请教!谢谢!
- 谁能帮我解决MSSQL,备份还原的问题,(附上备份)解决了,就50元送你买烟抽!
- sql 两个表查询问题,请教
- ms-sql2005,生产环境,不可预料,出现无法连接数据库.
- 存储过程中判断插入是否成功
- 好痛苦啊,我的查询分析器!!!
- 有一个dmp文件但怎么也imp不进去.请帮忙!
- 数据库处于紧急状态,无法用dbcc修复
- 如何把数据库中nvarchar的字段换成日期类型的某一格式显示
- c#在其他子模块中如何获取当前登录模块的用户名
and cast(right(COLUMN1,len(COLUMN1)-1) as int) between 1 and 10
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%'
and cast(right(COLUMN1,len(COLUMN1)-1) as int) between 1 and 10
@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
如果选择查 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
(
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