×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

不会用view ,用两个cursor做 .实现功能。

SET NOCOUNT ON
DECLARE @i_ID int
DECLARE @s_type varchar(2)
DECLARE @str_type varchar(1000)

DECLARE @tbl1 table
(id int,
str_type varchar(1000) )

Declare cur_id cursor
FOR
SELECT ID FROM test1
GROUP BY ID
ORDER BY ID

OPEN cur_id
FETCH NEXT FROM cur_id INTO @i_ID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @s_type = ''
SET @str_type = ''

DECLARE cur_type cursor
FOR SELECT [TYPE] FROM TEST1
WHERE ID = @i_ID

OPEN cur_type
FETCH NEXT FROM cur_type INTO @s_type

WHILE @@FETCH_STATUS = 0

BEGIN
SET @s_type = @s_type + ','
SET @str_type = @str_type + @s_type

FETCH NEXT FROM cur_type INTO @s_type
END

CLOSE cur_type
DEALLOCATE cur_type

SET @str_type = '''' + LEFT (@str_type, LEN(RTRIM(@str_type))- 1) + ''''

INSERT INTO @tbl1
Values (@i_ID,@str_type)


FETCH NEXT FROM cur_id INTO @i_ID
END

CLOSE cur_id
DEALLOCATE cur_id

SELECT * FROM @tbl1

