sqlserver临时表的使用

来源：互联网发布：j to e开发java模式编辑：程序博客网时间：2024/05/17 01:02

varchar(n)
长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n 个字节。

nvarchar(n)
包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1 与 4,000 之间。字节的存储大小是所输入字符个数的两倍。

临时表自动创建

使用完毕自动销毁

临时表不能使用IDENTITY_INSERT ON 允许标识列插入显式值

select * into #temp_all_project_type from ZJTower_Fee_project_Type where id in(select id from ZJTower_Fee_project_Type zfpt

where 'Region:'+regionId=(select ParentRegionID from RegionView where ObjectID='ZJHZ')
or regionId='ZJHZ' or regionId='')

使用临时表可以重用结果集，简化java代码中的sql，提高sql执行效率？

ConnectionBuilder.build( "db" ).createQuery()
.select( " * into #temp_all_project_type from ZJTower_Fee_project_Type where id in(select id from ZJTower_Fee_project_Type zfpt " +
" where 'Region:'+regionId=(select ParentRegionID from RegionView where ObjectID=?)" +
"or regionId=? or regionId=''); select case when bt.parentId='-1' then 'aa' when bt.parentId='-2' then 'bb' when bt.parentId='-3' then 'cc' end profession"
,"bt.[name] btName","st.[name] stName","up.unitPrice" )
.from( "#temp_all_project_type bt left join #temp_all_project_type st on bt.id = st.parentId" +
" left join [ZJTower_Fee_UnitPrice_demand] up on up.smallType = st.name and up.bigType = bt.name and up.county=?" )
.where( " bt.parentId "+ aConditons +"order by profession,btName,stName")
.condition( countyId,countyId,countyId,fixCondVal ).doQuery( new SqlQueryer<Integer>() {
@Override
public Integer apply(Query aQuery, ResultSet aResultSet) throws SQLException {
Profession pf = new Profession();
BigType bt = new BigType();
//pf.setBigProgramsList( new ArrayList<BigType>() );
while(aResultSet.next()){
SmallType st = new SmallType();
st.setUnitPrice( aResultSet.getString( "unitPrice" ) );
st.setName( aResultSet.getString( "stName" ) );

String tempProfession = aResultSet.getString( "profession" );
String tempBtName = aResultSet.getString( "btName" );

if(tempProfession.equals( pf.getName() )==false){
//init profession
pf = new Profession();
pf.setName( tempProfession );
pf.setBigProgramsList( new ArrayList<BigType>() );
//set professinList
List<Profession> pfList = res.getProfessionList();
pfList.add( pf );
res.setProfessionList( pfList );
}
if(tempBtName.equals( bt.getName() )==false){
//init bigProgram
bt = new BigType();
bt.setName( tempBtName );
bt.setSmallProgramsList( new ArrayList<SmallType>() );
//set bigProgramList
List<BigType> bpList = pf.getBigProgramsList();
bpList.add( bt );
pf.setBigProgramsList( bpList );
}
//set smallProgramList
List<SmallType> spList = bt.getSmallProgramsList();
spList.add( st );
bt.setSmallProgramsList( spList );
}
return 1;
}
});

0 0