none
SQL 动态语句 RRS feed

  • 问题

  • select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程:这个句子怎么理解,尤其是“isnull(@sql + ',' , '') ”

    'select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b':这个句子中的'select * from (select * from tb) a为什么要从结果集里select 直接从tb查询数据,不行吗?

     

     

    declare @sql varchar(8000)
    select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
    exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

    2013年11月25日 2:26

答案

  • isnull函数一定要加,不然的话

    因为如果表中某一行的课程是null,那么得出的结果也是null

    USE [tempdb]
    GO
    CREATE TABLE #tb
    (课程 NVARCHAR(200),
    分数 DECIMAL(18,2)
    )
    
    --DROP TABLE [#tb]
    
    INSERT INTO [#tb]
    SELECT '',80 UNION ALL
    SELECT '数学',90 UNION ALL
    SELECT '英语',70 
    
    SELECT * FROM [#tb]
    问题一:

    DECLARE @sql NVARCHAR(4000)
    select @sql = isnull(@sql + ',' , '') + 课程 from #tb group by 课程
    PRINT @sql

    上面语句print出来的结果是

    ,数学,英语

    DECLARE @sql NVARCHAR(4000)
    select @sql = @sql + 课程 from #tb group by 课程
    PRINT @sql

    不加isnull函数,print出来的结果是什么也没有,因为第一行中课程那一列是null,导致拼接后面的各个课程名的时候也变成了NULL

    问题二:

    DECLARE @sql NVARCHAR(4000)
    select @sql = isnull(@sql + ',' , '') + 课程 from #tb group by 课程
    PRINT @sql
    exec ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b')

    可以直接从tb查询数据

    不过这里有一个问题,如果第一行中课程那一列是null,那么print出来的结果是:,数学,英语

    执行EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 就会报错

    可以先加一下判断

    DECLARE @sql NVARCHAR(4000)
    SELECT  @sql = ISNULL(@sql + ',', '') + 课程
    FROM    #tb
    GROUP BY 课程
    PRINT @sql
    IF ( CHARINDEX(@sql, ',') = 0 )
        BEGIN
            SET @sql = SUBSTRING(@sql, 2, LEN(@sql))
            EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 
        END
    ELSE
        BEGIN
            EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 
        END


    • 已编辑 Steven.桦仔 2013年11月25日 11:35 修改答案
    • 已标记为答案 Ikkyuu 2013年11月26日 7:19
    2013年11月25日 11:34
  • 您好,
    您將它想像成利用for...next去指定變量的值!

    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    • 已编辑 亂馬客 2013年11月26日 7:45
    • 已标记为答案 Ikkyuu 2013年11月26日 7:54
    2013年11月26日 7:44
  • 您好,

    COALESCE ( expression [ ,...n ] )  可以用多個參數哦!

    會傳回 第1個 不為null的參數。

    DECLARE @a VARCHAR(10), @b VARCHAR(10), @c VARCHAR(10)
    SET @b = '2';
    SET @c = '3';
    SELECT COALESCE( @a, @b, @c ); -- return 2
    SET @b = NULL;
    SELECT COALESCE( @a, @b, @c ); -- return 3
    SET @c = NULL;
    SELECT COALESCE( @a, @b, @c, NULL, '123'); -- return 123
    我只使用2個參數! 只是因為裡面有個串接 ',' 所以讓您誤會了吧!

    COALESCE(@columns + ',' + column_namecolumn_name)

    就像上面所說的,使用Select @變量 From Table ,您把它當成是For....Next來理解!

    所以第1筆資料時,因為沒有設定 @columns 這個變量的值,所以它是null, null + 其他的值也會是Null,所以就會把 @columns 指定成 column_name 的值 (假設是 C1)。

    所以這時 變量@columns 的內容為  C1。

    到第2筆資料時,變量@columns的值不為Null ,所以就會傳回 變量@columns + ',' + 第2筆資料column_name 的值 (假設是 C2)。

    所以這時, 變量@columns 的內容為  C1,C2。

    您可參考:http://msdn.microsoft.com/zh-cn/library/ms190349.aspx


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    • 已标记为答案 Ikkyuu 2013年11月27日 1:33
    2013年11月26日 9:23

全部回复

  • 您好,

    isnull(@sql + ',' , '')
    因為一開始@sql的值為null,所以回傳值為'',當不為null時,回傳值會在原本@sql的後面再加一個 逗號 。
    這樣是因為想要組出
    课程1,课程2,课程3 等等的值出來


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2013年11月25日 4:50
  • isnull函数一定要加,不然的话

    因为如果表中某一行的课程是null,那么得出的结果也是null

    USE [tempdb]
    GO
    CREATE TABLE #tb
    (课程 NVARCHAR(200),
    分数 DECIMAL(18,2)
    )
    
    --DROP TABLE [#tb]
    
    INSERT INTO [#tb]
    SELECT '',80 UNION ALL
    SELECT '数学',90 UNION ALL
    SELECT '英语',70 
    
    SELECT * FROM [#tb]
    问题一:

    DECLARE @sql NVARCHAR(4000)
    select @sql = isnull(@sql + ',' , '') + 课程 from #tb group by 课程
    PRINT @sql

    上面语句print出来的结果是

    ,数学,英语

    DECLARE @sql NVARCHAR(4000)
    select @sql = @sql + 课程 from #tb group by 课程
    PRINT @sql

    不加isnull函数,print出来的结果是什么也没有,因为第一行中课程那一列是null,导致拼接后面的各个课程名的时候也变成了NULL

    问题二:

    DECLARE @sql NVARCHAR(4000)
    select @sql = isnull(@sql + ',' , '') + 课程 from #tb group by 课程
    PRINT @sql
    exec ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b')

    可以直接从tb查询数据

    不过这里有一个问题,如果第一行中课程那一列是null,那么print出来的结果是:,数学,英语

    执行EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 就会报错

    可以先加一下判断

    DECLARE @sql NVARCHAR(4000)
    SELECT  @sql = ISNULL(@sql + ',', '') + 课程
    FROM    #tb
    GROUP BY 课程
    PRINT @sql
    IF ( CHARINDEX(@sql, ',') = 0 )
        BEGIN
            SET @sql = SUBSTRING(@sql, 2, LEN(@sql))
            EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 
        END
    ELSE
        BEGIN
            EXEC ('select * from #tb a pivot (max(分数) for 课程 in (' + @sql + ')) b') 
        END


    • 已编辑 Steven.桦仔 2013年11月25日 11:35 修改答案
    • 已标记为答案 Ikkyuu 2013年11月26日 7:19
    2013年11月25日 11:34
  • 2013年11月25日 11:36
  • 能推荐一本讲述动态语句的书吗?

    就是这些动态语句是怎么拼出来的。看不懂那么多的,+这些符号。

    2013年11月26日 7:21
  • 这个没有的哦,都是平时积累的
    2013年11月26日 7:34
  • 那些语句别人是怎么写出来的呢?

    总有语法规范吧。

    网上说,和sql一样,我看W3SCHOOL上的也不一样啊。

    2013年11月26日 7:36
  • 您好,
    您將它想像成利用for...next去指定變量的值!

    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    • 已编辑 亂馬客 2013年11月26日 7:45
    • 已标记为答案 Ikkyuu 2013年11月26日 7:54
    2013年11月26日 7:44
  • 这是你以前给的代码,COALESCE这个东西为什么有那么多的参数,我看libary里只有两个参数的。这是为什么?

    SELECT @columns = COALESCE(@columns + ',' + column_name,  column_name)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = '科目余额表'
    AND column_name NOT IN ('科目代码', '科目名称')

    2013年11月26日 8:08
  • 这本书吧《程序员的SQL金典》

    http://baike.baidu.com/link?url=_VaFoh6d2ijFSKHT09xIWNgsJj-iJa-m26dL4yBeVQch0fCx2868yB-ribd41Nbatw8OS74ve03LgpoOrVcXJq

    2013年11月26日 8:14
  • 是挺全的,会不会太老旧了。
    2013年11月26日 8:31
  • 您好,

    COALESCE ( expression [ ,...n ] )  可以用多個參數哦!

    會傳回 第1個 不為null的參數。

    DECLARE @a VARCHAR(10), @b VARCHAR(10), @c VARCHAR(10)
    SET @b = '2';
    SET @c = '3';
    SELECT COALESCE( @a, @b, @c ); -- return 2
    SET @b = NULL;
    SELECT COALESCE( @a, @b, @c ); -- return 3
    SET @c = NULL;
    SELECT COALESCE( @a, @b, @c, NULL, '123'); -- return 123
    我只使用2個參數! 只是因為裡面有個串接 ',' 所以讓您誤會了吧!

    COALESCE(@columns + ',' + column_namecolumn_name)

    就像上面所說的,使用Select @變量 From Table ,您把它當成是For....Next來理解!

    所以第1筆資料時,因為沒有設定 @columns 這個變量的值,所以它是null, null + 其他的值也會是Null,所以就會把 @columns 指定成 column_name 的值 (假設是 C1)。

    所以這時 變量@columns 的內容為  C1。

    到第2筆資料時,變量@columns的值不為Null ,所以就會傳回 變量@columns + ',' + 第2筆資料column_name 的值 (假設是 C2)。

    所以這時, 變量@columns 的內容為  C1,C2。

    您可參考:http://msdn.microsoft.com/zh-cn/library/ms190349.aspx


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    • 已标记为答案 Ikkyuu 2013年11月27日 1:33
    2013年11月26日 9:23
  • COALESCE(@columns + ',' + column_namecolumn_name)

    这里是三个参数吗?

    这里的 ',' 和两个+有什么用?

    column_name这个东西是INFORMATION_SCHEMA.COLUMNS的字段吗?既然第一个column_name已经去到值了,为什么还要用第二个column_name?

    INFORMATION_SCHEMA.COLUMNS这个是系统表吗?

    2013年11月27日 1:33
  • LZ你可以查询一下嘛

    SELECT * FROM  INFORMATION_SCHEMA

    INFORMATION_SCHEMA应该不是系统表,用DAC连接也查询不到数据

    还有LZ可以思考一下

    SELECT COALESCE('22' + ',' + '66',  '77')

    SELECT COALESCE('' + ',' + '66',  '77')

    结果----------------------------------------------


    -----
    22,66

    (1 行受影响)


    ----
    ,66

    (1 行受影响)

    • 已建议为答案 ivaskida 2013年11月27日 6:39
    2013年11月27日 1:39
  • 您好,

    就像使用變量透過For ... Next來組出 1,2,3 的字串一樣。

    需要判斷除了第1個字串,不需要加上逗號外,其他的都要加入逗號,這樣出來就會為1,2,3

    一開始@columns值為null。

    上面SQL Script

    COALESCE的第1個參數為 @columns + ',' + column_name ,第2個參數為 column_name 

    當跑到第1筆資料時,第1個參數為 @columns + ',' + column_name

    @columns值為null, column_name欄位值為C1,所以 null + ',' + 'C1' 的值為 null

    所以要往下找第2個參數 column_name 的值為C1 ,所以就傳出 C1。

    SELECT @columns = COALESCE(@columns + ',' + column_name,  column_name)

    所以 @columns 的值就由null 變成了 C1

    跑第2筆資料時,第1個參數為 @columns + ',' + column_name

    @columns值為C1 , column_name欄位值為C2, 'C1' + ',' + 'C2' 的值為 'C1,C2',因為不為null,所以就傳出  'C1,C2'

    SELECT @columns = COALESCE(@columns + ',' + column_name,  column_name)

    所以 @columns 的值就由C1 變成了 C1,C2

    跑第3筆資料時,第1個參數為 @columns + ',' + column_name

    @columns值為C1,C2 , column_name欄位值為C3, 'C1,C2' + ',' + 'C3' 的值為 'C1,C2,C3',因為不為null,所以就傳出  'C1,C2,C3'

    SELECT @columns = COALESCE(@columns + ',' + column_name,  column_name)

    所以 @columns 的值就由C1,C2 變成了 C1,C2,C3

    ... 由此類推 ...

    希望對您有幫助!

    INFORMATION_SCHEMA 信息架构视图 (Transact-SQL)

     [SQL]將某個欄位值以逗號串起來


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/


    • 已建议为答案 ivaskida 2013年11月27日 6:37
    • 已编辑 亂馬客 2013年11月28日 9:37
    2013年11月27日 5:45
  • @columns值為null, column_name欄位值為C1,所以 null + ',' + 'C1' 的值為 null

    这一句中的:null + ',' + 'C1' 的值為 null 为什么是NULL。

    还有,这一条语句的结果是不是就是:@columns值為'C1,C2,C3'?也就是说,@columns是一个以,隔开的字符串了?还是说结果集是一个list?

    2013年11月27日 6:37
  • 谢谢,明白了

    ,在‘’之外是分隔符,在‘’之内是字符串;

    +是连接符。

    2013年11月27日 6:39