×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

再来一个SQL问题探讨 - Concatenation. Thanks for your input.

brother-pooh(Pooh哥哥)
本文发表在 rolia.net 枫下论坛再来一个SQL问题探讨

以下这个问题当然可以用循环的方式来解决，但是否有好的方法，比如，一个或几个SQL语句来解决？
目的是想不使用Loop来解决这个问题
You should give one or a few SQLs then the result will concatenate all the coloumn C2 according to C1 value.
(You allow to create/use one or more temporary tables if you believe it will give efficiency.)
You can give specific solution to Oracle, MS-SQL or DB2, etc. as long as it is a good solution to that system.

初始条件：
Sample Table T1
----------------------------------------------------
Id C1 C2
-------------------------------
1 101 AA
2 101 BBBB
3 101 HHHH
4 102 CCC
5 102 KKKKKK
6 105 ZZ
. . .
. . .
. . .
. . .


(Note: You could have multiple rows repeat for the same C1 value)
(Note: Id is a consistent integer number)


Result Set
Table T2
-----------------------------------------------------
NewId NewC1 NewC2
1 101 AABBBBHHHH
2 102 CCCKKKKKK
3 105 ZZ
. . .
. . .


Thanks for input.
:)更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#3335046@0)
Last Updated: 2006-11-22
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 再来一个SQL问题探讨 - Concatenation. Thanks for your input. -brother-pooh(Pooh哥哥); 2006-11-22 {1293} (#3335046@0)
    本文发表在 rolia.net 枫下论坛再来一个SQL问题探讨

    以下这个问题当然可以用循环的方式来解决，但是否有好的方法，比如，一个或几个SQL语句来解决？
    目的是想不使用Loop来解决这个问题
    You should give one or a few SQLs then the result will concatenate all the coloumn C2 according to C1 value.
    (You allow to create/use one or more temporary tables if you believe it will give efficiency.)
    You can give specific solution to Oracle, MS-SQL or DB2, etc. as long as it is a good solution to that system.

    初始条件：
    Sample Table T1
    ----------------------------------------------------
    Id C1 C2
    -------------------------------
    1 101 AA
    2 101 BBBB
    3 101 HHHH
    4 102 CCC
    5 102 KKKKKK
    6 105 ZZ
    . . .
    . . .
    . . .
    . . .


    (Note: You could have multiple rows repeat for the same C1 value)
    (Note: Id is a consistent integer number)


    Result Set
    Table T2
    -----------------------------------------------------
    NewId NewC1 NewC2
    1 101 AABBBBHHHH
    2 102 CCCKKKKKK
    3 105 ZZ
    . . .
    . . .


    Thanks for input.
    :)更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • 啊？这问题不是刚问过吗？ORACLE解决方案：到asktom.oracle.com搜索一下stragg -newkid(newkid); 2006-11-22 (#3335132@0)
    • Try UDF? -marsher(marsher); 2006-11-22 (#3335143@0)
    • One SQL Query in MySQL -looi500(looi); 2006-11-22 {249} (#3336200@0)
      Select count(1) as NewId, a.C1 as NewC1, a.NewC2
      from (select c.C1, group_concat(c.C2 ORDER BY c.Id SEPARATOR '') as NewC2 from T1 c group by c.C1) as a, (select distinct d.C1 from T1 d) as b
      where a.C1 >= b.C1
      group by a.C1
      order by NewId

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论