根据表中的表达式返回具体的SQL串。。有没有办法？

2008-11-26 09:03:50 185 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

从仿真到流片：手把手教你优化65nm CMOS电荷泵的电流匹配与稳定性本文详细介绍了在65nm CMOS工艺下优化电荷泵电流匹配与稳定性的工程实践指南。通过蒙特卡洛仿真、运放反馈结构版图技巧和全差分结构设计，实现优于1%的电流匹配精度和±2%以内的电流稳定性，特别针对current mismatch问题提供了有效的解决方案。阅读详情

表名 table
----------------------------
xm(项目) zd(字段) bds(表达式)
------------------------------
A1 Zd1
A2 Zd2
A3 Zd3
A4 Zd4 A1/2
A5 Zd5 A1/2
A6 Zd6
A7 Zd7
A8 Zd8 A4+A5+A6+A7
----------------------------
我想根据表达式得到最后的结果，比如我要查table的所有字段,那么想返回的是
zd1,zd2,zd3,zd4=(zd1/2),zd5=(zd1/2),zd6,zd7,zd8=(zd4+zd5+zd6+zd7)

这要能返回这样一串由公式得出来的字符串即可(希望能用存储过程),有没有办法？

...全文

（Touch™）搭建 Windows 开发环境 > OpenHaptics 开发包参考文档。阅读详情

lqscoke

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

lqscoke 2008-11-26

打赏
举报

