sql动态列

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*FROM[TestRows2Columns]（图2：样本数据）(二) 先以静态的方式实现行转列，效果如图3所示：--1：静态拼接行转列SELECT[UserName],SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END) AS'[数学]',SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END) AS'[英语]',SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END) AS'[语文]'FROM[TestRows2Columns]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列DECLARE@sql VARCHAR(8000)SET@sql='SELECT [UserName],'SELECT@sql=@sql+'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','FROM (SELECT DISTINCT[Subject]FROM[TestRows2Columns]) AS aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@sql_where NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'SET@sql_where='WHERE UserName = ''王五'''--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM['+@tableName+']'+@sql_where+') p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图6）。

学习SQL应知道的动态SQL语句基本语法1 、普通SQL语句可以用Exec执行eg: Select * from tableNameExec('select * from tableName')Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N2、字段名，表名，数据库名之类作为变量时，必须用动态SQLeg:declare @fname varchar(20)set @fname = 'FiledName'Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。

Exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格当然将字符串改成变量的形式也可declare @fname varchar(20)set @fname = 'FiledName' --设置字段名declare @s varchar(1000)set @s = 'select ' + @fname + ' from tableName'Exec(@s) -- 成功exec sp_executesql @s -- 此句会报错declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)set @s = 'select ' + @fname + ' from tableName'Exec(@s) -- 成功exec sp_executesql @s -- 此句正确3、输出参数declare @num int,@sqls nvarchar(4000)set @sqls='select count(*) from tableName'exec(@sqls)--如何将exec执行结果放入变量中？declare @num int,@sqls nvarchar(4000)set @sqls='select @a=count(*) from tableName 'exec sp_executesql @sqls,N'@a int output',@num outputselect @num此外，如果想要在SQL语句字符串中使用单引号'' 可以使用''''SQL Server数据库中经常用到的identity列发布时间：2008.03.24 04:59来源：赛迪网作者：Alice【赛迪网－IT技术报道】SQL Server中，经常会用到Identity标识列，这种自增长的字段操作起来的确是比较方便。

如果你用的是Oracle8i 及以上的版本，那简单，在过程中用 execute immediate sql_str 就行， sql_str 是一个拼凑的 SQL 语句，但这个动态语句中带参数，或 Select 的结果要 into 到变量中时就要稍加留心一下了。

而在 8i 以前的版本（谁还用这么古老的玩艺，总有些不得已的地方，老系统考虑升级成本遗留下来的，应用软件所伴随着的等），都没法用 execute immediate，就得使用 DBMS_SQL 包来实现了何谓动态 SQL 和 DDL 语句呢？通常在过程中要操作的表名、字段名都必须是明确的，否则编译过程时就要报错，但如果这两者也用变量名来表示就是动态的。

DDL 就是数据库对象定义的操作，如 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER……，及这些对象的删除、修改操作等等。

比如在Oracle中有执行下面过程块的意图时，就要使用到 execute immediate 或是DBMS_SQL 包了。

当然下面的语句块是通不过的。

declarecol_name varchar2(30) := 'name'; --假定表user存在name字段col_val varchar2(30);beginselect col_name into col_val --按照惯常思维，可能就会这么写from user where age between 18 and 25; --编译时会提示列名不存在的drop table t2; --不能直接执行 DDL 语句，后面查询 t2 编译器就无能为力了end;现在我们提出对上面问题的解，针对第一个 Select 语句来说明，并假设查询中还带有参数。

块中的 DDL 也是类似的解法。

例子因力图涵盖更多内容，所以稍显复杂，如果不需要 into （如 update/delete 语句），或者不带参数，会简单多了，应不难简化。

动态sql语句查询实现自定义列名的方法在进行动态SQL语句查询时，有时候需要根据不同的业务需求自定义列名来返回查询结果。

这种需求可以通过使用SQL的列别名来实现。

我们需要构建一个动态SQL语句，其中包含我们需要查询的列以及相应的别名。

假设我们有一个名为"employees"的表，其中包含"employee_id"、"first_name"和"last_name"三个列，我们希望查询结果中的列名分别为"员工编号"、"姓"和"名"，可以按照以下步骤进行操作：1. 使用SELECT语句编写动态SQL语句的基础框架：```sqlSELECT column1 AS alias1, column2 AS alias2, ...FROM table_nameWHERE condition;```2. 根据我们的需求，在SELECT语句中为每个列添加相应的别名：```sqlSELECT employee_id AS '员工编号', first_name AS '姓',last_name AS '名'FROM employees;```这样，当执行上述SQL语句时，返回的查询结果将包含按照自定义的列名进行命名的结果集。

需要注意的是，在编写动态SQL语句时，应该确保列名的唯一性，避免出现重复的列别名。

此外，在实际应用中，可能还需要对列名进行一些特殊字符的处理，以使其符合具体的要求。

总结一下，通过使用SQL的列别名功能，我们可以实现动态SQL语句查询中自定义列名的方法。

这为我们在满足业务需求的同时，提供了更灵活的结果展示方式。

使用动态SQL语句实现简单的行列转置（动态产生列）要实现简单的行列转置，并动态产生列，可以使用动态SQL语句来实现。

首先，假设有一个表格`table1`，有`id`、`name`和`value`三个字段，我们要将`name`字段的值转换为列名，并将`value`字段的值填充到相应的位置。

动态SQL语句的实现步骤如下：
1. 使用`GROUP_CONCAT`函数将`name`字段的值连接成一个字符串，作为动态列名。

2.使用`CONCAT`函数拼接SQL语句，动态生成列的部分。

3. 使用`GROUP BY`子句将数据按照`id`字段进行分组。

4.使用动态生成的SQL语句进行查询。

下面是实现的示例代码：
```sql
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(name = "', name, '", value, NULL)) AS "', name, '"'))
FROM table1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```
以上代码将会将`name`字段的值转换为列名，并将`value`字段的值填充到相应的位置，然后按照`id`字段进行分组，返回转置后的结果。

需要注意的是，动态SQL语句的生成需要使用`PREPARE`和`EXECUTE`语句，并在使用完毕后使用`DEALLOCATEPREPARE`释放资源。

SQLserver动态⾏转列在学习数据库的时候，遇到了怎么把数据表中的内容转变成数据表的字段，在此，记录⼀下⾃⼰对⾏转列的理解⽐如有个学⽣成绩表：stuid:学号course:科⽬score：成绩表的内容为：stuid course score0101 语⽂ 780101 数学 900101 英语 670101 物理 88⽽我们想要的是类似下表：stuid 语⽂ 数学 英语 物理0101 78 90 67 88这个时候就需要⽤到⾏转列，⾏转列有动态与静态之分：静态⾏转列：通过sql语句，静态的进⾏转换，⼀旦原表的数据有改动，⽐如增加化学成绩，或者删除物理成绩，我们就得重新改变sql语句：就上表，转换的sql语句为：select stuid,max(case course when'语⽂'then score else0end)语⽂,max(case course when'数学'then score else0end)数学,max(case course when'英语'then score else0end)英语,max(case course when'物理'then score else0end)物理from scores --表名group by stuid --分组查询主要知识：max（），case，group by 分组查询。

max（）取最⼤值。

case：我的理解是从⼏个选项中选择，⽐如：case course when '语⽂' then score else 0 end当course 为语⽂时，case返回对应的score与0当中的⼀个，在本例中，查询第⼀条数据：0101 语⽂ 78此时：course=‘语⽂’，score=78，则case返回78，当查询第⼆条数据的时候：0101 数学 90course=‘语⽂’不存在，则返回 0 （else 0 ）以此类推得：max(78,0,0,0),max（）取最⼤值，最后的数据就是 78，所谓静态，就是我们⼿动静态的获取每⼀个字段（语⽂，数学，英语，物理），⼀旦科⽬有所改变，我们就得修改sql语句，不怎么⽅便⽽动态⾏转列就可以避免这种情况，它是动态的⾃⼰根据原表中的数据，获取字段名：declare@sql varchar(8000) --申明⼀个变量 @sql，数据类型为 varchar（8000）set@sql='select stuid,' -- 使⽤ set 为@sql 赋值select@sql=@sql+'max(case course when '''+course +'''then score else 0 end)'+''''+course +''','from (select distinct course from scores) as sc --使⽤select 为@sql赋值set@sql=left(@sql,len(@sql)-1)+'from scores group by stuid'exec(@sql) --执⾏@sql注意：在sql语句中，使⽤单引号 ’ 来确定字符串的范围，如果字符串本⾝含有单引号如：‘ 姓名：‘张三’，性别：‘男’ ’，这时候需要⽤ '' ,即两个单引号来表⽰字符串本⾝的单引号。

