查询中有一列包含动态SQL，如何将这一列执行为具体的值？

[me_child]

有一个表A：

ID TYPE SQL_TEXT
1 1202 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1
2 1203 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3


这张表有5W多行数据
现在我需要查询这张表，并且把 SQL_TEXT执行为具体的值， 这一步不知道如何弄了， SQL函数不能用动态SQL，所以函数用不了， 存储过程的返回值不能做为查询的一列查来查询（select a,b,c,(exec proc) from A） 没招了， 另外不要用游标， 目前就是用的游标 执行40分钟才出来结果， 这个性能领导不满意。 求教 在线等

[道玄希言]

引用 7 楼 me_child 的回复:

还是得用游标？ 那没啥意义啊， 5W行数据 游标太坑爹 要40多分钟啊 现在就是不想用游标， 那一列的值永远只查出来一个字符产， 不会是别的东西

你沒明白我的意思啊。。。 我是说，如果每次你SQL语句查询出的值都是一样的， 那就一次性的查出它的值，保存好查询结果到新列SQL_VALUE。 以后就不用管那些查询语句了， 直接用保存好的值。 即： 假设 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 查询结果为 1 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3 查询结果为 2 如果每次执行这些语句， 其结果不会变化，也就是说， 我之前查询 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这句，值为1, N天以后，查询结果还是 1, 这个结果不会变化， 那么 我们就可以在数据表上添加一列： SQL_VALUE 將表变成： ID TYPE SQL_VALUE SQL_TEXT 1 1202 1 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 2 1203 2 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3 新增列后，需要游标或者如2楼说的， 拼接出语句，將查询结果写到新列SQL_VALUE。 然后， 表加上触发器，新增或者 SQL_TEXT 列变化时，將结果写到 SQL_VALUE 。 这个游标就只执行一次，写如初始数据到SQL_VALUE，之后表数据有变化，触发器就及时的帮你改正好了。 之后，你的查询语句，就不用管SQL_TEXT字段是啥玩意了，就直接 select id, type, sql_value from tbl 了。 因为 sql_value 的值，已经就是你 SQL_TEXT 查询出来的了。

[道玄希言]

不然，你一条查询语句执行只要花费 10毫秒， 5W 条纪录就是 500 秒了。实际上，一个查询耗时应该不止这个数了。这还不算其他开消。 个人感觉每次取数时及时查询效率太低了。除非你分页，一次只取几条数据。

[等不到来世]

--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

[me_child]

引用 10 楼 szx1999 的回复:

--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

循环和游标的性能差不多吧5W行要循环到啥时候？

[xqchenxue2]

select SQL_TEXT + ';' from tableName for xml path(''); 一次可以全部查出来,如果感觉太多的话，就分几次取了

[spiritofdragon]

引用 4 楼 me_child 的回复:

[quote=引用 2 楼 spiritofdragon 的回复:] 如果你的每条查询的结果，都能保证是1行1列。 那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。

declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);

但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

你的意思是把5W行记录的SQL都拼起来？[/quote] 5w怎么了，这是思路而已。你是一条一条执行，逻辑上肯定没错，但耗的是IO。拼成在一起，省了IO！只是会有长度限制或者别的某些限制。所以需要拆成N段执行。 我相信好多人在导生产库的表时本来用insert into select ...一句话就可以搞定的时候，因为耗时长时，怕锁表，超时等原因。有时也得改成insert into select ....where rownum between 1 and 1000 。。。1001 and 2000.....这个分法，用程序就可以拆出来的又不是非得写死。

[LongRui888]

引用 11 楼 me_child 的回复:

[quote=引用 10 楼 szx1999 的回复:]

--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

循环和游标的性能差不多吧5W行要循环到啥时候？[/quote] 循环和游标的性能确实差不多的，偶尔性能游标还快一点。 我觉得你这个问题，现在不是说用不用动态语句，什么函数，什么存储过程。 而是你要执行5w个语句，除非服务器性能超强，否则也会很慢。 当然，创建合适的索引，应该能降低查询所需的时间，但也不会说5w条数据，只需要几秒钟的。 我觉得能否这样，你新建一个表： 表tb 字段 id value 这里的id就是表A的id,而value就是表A中每个语句执行的结果，然后每次如果更新了表A中的sql_text,那么同步更新一下 表tb中的value值。 这样就不需要每次去查，都做无用功，因为肯定有一些表A中的sqltext是不变化的，就不需要重复去执行了。

[spiritofdragon]

bcp 都是1000行，1000行这么导的，你写个分页执行有啥不行...

[me_child]

人气呢？csdn的人气呢？

[spiritofdragon]

如果你的每条查询的结果，都能保证是1行1列。 那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。

declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);

但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

[道玄希言]

如果 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这类语句的查询结果不会变化，估计除了游标外，只有添加一个数据列， 將那些SQL语句的值，直接保存到表。 平时使用时，直接用这列的数据。

[me_child]

引用 2 楼 spiritofdragon 的回复:

如果你的每条查询的结果，都能保证是1行1列。 那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。

declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);

但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

你的意思是把5W行记录的SQL都拼起来？

[me_child]

引用 3 楼 yangb0803 的回复:

如果 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这类语句的查询结果不会变化，估计除了游标外，只有添加一个数据列， 將那些SQL语句的值，直接保存到表。 平时使用时，直接用这列的数据。

问题是每一列的查询条件都不一样， 唯一一样的是 查询的结果只有一个字符串

[道玄希言]

引用 5 楼 me_child 的回复:

问题是每一列的查询条件都不一样， 唯一一样的是 查询的结果只有一个字符串

我知道查询条件不同。 我的意思是， 你每一个查询语句的值， 是不是一定的? 比如： SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这个语句执行查询后，获取到的值，是不是固定的？ 如果是个固定值，那么，我们可以通过游标等方式， 一次性的对这列数据进行转化。 给数据表新添加一列 SQL_VALUE， 一次性的將SQL_TEXT列查询的值保存到 新增字段SQL_VALUE。 然后给表一个触发器，当纪录在这列修改，或者是新增纪录时，执行触发，將查询到的值写到新列 SQL_VALUE。 这样，你就有了该列的值数据了，再对表进行查询时，也就无需再去根据SQL_TEXT内容逐条的来查询， 而可以直接就用 SQL_VALUE 的值了。

[me_child]

引用 6 楼 yangb0803 的回复:

[quote=引用 5 楼 me_child 的回复:] 问题是每一列的查询条件都不一样， 唯一一样的是 查询的结果只有一个字符串

我知道查询条件不同。 我的意思是， 你每一个查询语句的值， 是不是一定的? 比如： SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这个语句执行查询后，获取到的值，是不是固定的？ 如果是个固定值，那么，我们可以通过游标等方式， 一次性的对这列数据进行转化。 给数据表新添加一列 SQL_VALUE， 一次性的將SQL_TEXT列查询的值保存到 新增字段SQL_VALUE。 然后给表一个触发器，当纪录在这列修改，或者是新增纪录时，执行触发，將查询到的值写到新列 SQL_VALUE。 这样，你就有了该列的值数据了，再对表进行查询时，也就无需再去根据SQL_TEXT内容逐条的来查询， 而可以直接就用 SQL_VALUE 的值了。 [/quote] 还是得用游标？ 那没啥意义啊， 5W行数据 游标太坑爹 要40多分钟啊 现在就是不想用游标， 那一列的值永远只查出来一个字符产， 不会是别的东西