已经结贴了
楼上先谢谢了，下次给分
再次谢谢各位帮忙，我第一次发贴，不知道分数如何分配，不当之处，谅解`

csdyyr 2008-11-26

打赏
举报

CREATE TABLE TB(xm VARCHAR(2), zd VARCHAR(3), bds VARCHAR(20))

INSERT TB

SELECT 'A1',  'Zd1' ,  ''UNION ALL 

SELECT 'A2',  'Zd2' ,  '' UNION ALL 

SELECT 'A3',  'Zd3' ,  '' UNION ALL 

SELECT 'A4',  'Zd4',  'A1/2' UNION ALL 

SELECT 'A5',  'Zd5',  'A1/2' UNION ALL 

SELECT 'A6',  'Zd6' ,  '' UNION ALL 

SELECT 'A7',  'Zd7' ,  '' UNION ALL 

SELECT 'A8',  'Zd8',  'A4+A5+A6+A7'

GO



CREATE FUNCTION F_GetField(@STR VARCHAR(20))

RETURNS VARCHAR(20)

AS

BEGIN

    DECLARE @SQL VARCHAR(20)

    SET @SQL=@STR

    SELECT @SQL=STUFF(@SQL,

	   CHARINDEX(XM,@SQL),

	   LEN(XM),

                     ZD)  --  SELECT *

    FROM TB WHERE CHARINDEX(XM,@SQL)>0

    RETURN @SQL

END

GO



DECLARE @SQL VARCHAR(8000)

SET @SQL=''

SELECT @SQL=@SQL+','+CASE WHEN bds<>'' THEN ZD+'=('+DBO.F_GetField(BDS)+')' ELSE zd END

FROM TB



SET @SQL=STUFF(@SQL,1,1,'')

PRINT @SQL



DROP TABLE TB

DROP FUNCTION F_GetField

/*

Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)

*/

fcuandy 2008-11-26

打赏
举报

DECLARE @t TABLE([xm] NVARCHAR(2),[zd] NVARCHAR(3),[bds] NVARCHAR(11))

INSERT @t SELECT N'A1',N'Zd1',NULL

UNION ALL SELECT N'A2',N'Zd2',NULL

UNION ALL SELECT N'A3',N'Zd3',NULL

UNION ALL SELECT N'A4',N'Zd4',N'A1/2'

UNION ALL SELECT N'A5',N'Zd5',N'A1/2'

UNION ALL SELECT N'A6',N'Zd6',NULL

UNION ALL SELECT N'A7',N'Zd7',NULL

UNION ALL SELECT N'A8',N'Zd8',N'A4+A5+A6+A7'

/************/

/*Test  Data*/

/***fcuandy**/

/*2008-11-26*/

/************/

 ;

WITH fc(xm,bds,lvl)

AS

(

	SELECT xm,CAST(bds AS NVARCHAR(MAX)),1

		FROM @t

		WHERE bds IS NOT NULL

	UNION ALL

	SELECT b.xm,REPLACE(b.bds,a.xm,a.zd),b.lvl+1

		FROM @t a,fc b

		WHERE CHARINDEX(a.xm,b.bds)>0

	

)



SELECT DISTINCT a.xm,b.bds

	FROM @t a

LEFT JOIN 

	(SELECT x.* FROM fc x

	INNER JOIN

		(

		SELECT MAX(lvl) ml,xm FROM fc GROUP BY xm

		) y

		ON x.xm=y.xm AND x.lvl=y.ml

	) b

	ON a.xm=b.xm

/*

A1	NULL

A2	NULL

A3	NULL

A4	Zd1/2

A5	Zd1/2

A6	NULL

A7	NULL

A8	Zd4+Zd5+Zd6+Zd7

*/

lqscoke 2008-11-26

打赏
举报

刚发错了,是要发给8楼的大哥的
9楼的可以
其它的再替换一次replace(@sql,xm,zd)再加上这句就OK了

谢谢各位了，非常感谢~结贴

dawugui 2008-11-26

打赏
举报

create table tb(xm varchar(50),zd varchar(50),bds varchar(50))

insert into tb select 'A1','Zd1',''

insert into tb select 'A2','Zd2',''

insert into tb select 'A3','Zd3',''

insert into tb select 'A4','Zd4','A1/2'

insert into tb select 'A5','Zd5','A1/2'

insert into tb select 'A6','Zd6',''

insert into tb select 'A7','Zd7',''

insert into tb select 'A8','Zd8','A4+A5+A6+A7'



--1.用动态语句生成

declare @output varchar(8000)

select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from 

(

select m.xm , m.zd , bds = replace(m.bds , n.xm , n.zd) from tb m , tb n where charindex(n.xm , m.bds) > 0 and charindex('/' , m.bds) > 0

union all

select * from tb where charindex('/' , bds) = 0

) t



print @output

/*

Zd4=(Zd1/2),Zd5=(Zd1/2),Zd1,Zd2,Zd3,Zd6,Zd7,Zd8=(A4+A5+A6+A7)

*/



--2.存储过程

go

create procedure my_proc @@str varchar(100) OUTPUT

as

select @@str = coalesce(@@str + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from 

(

select m.xm , m.zd , bds = replace(m.bds , n.xm , n.zd) from tb m , tb n where charindex(n.xm , m.bds) > 0 and charindex('/' , m.bds) > 0

union all

select * from tb where charindex('/' , bds) = 0

) t 



go



declare @@str as varchar(100)

exec my_proc @@str output

select @@str

/*

Zd4=(Zd1/2),Zd5=(Zd1/2),Zd1,Zd2,Zd3,Zd6,Zd7,Zd8=(A4+A5+A6+A7)



（所影响的行数为 1 行）

*/



drop table tb

drop procedure my_proc

dawugui 2008-11-26

打赏
举报

[Quote=引用 13 楼 lqscoke 的回复:]
11楼的大哥
我想得到的是
zd1,zd2,zd3,zd4=(zd1/2),zd5=(zd1/2),zd6,zd7,zd8=(zd4+zd5+zd6+zd7)
而不是
Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)

-----
还是非常感谢``
[/Quote]
哦,你还要替换啊?

wzy_love_sly 2008-11-26

打赏
举报

if object_id('tb') is not null

drop table tb

go

create table tb(xm varchar(50),zd varchar(50),bds varchar(50))

insert into tb select 'A1','Zd1',''

insert into tb select 'A2','Zd2',''

insert into tb select 'A3','Zd3',''

insert into tb select 'A4','Zd4','A1/2'

insert into tb select 'A5','Zd5','A1/2'

insert into tb select 'A6','Zd6',''

insert into tb select 'A7','Zd7',''

insert into tb select 'A8','Zd8','A4+A5+A6+A7'





declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'=('+bds+')' end from tb

select @sql

Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)

水族杰纶 2008-11-26

打赏
举报

[Quote=引用 12 楼 lqscoke 的回复:]
9楼的大哥
你这样是替换字段的名称,我字段和表达式名称没有规律的，不是A1->Zd1 A2->Zd2,这个是没有联系的

能不能再帮忙想想？
[/Quote]
舉例說說~~~

lqscoke 2008-11-26

打赏
举报

3楼的也是得到这个?
Zd1,Zd2,Zd3,Zd4=A1/2,Zd5=A1/2,Zd6,Zd7,Zd8=A4+A5+A6+A7
而不是
zd1,zd2,zd3,zd4=(zd1/2),zd5=(zd1/2),zd6,zd7,zd8=(zd4+zd5+zd6+zd7)

非常感谢大家的热情帮忙，有路过的再帮小弟想想``

lqscoke 2008-11-26

打赏
举报

11楼的大哥
我想得到的是
zd1,zd2,zd3,zd4=(zd1/2),zd5=(zd1/2),zd6,zd7,zd8=(zd4+zd5+zd6+zd7)
而不是
Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)

-----
还是非常感谢``

lqscoke 2008-11-26

打赏
举报

9楼的大哥
你这样是替换字段的名称,我字段和表达式名称没有规律的，不是A1->Zd1 A2->Zd2,这个是没有联系的

能不能再帮忙想想？

dawugui 2008-11-26

打赏
举报

create table tb(xm varchar(50),zd varchar(50),bds varchar(50))

insert into tb select 'A1','Zd1',''

insert into tb select 'A2','Zd2',''

insert into tb select 'A3','Zd3',''

insert into tb select 'A4','Zd4','A1/2'

insert into tb select 'A5','Zd5','A1/2'

insert into tb select 'A6','Zd6',''

insert into tb select 'A7','Zd7',''

insert into tb select 'A8','Zd8','A4+A5+A6+A7'



--1.用动态语句生成

declare @output varchar(8000)

select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb

print @output

/*

Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)

*/



--2.存储过程

go

create procedure my_proc @@str varchar(100) OUTPUT

as

select @@str = coalesce(@@str + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb

go



declare @@str as varchar(100)

exec my_proc @@str output

select @@str

/*

Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)



（所影响的行数为 1 行）

*/



drop table tb

drop procedure my_proc

lqscoke 2008-11-26

打赏
举报

非常谢谢大家,我试一下，回头来结贴`