2. 嵌入式SQL语句的基本形式
首先在主语言中使用SQL语言，必须将主语言的语句同SQL语句区分开来。区分 的方法是在SQL语句最后以分号“；”作为语句结束符。这样，以PowerBuilder 作为主语言的嵌入式SQL语句的一般形式为： SQL_statement； 例如：delete from wpkc_gysdjb；
嵌入式SQL 嵌入式
嵌入式SQL语句的具体使用方法：
2) 打开游标 语句语法： open cursor_name； 语法说明： （1）打开游标实际上是执行相应的select语句，并把指定表中的所有满足查询条件 的记录放到内存缓冲区中。 （2）执行了“打开游标”后，游标处于活动状态，指针指向查询结果集的第一条记 录。 3) 提取游标中的记录 语句语法： fetch cursor_name into host_variable[,...n]； 语法说明： （1）提取游标，是指从缓冲区中将指针指向的当前记录取出来，送至主变量供主语 言进一步处理，同时移动游标指针。 （2）在fetch中的主变量个数及数据类型应与定义游标时的select语句目标列一致。 （3）由于执行fetch语句只能提取出结果集中的一条记录，因此fetch语句通常用在主 语言的一个循环结构中，通过循环执行fetch语句逐条取出结果集中的记录进行处 理。 4) 关闭游标 语句语法： close cursor_name；
但是由于sql语言具有面向集合非过程化的特点主要负责对数据库中数据的存取操作在用户界面及控制程序流程方面的功能较弱因此sql语言仅仅只在交互环境中执行很难满足应用需求
嵌入式SQL及动态 及动态SQL 嵌入式 及动态
嵌入式SQL 嵌入式
SQL语言是一种结构化查询语言，在许多数据库系统环境中，一般是作为独立语 言，由用户在交互环境下使用。 但是由于SQL语言具有面向集合、非过程化的特点，主要负责对数据库中数据的 存取操作，在用户界面及控制程序流程方面的功能较弱，因此SQL语言仅仅只在交 互环境中执行很难满足应用需求。而高级语言正好相反，它是一种过程化的、与运 行环境有关的语言，具有较强的用户界面设计及控制程序流程的能力。 由于上述原因，出现了SQL语言的另一种执行方式，就是将SQL语言嵌入到某种 高级语言中使用，以便发挥SQL语言和高级语言的各自优势。在这种方式下使用的 SQL语言称为嵌入式SQL（Embedded SQL），而嵌入SQL语句的高级语言称为主 语言。 在这种混合编程模式下，高级语言需要和SQL语句进行信息交换，两者之间的通信 SQL 内容如下： 1）SQL语句将执行状态信息传递给主语言，主语言根据SQL语句的执行状态来控制 程序流程。向主语言传递SQL执行状态信息，主要通过SQL通信区(SQL Communication Area，简称为SQLCA)实现。SQLCA是一个数据结构，其中有一个 返回执行状态的系统变量sqlcode，主语言在执行完一条SQL语句后均应测试sqlcode 的值，以决定该SQL语句的执行情况并做相应处理。 2）主语言需要提供一些变量参数给SQL语句，方法是在主语言中定义“主变 量”(Host Variable)，通过主变量向SQL语句提供参数。 3）SQL语句需要将查询数据库的结果返回给主语言，以便主语言做进一步的处理。 如果SQL语句返回的是单条记录，则可以通过主变量将结果返回到主语言中；如果 SQL语句返回的是多条记录，则可以使用游标将结果返回到主语言中。

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*FROM[TestRows2Columns]（图2：样本数据）(二) 先以静态的方式实现行转列，效果如图3所示：--1：静态拼接行转列SELECT[UserName],SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END) AS'[数学]',SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END) AS'[英语]',SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END) AS'[语文]'FROM[TestRows2Columns]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列DECLARE@sql VARCHAR(8000)SET@sql='SELECT [UserName],'SELECT@sql=@sql+'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','FROM (SELECT DISTINCT[Subject]FROM[TestRows2Columns]) AS aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@sql_where NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'SET@sql_where='WHERE UserName = ''王五'''--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM['+@tableName+']'+@sql_where+') p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图6）。

