例如表table有三个字段A、B、C
A B C
1 * 3
用sql语句怎么实现1*3=3
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-06-13 17:00:06
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] varchar(1),[C] int)
insert [tb]
select 1,'*',3
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tb
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
----------------结果----------------------------
/*
3
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-06-13 17:00:06
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] varchar(1),[C] int)
insert [tb]
select 1,'*',3 union all
select 2,'/',2
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tb
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
----------------结果----------------------------
/*
col
-----------
3
1
(2 行受影响)
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-13 16:57:47
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] int,[B] nvarchar(2),[C] int)
insert [TB]
select 1,'*',3
--------------生成数据--------------------------
DECLARE @a VARCHAR(10)
SELECT @a= 'select '+CAST(a AS VARCHAR)+CAST(b AS VARCHAR)+CAST(c AS VARCHAR)+''from [TB]
exec (@a)
----------------结果----------------------------
/*
-----------
3
*/
------解决方案--------------------
create table t
(A varchar(5),B varchar(5),C varchar(5))
insert into t
select '1','*','3' union all
select '8','/','4' union all
select '3','+','5' union all
select '5','-','2'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+' union all','')
+' select '''+A+B+C+'''+''=''+rtrim('+A+B+C+') ''result'' '
from t
exec(@tsql)
/*
result
----------------
1*3=3
8/4=2
3+5=8
5-2=3
(4 row(s) affected)
*/