写个分割的方法 CREATE FUNCTION f_splitStr( @SourceSql varchar(8000) , @StrSeprate varchar(100) ) RETURNS @temp table( roleId varchar(100) ) AS BEGIN
DECLARE @ch as varchar(100) SET @SourceSql = @SourceSql + @StrSeprate WHILE(@SourceSql <> '') BEGIN SET @ch = left(@SourceSql,charindex(',',@SourceSql,1)-1) INSERT @temp values(@ch) SET @SourceSql = stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') END RETURNEND 比如@Ids = 1,2,3,4,5,6,,, select * from xxx where id in (SELECT * FROM f_splitStr(@Ids,','))
SELECT * FROM users WHERE userid IN (SELECT * FROM TABLE(CAST(my_split('abc,def',',') AS myarray)));
以LZ的资历不应该问这样的问题啊 难道是 要是LZ是想用hibernate的hql语句那我就提供另外一种方法String hql="select * from xxx where id=:id"; for(int i=0;i<arrayid.length;i++){ q.setInteger("id",arrayid[i]); }一楼的方法也是可行的
StringBuffer sb = new StringBuffer();
String[] dir = new String [100];
for(int i=0;i <dir.length;i++){
if(i == dir.length-1){
sb.append(dir[i]);
}else{
sb.append(dir[i] + ",");
}}String sql="select * from table where column_name in ("+sb.toString()+")";
这样可以在select里直接调用
===========================================================
CREATE FUNCTION f_splitStr(
@SourceSql varchar(8000) ,
@StrSeprate varchar(100)
)
RETURNS @temp table( roleId varchar(100) )
AS
BEGIN
DECLARE @ch as varchar(100)
SET @SourceSql = @SourceSql + @StrSeprate
WHILE(@SourceSql <> '')
BEGIN
SET @ch = left(@SourceSql,charindex(',',@SourceSql,1)-1)
INSERT @temp values(@ch)
SET @SourceSql = stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
END
RETURNEND
比如@Ids = 1,2,3,4,5,6,,,
select * from xxx where id in (SELECT * FROM f_splitStr(@Ids,','))
难道是
要是LZ是想用hibernate的hql语句那我就提供另外一种方法String hql="select * from xxx where id=:id";
for(int i=0;i<arrayid.length;i++){
q.setInteger("id",arrayid[i]);
}一楼的方法也是可行的