MyBatis之动态SQL：if、choose、when、otherwise、where、。

动态 SQL 是 MyBatis 的强⼤特性之⼀。

如果你使⽤过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后⼀个列名的逗号。

利⽤动态 SQL，可以彻底摆脱这种痛苦。

使⽤动态 SQL 并⾮⼀件易事，但借助可⽤于任何 SQL 映射语句中的强⼤的动态 SQL 语⾔，MyBatis 显著地提升了这⼀特性的易⽤性。

如果你之前⽤过 JSTL 或任何基于类 XML 语⾔的⽂本处理器，你对动态 SQL 元素可能会感觉似曾相识。

在 MyBatis 之前的版本中，需要花时间了解⼤量的元素。

借助功能强⼤的基于 OGNL 的表达式，MyBatis 3 替换了之前的⼤部分元素，⼤⼤精简了元素种类，现在要学习的元素种类⽐原来的⼀半还要少。

⼀、if —— 只要为true就加上后⾯的sql 使⽤动态 SQL 最常见情景是根据条件包含 where ⼦句的⼀部分。

⽐如：<select id="findActiveBlogWithTitleLike" resultType="Blog">SELECT * FROM BLOGWHERE state = ‘ACTIVE’<if test="title != null">AND title like #{title}</if></select> 这条语句提供了可选的查找⽂本功能。