SET NOCOUNT OFF
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 这里SQL高手还是很多的。俺出个题给你们做做吧。
    某表A中有两列 id, type。顾名思义,ID是unique的。但是一个ID可以对应有N多个type,N不定。

    sample data:
    1 A
    1 C
    2 B
    2 C
    2 D
    3 A
    3 E
    ...

    需要一个View得到 id, all types in comma delimited string。
    上述sample data输出应该是
    1 'A, C'
    2 'B, C, D'
    3 'A, E'

    string 中各个type的顺序不作要求。
    • 1)这个ID明显不是unique的,估计是combination primary key。2)如果是一个view,我想用cursor最简单明了。
      • 是combination primary key。id unique不是指在这个表里unique。您的答案是?
        • 现在在脑子里的是:大致是建立一个cursor,然后foreach中,for one ID,用一个变量循环累加type name,完成一个后,insert到一个临时表。然后在view的最后,select * from temptable。
    • 我的做法:
      1, UDF: SELECT @t = ISNULL( @t, '' ) + ',' + type FROM table WHERE id=@id

      2, SELECT udf( ID) FROM table GROUP BY id
      • 嗯,不错。:)// 加个id在2)里面,就是LZ想要得了。:))
        • 呵呵,这还不是我想要的。因为作为VIEW,在跟其它TABLE或VIEW JOIN的时候,UDF作为一个sub query,performance及其糟糕。比方说假如有1000个unique id,这个UDF就要被CALL1000次。我想知道有没有更好的解。
          顺便说一下,轮胎的UDF里,应该用COALESCE(@t + ',', '') + type,这样可以避免leading comma。
    • 我想标准的SQL是做不了的,不同的数据库应该有不同的方案。ORACLE里面也要用到自定义函数,虽然这函数不需要访问表。写起来有一大堆。
      • Oracle solution (函数来自asktom)
        本文发表在 rolia.net 枫下论坛create or replace type string_agg_type as object
        (
        total varchar2(4000),

        static function
        ODCIAggregateInitialize(sctx IN OUT string_agg_type )
        return number,

        member function
        ODCIAggregateIterate(self IN OUT string_agg_type ,
        value IN varchar2 )
        return number,

        member function
        ODCIAggregateTerminate(self IN string_agg_type,
        returnValue OUT varchar2,
        flags IN number)
        return number,

        member function
        ODCIAggregateMerge(self IN OUT string_agg_type,
        ctx2 IN string_agg_type)
        return number
        );
        /

        create or replace type body string_agg_type
        is

        static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
        return number
        is
        begin
        sctx := string_agg_type( null );
        return ODCIConst.Success;
        end;

        member function ODCIAggregateIterate(self IN OUT string_agg_type,
        value IN varchar2 )
        return number
        is
        begin
        self.total := self.total || ',' || value;
        return ODCIConst.Success;
        end;

        member function ODCIAggregateTerminate(self IN string_agg_type,
        returnValue OUT varchar2,
        flags IN number)
        return number
        is
        begin
        returnValue := ltrim(self.total,',');
        return ODCIConst.Success;
        end;

        member function ODCIAggregateMerge(self IN OUT string_agg_type,
        ctx2 IN string_agg_type)
        return number
        is
        begin
        self.total := self.total || ctx2.total;
        return ODCIConst.Success;
        end;


        end;
        /

        CREATE or replace
        FUNCTION stragg(input varchar2 )
        RETURN varchar2
        PARALLEL_ENABLE AGGREGATE USING string_agg_type;
        /

        CREATE TABLE test (ID NUMBER,TYPE CHAR(1));

        INSERT INTO test VALUES(1,'A');
        INSERT INTO test VALUES(1,'C');
        INSERT INTO test VALUES(2,'B');
        INSERT INTO test VALUES(2,'C');
        INSERT INTO test VALUES(2,'D');
        INSERT INTO test VALUES(3,'A');
        INSERT INTO test VALUES(3,'E');

        SELECT id,stragg(type) FROM test GROUP BY id;更多精彩文章及讨论,请光临枫下论坛 rolia.net
        • 很Cool, 不懂Oracle,看上去好像是Aggregation 的时候,Oracle会调用这么个Hook似的东西,所以就不必另外查表了?
          • 我猜也是这样,这函数似乎是很底层的东西。
            • 嗯,看来确实如此。这篇文章说的就是如何用C#写一段User Defined Aggregate Function,应该符合搂住的意思
        • 狗了一下,发现有这么一段* Extensibility hook to aggregate values over a group during query processing似乎SQL 2005也能做类似的事情。
    • for SQL Server 2005, --->
      SELECT DISTINCT
      a.id,
      (
      SELECT LTrim(RTrim( type )) + ', ' AS [text()]
      FROM #t1 b
      WHERE b.id = a.id
      ORDER BY id, type FROM XML PATH('')
      ) AS lst
      FROM #t1 a
      • 这是Hack! :)
      • for SQL Server 2000 or 7.0, I wrote a simple function (tested) -->
        --table to list
        CREATE TABLE t1(id int, v varchar(20))
        GO

        INSERT t1
        SELECT 1, 'A'
        UNION
        SELECT 1, 'B'
        UNION
        SELECT 1, 'C'
        UNION
        SELECT 2, 'A'
        UNION
        SELECT 2, 'C'
        UNION
        SELECT 3, 'X'
        UNION
        SELECT 3, 'Y'
        UNION
        SELECT 3, 'Z'
        GO


        CREATE FUNCTION typeList(@id int)
        RETURNS varchar(20)
        AS
        BEGIN
        DECLARE @vl varchar(20)
        SELECT @vl=''

        SELECT @vl = @vl + v + ','
        FROM t1
        WHERE id = @id

        IF ( LEN(@vl) > 0 )
        SET @vl = LEFT(@vl, LEN(@vl) - 1 )

        RETURN (@vl)
        END
        GO


        CREATE VIEW t1_view
        AS
        SELECT id, dbo.typelist(id) as type_list
        FROM t1
        GROUP BY id
        GO


        SELECT * FROM t1_view
        GO


        DROP VIEW t1_view
        DROP FUNCTION typelist
        DROP TABLE t1
        GO
    • 不会用view ,用两个cursor做 .实现功能。
      SET NOCOUNT ON
      DECLARE @i_ID int
      DECLARE @s_type varchar(2)
      DECLARE @str_type varchar(1000)

      DECLARE @tbl1 table
      (id int,
      str_type varchar(1000) )

      Declare cur_id cursor
      FOR
      SELECT ID FROM test1
      GROUP BY ID
      ORDER BY ID

      OPEN cur_id
      FETCH NEXT FROM cur_id INTO @i_ID

      WHILE @@FETCH_STATUS = 0

      BEGIN

      SET @s_type = ''
      SET @str_type = ''

      DECLARE cur_type cursor
      FOR SELECT [TYPE] FROM TEST1
      WHERE ID = @i_ID

      OPEN cur_type
      FETCH NEXT FROM cur_type INTO @s_type

      WHILE @@FETCH_STATUS = 0

      BEGIN
      SET @s_type = @s_type + ','
      SET @str_type = @str_type + @s_type

      FETCH NEXT FROM cur_type INTO @s_type
      END

      CLOSE cur_type
      DEALLOCATE cur_type

      SET @str_type = '''' + LEFT (@str_type, LEN(RTRIM(@str_type))- 1) + ''''

      INSERT INTO @tbl1
      Values (@i_ID,@str_type)


      FETCH NEXT FROM cur_id INTO @i_ID
      END

      CLOSE cur_id
      DEALLOCATE cur_id

      SELECT * FROM @tbl1

      SET NOCOUNT OFF
      • failed! :(
        • failed ? 虽然代码比function的长,但效率不一定比那个差 。
          • 题目要求做个view呀。:)
    • SQL 2005 ,用PIVOT做,简单又方便,外面再套一层就行了。
      SELECT ID, [A] AS A, [B] AS B , C AS [C] , D AS [D] , E AS [E],
      F AS [F] , J AS [J] ,K AS [K] ,L AS [L]
      FROM
      (SELECT id,[type]
      FROM test1) t1
      PIVOT
      (
      MAX([type])
      FOR [type] IN
      ( A, B, C, D, E ,F, J, K, L)
      ) AS pvt;
    • select ID, '('+ListAGG(type)+)' from table1 group by ID
      • good! 简单! 不知道sql server 2005以前的版本有没有这个listagg?
        • 现在也没有呀。这只是一个mockup function而已。楼上那是放狗搜出来的吧。好像有点闹笑话了。呵呵。
      • Msg 195, Level 15, State 10, Line 1 'ListAGG' is not a recognized built-in function name. --sqlserver 2005
        • 奇怪... 我没有sql server 2005, 没办法验证...
          • 我有。验证不过 Msg 195, Level 15, State 10, Line 2 'LISTAGG' is not a recognized built-in function name.
          • Although this aggregate is NOT built into SQL Server 2005....
          • From you link, it says

            Such an aggregate might be used on this table in order to produce a list of strings:

            SELECT LISTAGG(String)
            FROM Strings
            
            Output:
            
            'A, B, C'
            

            Although this aggregate is not built into SQL Server 2005, the new system introduces a way of easily achieving this functionality. The most obvious way might be to use the new CLR user-defined aggregates (UDAs). Unfortunately, UDAs have an 8000-byte limit, which severely limits their use when aggregating large sets.

            • o, 我只看了code... google listagg有三页结果... 原来是未来世界的东西... anyhow, 跟我没有关系, 我没有....
    • 如果你有幸用MySQL, 很简单CREATE VIEW TABLE1VIEW AS select id, GROUP_CONCAT(type) from table1 group by id;
      • good! 这是oracle的, 估计sql server 2005以下也得按这个思路做
      • MySQL挺不错的,缺陷是对语法要求很低,对SQL不很熟的人很容易因为错误的语法得到莫名其妙的结果。可惜大公司还没用MySQL作数据库平台的。:(