没测试create function f_change(@str varchar(100)) returns varchar(200) as begin select @str=replace(@str,字段ID,字段名) from 表2 return @str endselect 用户名, f_change(字段ID ) from 表1
update tb set get=case when 项目='cash' then cash when 项目='paper' then paper end where 项目 in('cash','paper')
update tb set get=case when 项目='cash' then cash when 项目='paper' then paper end where 项目 in('cash','paper')需要动态拼接一下字符串就行了
update tb set get=case when 项目='cash' then cash when 项目='paper' then paper end where 项目 in('cash','paper')
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-05-13 13:36:40 -- Version: -- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) -- Oct 14 2005 00:33:37 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#TB if object_id('tempdb.dbo.#TB') is not null drop table #TB go create table #TB([项目] varchar(5),[cash] int,[paper] int,[get] sql_variant) insert #TB select 'cash',1,2,null union all select 'paper',1,2,null --------------开始查询-------------------------- DECLARE @S VARCHAR(800) SET @S='CASE ' SELECT @S=@S+' WHEN [项目]='''+NAME+''' THEN '+NAME+'' FROM #TB A ,tempdb.dbo.SYSCOLUMNS B WHERE A.[项目]=B.NAME AND B.NAME NOT IN('[项目]','[GET]') AND B.ID=OBJECT_ID('tempdb.dbo.#TB') SELECT @S='UPDATE #TB SET GET='+@S+' END ' EXEC (@S) SELECT * FROM #TB --SELECT * FROM tempdb.dbo.SYSCOLUMNS B WHERE B.ID=OBJECT_ID('tempdb.dbo.#TB')----------------结果---------------------------- /* (2 行受影响)(2 行受影响) 项目 cash paper get ----- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- cash 1 2 1 paper 1 2 2(2 行受影响) */
returns varchar(200)
as
begin
select @str=replace(@str,字段ID,字段名) from 表2
return @str
endselect 用户名, f_change(字段ID ) from 表1
set get=case when 项目='cash' then cash
when 项目='paper' then paper end
where 项目 in('cash','paper')
set get=case when 项目='cash' then cash
when 项目='paper' then paper end
where 项目 in('cash','paper')需要动态拼接一下字符串就行了
set get=case when 项目='cash' then cash
when 项目='paper' then paper end
where 项目 in('cash','paper')
如果【get】字段是另外一个表的字段,和这个表建立视图的时候,可以自动将这个表的值以这个逻辑写入【get】吗?
-- Author :SQL77(只为思齐老)
-- Date :2010-05-13 13:36:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([项目] varchar(5),[cash] int,[paper] int,[get] sql_variant)
insert #TB
select 'cash',1,2,null union all
select 'paper',1,2,null
--------------开始查询--------------------------
DECLARE @S VARCHAR(800)
SET @S='CASE '
SELECT @S=@S+' WHEN [项目]='''+NAME+''' THEN '+NAME+''
FROM
#TB A ,tempdb.dbo.SYSCOLUMNS B
WHERE A.[项目]=B.NAME AND B.NAME NOT IN('[项目]','[GET]') AND
B.ID=OBJECT_ID('tempdb.dbo.#TB')
SELECT @S='UPDATE #TB SET GET='+@S+' END '
EXEC (@S)
SELECT * FROM #TB
--SELECT * FROM tempdb.dbo.SYSCOLUMNS B WHERE B.ID=OBJECT_ID('tempdb.dbo.#TB')----------------结果----------------------------
/* (2 行受影响)(2 行受影响)
项目 cash paper get
----- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
cash 1 2 1
paper 1 2 2(2 行受影响)
*/