如果不传⼊ “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传⼊了 “title” 参数，那么就会对 “title” ⼀列进⾏模糊查找并返回对应的 BLOG 结果（细⼼的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）。

ibatis中动态SQL查询和动态标签嵌套的使⽤ibatis 动态查询对于从事 Java EE 的开发⼈员来说，iBatis 是⼀个再熟悉不过的持久层框架了，在 Hibernate、JPA 这样的⼀站式对象 / 关系映射（O/R Mapping）解决⽅案盛⾏之前，iBaits 基本是持久层框架的不⼆选择。

即使在持久层框架层出不穷的今天，iBatis 凭借着易学易⽤、轻巧灵活等特点，也仍然拥有⼀席之地。

尤其对于擅长 SQL 的开发⼈员来说，iBatis 对 SQL 和存储过程的直接⽀持能够让他们在获得 iBatis 封装优势的同时⽽不丧失 SQL 调优的⼿段，这是 Hibernate/JPA 所⽆法⽐拟的。

若要了解、学习ibatis，请看 iBatis开发环境搭建和⽰例。

在项⽬开发的过程中，肯定会遇到需要根据需求动态组装sql 语句的时候，这时，ibatis的动态查询功能应运⽽⽣。

使⽤动态查询是iBatis⼀个⾮常强⼤的功能。

