where productid between '00002-00002' and '00002-00005'
WHERE CAST(REPLACE(productid,'-',0) AS INT) BETWEEN 2000002 AND 2000005看情况了,如果转换成int时候小那么就int 如果大,那么bigint取决于int范围,java里面是 2147483647不知道SQL是2的几次方
改成bigint時﹐ 查詢'07885-09001' and '07886-09999'范圍時報錯 轉換資料型別從 varchar 到 bigint 時發生錯誤。
直接BETWEEN AND 就OK了。
productid between '00002-00002' and '00002-00005'
一個ID設置成這樣,自己給自己添麻煩! 用REPLANCE把-轉為空化整形!
between and 这样的问题来问?
productid between '00002-00002' and '00002-00005'
where productid between '00002-00002' and '00002-00005'
select * from t1 where productid between '00002-00002' and '00002-00005'
productid between '00002-00002' and '00002-00005'
虽然是字串,但空位已补零,所以直接between and 就可以了,不需要额外处理。不补位的话 '10' 是 小于 '7'的补了位 '10' > '07'
drop table ccssddnn520 create table ccssddnn520(productid varchar(100), productDate dateTime , productName varchar(10)) insert into ccssddnn520 select '00001-00002' , '2009-01-01' , 'A' union all select '00001-00003' , '2009-01-01' , 'A' union all select '00002-00001' , '2009-01-01' , 'A' union all select '00002-00002' , '2009-01-01' , 'A' union all select '00002-00004' , '2009-01-01' , 'A' union all select '00002-00005' , '2009-01-01' , 'A' declare @productid_001 varchar(100),@productid_002 varchar(100) set @productid_001 = '0000200002' set @productid_002 = '0000200005' select * from ccssddnn520 where replace (productid,'-','') between @productid_001 and @productid_002set @productid_001 = '00002-00002' set @productid_002 = '00002-00005' select * from ccssddnn520 where productid between @productid_001 and @productid_002 ================================== productid productDate productName 00002-00002 2009-01-01 00:00:00.000 A 00002-00004 2009-01-01 00:00:00.000 A 00002-00005 2009-01-01 00:00:00.000 A
SQL codeproductid between '00002-00002' and '00002-00005'
select * from t1 where productid in ( '00002-00002' ,'00002-00003','00002-00004', '00002-00005')
select * from t1 where productid between '00002-00002' and '00002-00005'select * from t1 where productid between '00002-00002' and '00002-00005'select * from t1 where productid between '00002-00002' and '00002-00005' select * from t1 where productid between '00002-00002' and '00002-00005' select * from t1 where productid between '00002-00002' and '00002-00005'
直接between... and就可以了呀 select * from R where productid between '00002-00002' and '00002-00005'
WHERE CAST(REPLACE(productid,'-',0) AS INT) BETWEEN 2000002 AND 2000005
執行時會報﹕
將 varchar 數值 '07885-09001' 轉換成資料型別為 int 的資料行語法錯誤。
INT
改為
BIGINT
查詢'07885-09001' and '07886-09999'范圍時報錯
轉換資料型別從 varchar 到 bigint 時發生錯誤。
productid between '00002-00002' and '00002-00005'
用REPLANCE把-轉為空化整形!
这样的问题来问?
drop table ccssddnn520
create table ccssddnn520(productid varchar(100), productDate dateTime , productName varchar(10))
insert into ccssddnn520
select '00001-00002' , '2009-01-01' , 'A' union all
select '00001-00003' , '2009-01-01' , 'A' union all
select '00002-00001' , '2009-01-01' , 'A' union all
select '00002-00002' , '2009-01-01' , 'A' union all
select '00002-00004' , '2009-01-01' , 'A' union all
select '00002-00005' , '2009-01-01' , 'A' declare @productid_001 varchar(100),@productid_002 varchar(100)
set @productid_001 = '0000200002'
set @productid_002 = '0000200005'
select * from ccssddnn520 where replace (productid,'-','') between @productid_001 and @productid_002set @productid_001 = '00002-00002'
set @productid_002 = '00002-00005'
select * from ccssddnn520 where productid between @productid_001 and @productid_002
==================================
productid productDate productName
00002-00002 2009-01-01 00:00:00.000 A
00002-00004 2009-01-01 00:00:00.000 A
00002-00005 2009-01-01 00:00:00.000 A
SQL codeproductid between '00002-00002' and '00002-00005'
地下城与勇士小游戏
武林外传小游戏
QQ连连看
做蛋糕小游戏
QQ小游戏
4399.com拳皇小游戏
select * from t1 where productid between '00002-00002' and '00002-00005'
select * from t1 where productid between '00002-00002' and '00002-00005'
select * from R where productid between '00002-00002' and '00002-00005'