.net后台创建临时表,并查询过滤临时表中的数据.

jeje 2012-06-11 01:26:01
问题:
通过查询现有的表将查询出来的数据插入到临时表中,再通过不同的条件查询临时表中的数据返回相应的结果.(必须是用代码写出来的)
...全文
392 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
jeje 2012-06-11
  • 打赏
  • 举报
回复
自己搞定了不过还是谢谢大家
string endCall = "";
StringBuilder sb = new StringBuilder();
//创建临时表并查询出所有呼入记录
sb.Append("create global temporary table temptable on commit preserve rows as ");
sb.Append("select * from CDRMAIN where ");
sb.Append(" Direction=1 ");
sb.Append(timesql.ToString());

OracleConnection con = new OracleConnection(DBHelper.connectionStringAltigen);
con.Open();
OracleCommand comd = new OracleCommand(sb.ToString(), con);
comd.CommandTimeout = 240;
comd.ExecuteNonQuery();
//总的排队等待数(过滤重复)
comd.CommandText = "select count(sessionid) from (select distinct(sessionid) from temptable where QueueDuration>0)";
int Sum = Convert.ToInt32(comd.ExecuteScalar());
//排队接通数
comd.CommandText = "select count(sessionid) from (select distinct(sessionid) from temptable where QueueDuration>0 and sessioinid in (select sessionid from temptable where TalkDuration>0) )";
int inRecordeCount = Convert.ToInt32(comd.ExecuteScalar());
//排队挂机数(总排队数-排队接通数)
endCall = (Convert.ToInt32(Sum) - Convert.ToInt32(inRecordeCount)).ToString();
  • 打赏
  • 举报
回复
我项目中实际的存储过程,临时表加游标,自己看吧
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
jyh070207 2012-06-11
  • 打赏
  • 举报
回复
CREATE GLOBAL TEMPORARY TABLE 是oracle的写法...
bdmh 2012-06-11
  • 打赏
  • 举报
回复
干嘛非得一条语句完成,你执行三次sql不行吗,要不就写存储过程,当然每条语句以 换行分隔,sqlserver是可以识别的
jeje 2012-06-11
  • 打赏
  • 举报
回复
流程如下.请写出C#的操作代码
1.创建临时表
2.将查询的数据插入到临时表中.
3.按条件查询临时表.

如下一次性操作完成上面的流程.
string strSql = " CREATE GLOBAL TEMPORARY TABLE servlog ( StartTime int ,EndTime int,LocalDay int,SessionID int,SequenceID int,Direction int, CallerNum varchar(41),TargetNum varchar(41),TalkDuration int,TargetWGNum int,IVRData varchar(255),RingDuration int ) ON COMMIT PRESERVE ROWS;";
strSql = " insert into servlog select StartTime,EndTime,LocalDay,SessionID,SequenceID,Direction,CallerNum,TargetNum,TalkDuration,TargetWGNum,IVRData,RingDuration ";
strSql += " FROM altigen2.CDRMAIN where 1=1 ";
strSql += sSQLstr;
strSql += " ORDER BY sessionid DESC, sequenceid ASC";
EnForGrass 2012-06-11
  • 打赏
  • 举报
回复
select * into #temptable from UserInfo where UserName='Chinajiyong'

select * from #temptable where Condition

drop table #temptable

操作频繁的话,可以写出存储过程,C#执行存储过程我就不说了
xuan.ye 2012-06-11
  • 打赏
  • 举报
回复
不知道是什么样的临时表?

62,243

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