有时你已经改变WHERE⼦句条件的基础上你的参数对象的状态。

在这种情况下的iBATIS提供了⼀组可以映射语句中使⽤，以提⾼SQL语句的重⽤性和灵活性的动态SQL标签。

动态标签的作⽤是动态构建SQL语句，根据不同的⼀元或⼆元运算条件构建复杂的SQL语句，这功能⾮常好，这样就可以把写的BO层的SQL语句构造移值到SQL MAP ⽂件⾥。

例如：select id="findUser" resultClass="User">SELECT * User<dynamic prepend="WHERE "><isNull property="id">id IS NULL</isNull><isNotNull property="id">id = #id#</isNotNull></dynamic></select>ibatis的动态标签分为⼀元条件元素标签和⼆元条件元素标签：⼀元条件元素<isPropertyAvailable>检查是否存在该属性（存在parameter bean的属性）。

sqlselect查询动态添加列,标识列1. 使⽤单引号, 默认值(标识列)select'good'as "ELable","DGHashId" as "EHashId","DHashId" as "DHashId","PHashId" as "PHashId","DSNames" as "EContent","DGDiagnosisTime" as "EDateTime","DGStatus" as "EStatus","DGUpdateTime" as "ECreateTime"from "Diagnosis" where"DHashId" ='4444444444444444'and "PHashId" ='c61b362b1c9dcd09'and "DGStatus" =1效果图如下:1. 使⽤ROW_NUMBER() ;(优点,主要⽤排序功能)selectrow_number() over(order by "DHashId") as "ELable","DGHashId" as "EHashId","DHashId" as "DHashId","PHashId" as "PHashId","DSNames" as "EContent","DGDiagnosisTime" as "EDateTime","DGStatus" as "EStatus","DGUpdateTime" as "ECreateTime"from "Diagnosis" where"DHashId" ='4444444444444444'and "PHashId" ='c61b362b1c9dcd09'and "DGStatus" =1效果如下图:。

MySQL中的动态SQL和参数化查询一、引言随着互联网的迅猛发展，数据处理和存储的需求也越来越大。

MySQL作为一种可靠而且高效的开源数据库管理系统，被广泛应用于各种应用程序中。

在使用MySQL进行数据库查询时，我们常常会遇到一些需要动态生成SQL语句的情况，同时也需要考虑查询的性能和安全性。

在本文中，我们将探讨MySQL中的动态SQL和参数化查询的概念、用法和优点。

二、动态SQL的概念和用法1. 动态SQL是指在程序中根据不同的条件和需求生成不同的SQL语句。

与静态SQL相比，动态SQL的查询条件和语句内容是可以变化的，更加灵活和适应各种场景。

2. 在MySQL中，我们可以使用字符串拼接的方式来生成动态SQL语句。

例如，当我们需要根据用户的输入条件进行查询时，可以使用如下方式：```String name = "John";String sql = "SELECT * FROM users WHERE name = '" + name + "'";```这样我们就可以根据不同的name值生成不同的SQL语句，实现动态查询。

3. 动态SQL的用法在某些情况下非常灵活，但同时也存在一些问题。

例如，如果输入的条件中包含特殊字符或SQL注入攻击的风险，字符串拼接的方式可能会导致安全性问题。

此外，动态SQL也可能影响查询的性能，特别是在大规模数据查询的情况下。

三、参数化查询的概念和优点1. 参数化查询是一种使用预处理语句和参数来执行SQL查询的方法。

与动态SQL直接拼接字符串的方式不同，参数化查询使用占位符来代替查询条件值，然后通过传递参数的方式执行查询。

2. 在MySQL中，参数化查询可以通过PreparedStatement对象来实现。

例如，我们可以使用如下方式执行参数化查询：```String sql = "SELECT * FROM users WHERE name = ?";PreparedStatement statement = connection.prepareStatement(sql);statement.setString(1, "John");ResultSet resultSet = statement.executeQuery();```这样，我们可以直接将查询条件传递给PreparedStatement对象，而不需要将其直接拼接到SQL语句中。

