像select cast(字段名 as char) from 表//////////////这只是改变了字段的类型,将"字段名"转换为char类型
select cast(字段a as char) from 表,像字段a有个值是0.8*100/20,sql语句取出来是0.8*100/20,如何让它取出来是值4呢?
给你个例子,要动态执行sql才行 declare @Expression varchar(20) set @Expression='0.8*100/20' execute ('select '+@expression)
--创建表 create table t(id int identity(1,1),e varchar(20)) go --插入数据 insert into t select '0.8*100/20' union select '0.8*100/23' union select '0.8*100/37' go --创建存储过程 create proc p_t as declare @s varchar(8000) select @s=' select id,e,' select @s=@s+e+'[value] from t where id = '+convert(varchar(20),id)+' union select id,e, ' from t exec (@s+' null from t where id=-1') go --测试 exec p_t go drop table t drop proc p_t /*id e value ----------- -------------------- ------------ 1 0.8*100/20 4.000000 2 0.8*100/23 3.478260 3 0.8*100/37 2.162162 */
varchar最多只能8KB阿,我数据一多,好像就不能执行了
那不如建个这样的存储过程 create proc p_t(@Expression varchar(20)) as execute ('select '+@expression) go程序里一条一条循环取值 with adoquery1 do begin close; sql.text := 'select e from table1'; open; first; while not eof do begin with adoStoredProc1 do begin close; procedureName ;= 'p_t;1'; parameters.refresh; parameters[1].value := adoquery1.FieldByName('e').asString; Open; showmessage(Adoquery1.Fields[0].asstring); end; next; end; end
select cast(字段名 as char) as 别名
from tblname
declare @Expression varchar(20)
set @Expression='0.8*100/20'
execute ('select '+@expression)
create table t(id int identity(1,1),e varchar(20))
go
--插入数据
insert into t select '0.8*100/20'
union select '0.8*100/23'
union select '0.8*100/37'
go
--创建存储过程
create proc p_t
as
declare @s varchar(8000)
select @s=' select id,e,'
select @s=@s+e+'[value] from t where id = '+convert(varchar(20),id)+' union select id,e, ' from t
exec (@s+' null from t where id=-1')
go
--测试
exec p_t
go
drop table t
drop proc p_t
/*id e value
----------- -------------------- ------------
1 0.8*100/20 4.000000
2 0.8*100/23 3.478260
3 0.8*100/37 2.162162
*/
create proc p_t(@Expression varchar(20))
as
execute ('select '+@expression)
go程序里一条一条循环取值
with adoquery1 do
begin
close;
sql.text := 'select e from table1';
open;
first;
while not eof do
begin
with adoStoredProc1 do
begin
close;
procedureName ;= 'p_t;1';
parameters.refresh;
parameters[1].value := adoquery1.FieldByName('e').asString;
Open;
showmessage(Adoquery1.Fields[0].asstring);
end;
next;
end;
end