.net后台创建临时表,并查询过滤临时表中的数据.
问题:
 通过查询现有的表将查询出来的数据插入到临时表中,再通过不同的条件查询临时表中的数据返回相应的结果.(必须是用代码写出来的)


------解决方案--------------------
不知道是什么样的临时表？
------解决方案--------------------
select * into #temptable from UserInfo where UserName='Chinajiyong'

select * from #temptable where Condition

drop table #temptable
  
操作频繁的话，可以写出存储过程，C#执行存储过程我就不说了
------解决方案--------------------
干嘛非得一条语句完成，你执行三次sql不行吗，要不就写存储过程，当然每条语句以 换行分隔，sqlserver是可以识别的
------解决方案--------------------
CREATE GLOBAL TEMPORARY TABLE 是oracle的写法...
------解决方案--------------------
我项目中实际的存储过程，临时表加游标，自己看吧
ALTER PROCEDURE [dbo].[up_CutScore_View]
(
@userid char(19),
@IsMp int,
@isexchange int,
@MpCutScoreId char(19),
@TotalCutScore decimal(12,5)
)
AS
BEGIN
declare @sqlcursor nvarchar(500)

declare @id char(19)
declare @MPCutScoreDetailId char(19)
declare @reguser char(19)
declare @mprule char(19)
declare @CutScore decimal(12,5)
--创建临时表
create table #SubMpScoreTable(
MPCutScoreDetailId char(19),
CutScore decimal(12,5),
MpRule char(19),
reguser char(19),
IsMp bit,
IsExchange bit,
createtime datetime
)

--声明游标
if(@IsMp=1 and @isexchange=1)
begin
set @sqlcursor='declare GkMpExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by createon asc'
end
else if(@IsMp=1 and @isexchange=0)
begin
set @sqlcursor='declare xxxExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by isexchange asc,createon asc'
end
else if(@IsMp=0 and @isexchange=0)
begin
set @sqlcursor='declare xxxExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by createon asc'
end

exec (@sqlcursor)
print @sqlcursor
--打开游标
open xxxExchange_cursor
--循环数据
fetch next from xxxExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore
WHILE (@@FETCH_STATUS=0 and @TotalCutScore>0)
begin
if @CutScore<=@TotalCutScore
begin
set @TotalCutScore=@TotalCutScore-@CutScore
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,CreateTime) values(@MPCutScoreDetailId,@CutScore,@MpRule,@userid,@IsMp,@isexchange,getdate())
end
else
begin
set @CutScore=@CutScore-@TotalCutScore
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,CreateTime) values(@MPCutScoreDetailId,@TotalCutScore,@MpRule,@userid,@IsMp,@isexchange,getdate())
set @TotalCutScore=0
end
print @TotalCutScore
fetch next from xxxExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore
end

--关闭，释放游标资源
close xxxExchange_cursor
DEALLOCATE xxxExchange_cursor
select * from #SubMpScoreTable
drop table #SubMpScoreTable
END