动态sql/sql循环/动态select语句

vxqlmq5t 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(369)

昨晚我发布了一个很长的问题，太深入了。所以，让我们把这个问题保持在理论上。
假设您有一个非常长的select语句，它由几十个代码块组成。每个块创建的计算列略有不同。为了简单起见，你用一种颜色来命名每一块。
在顶部还声明了5个变量。每个变量包含一个字符串形式的颜色。
是否可以遍历这些变量并使用其中的值来选择要运行的select语句中的哪些块？
因此查询将返回3个计算列。绿色的列，黄色的，然后又是绿色的，其他变量4和5都是空的

DECLARE

@variable1 = 'Green'
@variable2 = 'Yellow'
@variable3 = 'Green'
@variable4 =  NULL
@variable5 = NULL

SELECT

CASE WHEN... THEN... AS 'Red Column',
CASE WHEN... THEN... AS 'Yellow Column',
CASE WHEN... THEN... AS 'Pink Column',
CASE WHEN... THEN... AS 'Green Column',
CASE WHEN... THEN... AS 'Orange Column',
CASE WHEN... THEN... AS 'Purple Column',
CASE WHEN... THEN... AS 'Blue Column'

FROM Some_tables

WHERE something = something

ORDER BY

sql也可以吗？非常感谢

sql sql-server dynamic-sql sql-server-2008-r2

来源：https://stackoverflow.com/questions/61955800/dynamic-sql-sql-loops-dynamic-select-statement

1条答案

按热度按时间

fjaof16o1#

你可以分两步来做：1。将所有数据插入中间表tmp1

SELECT
CASE WHEN... THEN... AS 'Red Column',
CASE WHEN... THEN... AS 'Yellow Column',
CASE WHEN... THEN... AS 'Pink Column',
CASE WHEN... THEN... AS 'Green Column',
CASE WHEN... THEN... AS 'Orange Column',
CASE WHEN... THEN... AS 'Purple Column',
CASE WHEN... THEN... AS 'Blue Column'
Into Tmp1 
FROM Some_tables

2.动态选择表格tmp1表格

@sql = 'select '
if(@variable1 is not null)
begin 
  @sql= @sql + @variable1  + ' , '
end 

if(@variable2 is not null)
begin 
  @sql= @sql + @variable2  + ' , '
end 
 .......
.......
@sql = left(@sql ,len(@sql)-1)  -- this to remove last ","
@sql = @sql  + ' from Tmp1'

赞(0）回复(0）举报 2021-08-13

我来回答

动态sql/sql循环/动态select语句

1条答案

相关问题

热门标签

最新问答