请教一个显示变量值问题! set @sql='select P_name,'+@lw_name+' as monthly,'+@lw_name+' as m_value from t_errect'@lw_name为表中字段名称,请问在执行exec(@sql)时,如何将第一个出现的@lw_name对应的字段名称显示在查询中而不是字段对应的值? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 TRYset @sql='select P_name,'''+@lw_name+''' as monthly,'+@lw_name+' as m_value from t_errect' 多谢paoluo指教,如果我想将用变量循环显示出的多个select结果最后用union显示在一个select中,该如何操作,比如:表t_errect结构如下: P_name m1 m2 m3pc1 3 5 8pc2 6 9 12要得到:P_name monthly m_valuepc1 m1 3pc1 m1 5pc1 m1 8pc2 m2 6pc2 m2 9pc2 m2 12declare @m_str varchar(50),@monthly int,@sql varchar(200)set @monthly=1whlie (@monthly<4)begin set @m_str='m'+cast(@monthly as varchar(5)) set @sql='select P_name,''''+@monthly+'''' as monthly,'+@m_str+' as m_value from' +'(select P_name,'+@m_str+' as m_value) a' exec(@sql) set @monthly=@monthly+1end这样做得出的结果分别有3个select结果,如何将这些结果都集中在一个select中显示出来? 多谢paoluo指教,如果我想将用变量循环显示出的多个select结果最后用union显示在一个select中,该如何操作,比如:表t_errect结构如下: P_name m1 m2 m3pc1 3 5 8pc2 6 9 12要得到:P_name monthly m_valuepc1 m1 3pc1 m1 5pc1 m1 8pc2 m2 6pc2 m2 9pc2 m2 12declare @m_str varchar(50),@monthly int,@sql varchar(200)set @monthly=1whlie (@monthly<4)begin set @m_str='m'+cast(@monthly as varchar(5)) set @sql='select P_name,''''+@monthly+'''' as monthly,'+@m_str+' as m_value from' +'(select P_name,'+@m_str+' as m_value from t_errect) a' exec(@sql) set @monthly=@monthly+1end这样做得出的结果分别有3个select结果,如何将这些结果都集中在一个select中显示出来? Create Table t_errect(P_name Varchar(10), m1 Int, m2 Int, m3 Int)Insert t_errect Select 'pc1', 3, 5, 8Union All Select 'pc2', 6, 9, 12GOdeclare @m_str varchar(50),@monthly int,@sql varchar(2000)select @monthly=1, @sql = ''while (@monthly<4)begin set @m_str='m'+cast(@monthly as varchar(5)) set @sql= @sql + ' union all select P_name,'''+Rtrim(@monthly)+''' as monthly,'+@m_str+' as m_value from t_errect' set @monthly=@monthly+1endselect @sql = Stuff(@sql, 1, 10, '')exec(@sql)GO--Result/*P_name monthly m_valuepc1 1 3pc2 1 6pc1 2 5pc2 2 9pc1 3 8pc2 3 12*/ 修改下Create Table t_errect(P_name Varchar(10), m1 Int, m2 Int, m3 Int)Insert t_errect Select 'pc1', 3, 5, 8Union All Select 'pc2', 6, 9, 12GOdeclare @m_str varchar(50),@monthly int,@sql varchar(2000)select @monthly=1, @sql = ''while (@monthly<4)begin set @m_str='m'+cast(@monthly as varchar(5)) set @sql= @sql + ' union all select P_name,'''+ @m_str+''' as monthly,'+@m_str+' as m_value from t_errect' set @monthly=@monthly+1endselect @sql = Stuff(@sql, 1, 10, '')exec(@sql)GODrop Table t_errect--Result/*P_name monthly m_valuepc1 m1 3pc2 m1 6pc1 m2 5pc2 m2 9pc1 m3 8pc2 m3 12*/ 帮忙找个错,谢谢 sql sever 表语句 请问,sqlserver把表全部装在到内存怎么装载。装载之后,跟把数据读到内存放到map里,然后map(key,vale)查找有什么区别 关于SQL语句,把纪录变成字段横向显示 请问这条语句MS sql里怎么表示? 關于sqlserver的急問題! 求SQL语句:每班各取n个学生。 怎么写这样的触发器??---在线等 面试考题:关于建立自动运行的存储过程,并让这个存储过程自动建立两个触发器的问题! 请问8I中,为什么sqlplus worksheet工具中输出栏中的汉字全为乱码? 一个小问题, 请问这个情况用sql语句可不可以实现
表t_errect结构如下:
P_name m1 m2 m3
pc1 3 5 8
pc2 6 9 12
要得到:
P_name monthly m_value
pc1 m1 3
pc1 m1 5
pc1 m1 8
pc2 m2 6
pc2 m2 9
pc2 m2 12declare @m_str varchar(50),@monthly int,@sql varchar(200)
set @monthly=1
whlie (@monthly<4)
begin
set @m_str='m'+cast(@monthly as varchar(5))
set @sql='select P_name,''''+@monthly+'''' as monthly,'+@m_str+' as m_value from'
+'(select P_name,'+@m_str+' as m_value) a'
exec(@sql)
set @monthly=@monthly+1
end
这样做得出的结果分别有3个select结果,如何将这些结果都集中在一个select中显示出来?
表t_errect结构如下:
P_name m1 m2 m3
pc1 3 5 8
pc2 6 9 12
要得到:
P_name monthly m_value
pc1 m1 3
pc1 m1 5
pc1 m1 8
pc2 m2 6
pc2 m2 9
pc2 m2 12declare @m_str varchar(50),@monthly int,@sql varchar(200)
set @monthly=1
whlie (@monthly<4)
begin
set @m_str='m'+cast(@monthly as varchar(5))
set @sql='select P_name,''''+@monthly+'''' as monthly,'+@m_str+' as m_value from'
+'(select P_name,'+@m_str+' as m_value from t_errect) a'
exec(@sql)
set @monthly=@monthly+1
end
这样做得出的结果分别有3个select结果,如何将这些结果都集中在一个select中显示出来?
(P_name Varchar(10),
m1 Int,
m2 Int,
m3 Int)
Insert t_errect Select 'pc1', 3, 5, 8
Union All Select 'pc2', 6, 9, 12
GO
declare @m_str varchar(50),@monthly int,@sql varchar(2000)
select @monthly=1, @sql = ''
while (@monthly<4)
begin
set @m_str='m'+cast(@monthly as varchar(5))
set @sql= @sql + ' union all select P_name,'''+Rtrim(@monthly)+''' as monthly,'+@m_str+' as m_value from t_errect'
set @monthly=@monthly+1
end
select @sql = Stuff(@sql, 1, 10, '')
exec(@sql)
GO
--Result
/*
P_name monthly m_value
pc1 1 3
pc2 1 6
pc1 2 5
pc2 2 9
pc1 3 8
pc2 3 12
*/
(P_name Varchar(10),
m1 Int,
m2 Int,
m3 Int)
Insert t_errect Select 'pc1', 3, 5, 8
Union All Select 'pc2', 6, 9, 12
GO
declare @m_str varchar(50),@monthly int,@sql varchar(2000)
select @monthly=1, @sql = ''
while (@monthly<4)
begin
set @m_str='m'+cast(@monthly as varchar(5))
set @sql= @sql + ' union all select P_name,'''+ @m_str+''' as monthly,'+@m_str+' as m_value from t_errect'
set @monthly=@monthly+1
end
select @sql = Stuff(@sql, 1, 10, '')
exec(@sql)
GO
Drop Table t_errect
--Result
/*
P_name monthly m_value
pc1 m1 3
pc2 m1 6
pc1 m2 5
pc2 m2 9
pc1 m3 8
pc2 m3 12
*/