sql语句学习笔记(12)-动态sql语句

所谓动态就是根据数据库中的数据自动生成sql语句。看一个列子

捐款表tb_donation的结构如下：

id          name          direction         amount

1            a                  地震                200

2            a                  旱灾                400

3            a                  地震                400

5            b                  地震                200

9            b                  旱灾                200

11          c                   地震                200

...         ...                  ...                    ...

...         ...                  ...                    ...

...         ...                  ...                    ...

 

 

要求：统计捐款人的各类捐款额

如：

name    地震       旱灾

a           600       400

...          ...          ...

 

也就是将行转换成列显示出来

在sql语句学习笔记（11）中是这样实现的。

这里使用了一个函数case when来实现，有点像编程语言中的case...switch

SELECT NAME ,SUM(CASE  direction WHEN '地震' then amount end) as 地震,
SUM(CASE direction WHEN '旱灾' then amount end) as 旱灾
FROM tb_donation td
GROUP BY td.[name]

 

然而如果direction又增加了新的类型，比如“水灾“，怎么办，按照（11）的写法，只有再增加一个一条case direction when '水灾' then amount end,如果增加了很多类型，难道一条一条的加吗，岂不很麻烦了。这时候动态sql就发挥了左右。如下：

DECLARE @sql NVARCHAR(500)--用于保存sql语句表达式，这个要足够大，否则sql会被截断。
SET @sql = 'select name '
SELECT @sql = @sql + ',SUM (CASE direction WHEN '''

+ direction + ''' THEN amount END) AS '+ --注意when后面用direction，而不是具体的字段值了（旱灾，水灾，地震...）
direction from tb_donation group by direction--先按direction分组，这样才可以生成不同的direction列
SET @sql = @sql + ' from tb_donation group by name'--最后按name分组
PRINT @sql --打印sql表达式，测试用的代码。
EXECUTE sp_executesql @sql--调用系统存储过程sp_executesql 执行sql表达式。

别看这段代码很长，但是随便你数据库中的direction变化，都能自动生成不同的direction值，比如你又加了”水灾“，”海啸“

这条sql语句会自动生成这两列

name    地震       旱灾    水灾    海啸

a           600       400    200    400

...          ...          ...       ....       ....