水族杰纶 2008-11-26

打赏
举报

set nocount on

if object_id('tb')is not null drop table tb

go

create table tb(xm varchar(10), zd varchar(10), bds varchar(20)) 

------------------------------ 

insert tb select 'A1' ,'Zd1','' 

insert tb select 'A2' ,'Zd2', ''

insert tb select 'A3', 'Zd3', ''

insert tb select 'A4' ,'Zd4', 'A1/2' 

insert tb select 'A5' ,'Zd5', 'A1/2' 

insert tb select 'A6' ,'Zd6', ''

insert tb select 'A7' ,'Zd7', ''

insert tb select 'A8', 'Zd8', 'A4+A5+A6+A7' 

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'=('+bds+')' end from tb

select @sql=replace(@sql,xm,zd) from tb

select @sql

/*                                                                                                                                                                                                         

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)*/

csdyyr 2008-11-26

打赏
举报

CREATE TABLE TB(xm VARCHAR(2), zd VARCHAR(3), bds VARCHAR(20))

INSERT TB

SELECT 'A1',  'Zd1' ,  ''UNION ALL 

SELECT 'A2',  'Zd2' ,  '' UNION ALL 

SELECT 'A3',  'Zd3' ,  '' UNION ALL 

SELECT 'A4',  'Zd4',  'A1/2' UNION ALL 