oracle动态sql语句基本语法Oracle动态SQL语句是一种在运行时动态生成SQL语句的技术。

它可以根据不同的条件和参数生成不同的SQL语句，从而实现更加灵活和高效的数据操作。

下面是Oracle动态SQL语句的基本语法：1. 使用EXECUTE IMMEDIATE语句执行动态SQL语句：EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE department_id = :dept_id' USING dept_id;2. 使用BIND VARIABLES绑定变量：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';EXECUTE IMMEDIATE v_sql USING v_dept_id;END;3. 使用PL/SQL变量拼接SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = ' || v_dept_id;EXECUTE IMMEDIATE v_sql;END;4. 使用CASE语句生成动态SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = ';v_sql := v_sql || CASE WHEN v_dept_id IS NULL THEN 'NULL' ELSE TO_CHAR(v_dept_id) END;EXECUTE IMMEDIATE v_sql;END;5. 使用FOR LOOP生成动态SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id IN (';FOR i IN 1..10 LOOPv_sql := v_sql || i || ',';END LOOP;v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1) || ')';EXECUTE IMMEDIATE v_sql;END;6. 使用SYS_CONTEXT函数获取当前用户信息：DECLAREv_user VARCHAR2(30) := SYS_CONTEXT('USERENV', 'CURRENT_USER');v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE created_by = ''' || v_user || '''';EXECUTE IMMEDIATE v_sql;END;7. 使用DBMS_SQL包执行动态SQL语句：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);DBMS_SQL.CLOSE_CURSOR(v_cursor);END;8. 使用DBMS_SQL.RETURN_RESULT函数返回结果集：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);DBMS_SQL.RETURN_RESULT(v_cursor);DBMS_SQL.CLOSE_CURSOR(v_cursor);END;9. 使用DBMS_SQL.DESCRIBE_COLUMNS函数获取结果集元数据：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);v_col_cnt INTEGER;v_col_desc DBMS_SQL.DESC_TAB;BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);v_col_cnt := DBMS_SQL.FETCH_ROWS(v_cursor);DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_col_desc); DBMS_SQL.CLOSE_CURSOR(v_cursor);END;10. 使用DBMS_SQL.COLUMN_VALUE函数获取结果集列值：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);v_col_cnt INTEGER;v_col_desc DBMS_SQL.DESC_TAB;v_emp_id NUMBER;v_emp_name VARCHAR2(30);BEGINv_sql := 'SELECT employee_id, first_name FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);v_col_cnt := DBMS_SQL.FETCH_ROWS(v_cursor);DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_col_desc); LOOPEXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_emp_id);DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_emp_name);DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_emp_name);END LOOP;DBMS_SQL.CLOSE_CURSOR(v_cursor);END;以上是Oracle动态SQL语句的基本语法，可以根据实际需求进行灵活应用。

SQL通⽤⾏转列，动态列本⼈能⼒有限，请⼤家复制后⾃⾏优化。

使⽤⽅法：select row_number() over(order by convert(date,inDate,105)) rowid,convert(date,inDate,105) ⽇期,sum(case when flag='⼆级报警'then1else0end ) ⼆级报警,sum(case when flag='三级报警'then1else0end ) 三级报警into ##tt --插⼊到全局临时表from pinAdd where inDate>='2020-11-30'group by convert(date,inDate,105)select*from ##ttexec DynamicColumn @table='##tt', --临时表名@firstName='⽇期', --指定按哪⼀列来转换@orderName='rowid'--指定排序列drop table ##tt --⽤完请将全局临时表删除内部实现：ALTER PROCEDURE[dbo].[DynamicColumn]@table varchar(50), --临时表名@firstName varchar(50), --按哪⼀列(列名)来转换@orderName varchar(50) --按哪⼀列来排序ASbegindeclare@sql nvarchar(max),@sql2nvarchar(max),@subStr varchar(500),@id int,@count int,@col varchar(50)set@sql='create table ##Dtb('+quotename(@firstName)+' varchar(50),'set@id=1set@subStr=''exec('select row_number()over(order by convert(varchar(50),'+@orderName+')) id,'+@firstName+' as '+@firstName+' into ##tb from tempdb..'+@table)select@count=count(1) from ##tbwhile(@id<=@count)beginset@sql2='select @col='+@firstName+' from ##tb where id=@id'exec sp_executesql @sql2,N'@col varchar(50) out,@id int',@col out,@idset@subStr=@subStr+quotename(@col)+' varchar(50),'set@id=@id+1endexec(@sql+@subStr+')')set@id=1select row_number()over(order by colid) id,name into #ins from tempdb.dbo.syscolumns where id=object_id( 'tempdb..'+@table) and name!=@firstName order by colid select@count=count(1) from #inswhile(@id<=@count)beginselect@col=name from #ins where id=@idset@sql='insert into ##Dtb select '+quotename(@col,'''')+', '--select * from #ins for xml path('')--set @sql2='select @col=(-- select '+''''''''''+'+convert(varchar(10),['+@col+'])+'+quotename(''',','''')+' from '+@table+' order by '+@orderName+' for xml path('''') --)'set@sql2=concat('select @col=(select ''''''''+convert(varchar(10),[',@col,'])+' , ''''''','' from ',@table,' order by rowid for xml path(''''))') exec sp_executesql @sql2,N'@col varchar(max) out',@col outset@sql=@sql+substring(@col, 1, len(@col)-1)exec(@sql)set@id=@id+1endset@sql=N'select * from ##Dtb where '+quotename(@firstName)+'!=@orderName'exec sp_executesql @sql,N'@orderName varchar(50)',@orderName--set @sql='drop table '+@table--exec(@sql)drop table ##Dtbdrop table ##tbend。

Mysql、SqlServer和Oracle 添加修改删除字段MySql:添加单列:ALTER TABLE 表名ADD 列名数据类型添加多列:ALTER TABLE 表名ADD 列名1 数据类型1,Add 列名2 数据类型2修改单列数据类型:ALTER TABLE 表名CHANGE COLUMN 列名数据类型同时修改多列数据类型:ALTER TABLE 表名CHANGE COLUMN 列名数据类型,CHANGE COLUMN 列名数据类型删除单列:ALTER TABLE 表名DROP COLUMN 列名删除多列:ALTER TABLE 表名DROP COLUMN 列名1,DROP COLUMN 列名2同时添加和修改多列:ALTER TABLE 表名ADD 列名1 数据类型1,CHANGE COLUMN 列名数据类型,DROP COLUMN列名1（COLUMN关键字可以省略）--------------------------------------------------------------------------------------SqlServer(Transact-SQL):添加单列:ALTERTABLE 表名ADD 列名数据类型添加多列:ALTER TABLE 表名ADD 列名1 数据类型1,列名2 数据类型2修改单列数据类型:ALTER TABLE 表名ALTER COLUMN 列名数据类型同时修改多列数据类型:不支持删除单列:ALTER TABLE 表名DROP COLUMN 列名删除多列:ALTER TABLE 表名DROP COLUMN 列名1,列名2同时添加和修改多列:不支持,每次只能进行一种操作.-------------------------------------------------------------------------------------- Oracle(PL/SQL):添加单列:ALTER TABLE 表名ADD 列名数据类型添加多列:ALTER TABLE 表名ADD ( 列名1 数据类型1,列名2 数据类型2)修改单列数据类型:ALTER TABLE 表名MODIFY 列名数据类型同时修改多列数据类型:ALTER TABLE 表名MODIFY ( 列名1 数据类型1,列名2 数据类型2) 删除单列:ALTER TABLE 表名DROP COLUMN 列名删除多列:ALTER TABLE 表名DROP (列名1,列名2)同时添加和修改多列:。

动态SQL详解动态SQL在之前⽤户所编写的PL/SQL程序时有⼀个最⼤的特点：就是所操作的数据库对象（例如：表）必须存在，否则创建的⼦程序就会出问题，⽽这样的操作在开发之中被称为静态SQL操作，⽽动态SQL操作可以让⽤户在定义程序时不指定具体的操作对象，⽽在执⾏时动态的传⼊所需要的数据库对象，从⽽使程序变得更加的灵活。

创建⼀个功能，输⼊表名，输出表中有多少⾏，若没有这个表，则创建这个表。

⾸先禁⽤所有触发器GRANT CREATE ANY TABLE TO SCOTTcreate or replace function get_table_count_fun(p_table_name varchar2) return number asv_sql_statement varchar2(200);--定义操作的SQL语句v_count number;--保存表中记录beginselect count(*) into v_count from user_tables where table_name=UPPER(p_table_name);if v_count=0 then--数据表不存在v_sql_statement:='CREATE TABLE '|| p_table_name ||'(id number ,name varchar2(30) not null)';--创建数据表，注意此处不要忽略空格，按照正常写create table语句的格式加空格，否则⽆法识别execute immediate v_sql_statement;--执⾏动态SQLend if;v_sql_statement:=' select count(*) from '|| p_table_name;--查询数据表记录，注意加空格execute immediate v_sql_statement into v_count;--执⾏动态SQL并保存数据记录return v_count;end;/declarev1 varchar2(100);beginv1:=get_table_count_fun('t100');--查看t100表dbms_output.put_line(v1);end;/EXECUTE IMMEDIATE语句在动态SQL之中EXECUTE IMMEDIATE是最为重要的执⾏命令，使⽤此语句可以⽅便的在PL/SQL程序之中执⾏DML(INSERT/UPDATE/DELETE/单列SELECT)、DDL(CREATE/ALTER/DROP)、DCL(GRANT/REVOKE)语句，EXECUTE IMMEDIATE语法定义如下：EXECUTE IMMEDIATE 动态SQL字符串 [[BULK COLLECT]] INTO ⾃定义变量，...|记录类型|[USING [IN | OUT |IN OUT|绑定参数,...][[RETURNING | RETURN][BULK COLLECT] INTO 绑定参数,...];EXECUTE IMMEDIATE由以下三个主要字句组成：INTO:保存动态SQL执⾏结果，如果返回多⾏记录可以通过BULK COLLECT设置批量保存;USING:⽤来为动态SQL设置占位符设置内容；RETURNING | RETURN:两者使⽤效果⼀样，是取得更新表记录被影响的数据，通过BULK COLLECT来设置批量绑定。

(பைடு நூலகம்
[a] [int]null,
[b] [int]null,
[col_add]as([a]+[b]),
[col_ji]as([a]*[b])
)
on[primary]
--插入数据
insertintotable_name(a,b)
select2,3
--查询出结果
select*fromtable_name
--删掉表
droptabletable_name
/*
abcol_addcol_ji
----------- ----------- ----------- -----------
2356
*/
这样当每插入一条记录的时候，就进行一次计算。
declare@tabletable(aint,bint)
insertinto@table
select2,3
selecta,b,a+bascol_add,a*bascol_jifrom@table
/*结果
abcol_addcol_ji
----------- ----------- ----------- -----------
如何创建动态数据列首先我先写一个小例子declaretabletableaintbintinserttableselect23selectcoljifromtable结果coladdcolji我们可以看到后两列是前面数据运算后的结果我们可以取出这样的结果但是结果是在取数据的时候进行运算的
首先我先写一个小例子
2356
*/
我们可以看到后两列是前面数据运算后的结果
我们可以取出这样的结果，但是结果是在取数据的时候，进行运算的。