SELECT 'A5',  'Zd5',  'A1/2' UNION ALL 

SELECT 'A6',  'Zd6' ,  '' UNION ALL 

SELECT 'A7',  'Zd7' ,  '' UNION ALL 

SELECT 'A8',  'Zd8',  'A4+A5+A6+A7'



DECLARE @SQL VARCHAR(8000)

SET @SQL=''

SELECT @SQL=@SQL+','+CASE WHEN bds<>'' THEN ZD+'=('+REPLACE(bds,'A','Zd')+')' ELSE zd END

FROM TB



SET @SQL=STUFF(@SQL,1,1,'')

PRINT @SQL



DROP TABLE TB

/*

Zd1,Zd2,Zd3,Zd4=(Zd1/2),Zd5=(Zd1/2),Zd6,Zd7,Zd8=(Zd4+Zd5+Zd6+Zd7)

*/

dawugui 2008-11-26

打赏
举报

create table tb(xm varchar(50),zd varchar(50),bds varchar(50))

insert into tb select 'A1','Zd1',''

insert into tb select 'A2','Zd2',''

insert into tb select 'A3','Zd3',''

insert into tb select 'A4','Zd4','A1/2'

insert into tb select 'A5','Zd5','A1/2'

insert into tb select 'A6','Zd6',''

insert into tb select 'A7','Zd7',''

insert into tb select 'A8','Zd8','A4+A5+A6+A7'





declare @output varchar(8000)

select @output = coalesce(@output + ',' , '') + zd + case when bds = '' then '' else '=(' + bds + ')' end from tb

print @output



drop table tb



/*

Zd1,Zd2,Zd3,Zd4=(A1/2),Zd5=(A1/2),Zd6,Zd7,Zd8=(A4+A5+A6+A7)

*/

wzy_love_sly 2008-11-26

打赏
举报

create proc 你的存储过程名称

--@参数 varchar(50) 可以加参数

as

begin

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'='+bds end from tb-- where 列=@参数   这里可以加条件

exec('select '+ @sql +' from 你的表')--你的表 后面可以加条件

end

dawugui 2008-11-26

打赏
举报

/*如何将一列中所有的值一行显示

数据源

  a

  b

  c

  d

  e

结果

a,b,c,d,e

*/



create table tb(col varchar(20))

insert tb values ('a')

insert tb values ('b')

insert tb values ('c')

insert tb values ('d')

insert tb values ('e')

go



--方法一

declare @sql varchar(1000)

set @sql = ''

select @sql = @sql + t.col + ',' from (select col from tb) as t

set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''

exec(@sql)

/*

result     

---------- 

a,b,c,d,e,

*/



--方法二

declare @output varchar(8000)

select @output = coalesce(@output + ',' , '') + col from tb

print @output

/*

a,b,c,d,e

*/



drop table tb

wzy_love_sly 2008-11-26

打赏
举报

Zd1,Zd2,Zd3,Zd4=A1/2,Zd5=A1/2,Zd6,Zd7,Zd8=A4+A5+A6+A7

wzy_love_sly 2008-11-26

打赏
举报

create table tb(xm varchar(50),zd varchar(50),bds varchar(50))

insert into tb select 'A1','Zd1',''

insert into tb select 'A2','Zd2',''

insert into tb select 'A3','Zd3',''

insert into tb select 'A4','Zd4','A1/2'

insert into tb select 'A5','Zd5','A1/2'

insert into tb select 'A6','Zd6',''

insert into tb select 'A7','Zd7',''

insert into tb select 'A8','Zd8','A4+A5+A6+A7'





declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+case when bds='' then zd else zd+'='+bds end from tb

exec('select '+ @sql +' from 你的表')

加载更多回复（2）

根据表中的表达式返回具体的SQL串。。有没有办法？

相关推荐