×

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

@Florida

a SQL问题, 与大侠们共同探讨. I'm thinking about this question for a while, couldn't get a One-Statement solution. With loop structure, it's very easy, but is there a One-Statement solution? Please take a look. Thanks.

brother-pooh(Pooh哥哥)
本文发表在 rolia.net 枫下论坛SQL question:
Please don't use sp, function, trigger or program coding.
Is it possible to use only one SQL statement to achieve the following result?
Of course, please tell if you think One-statement is impossible. (You don't have to give the loop coding).
Thanks.

Sample Table:(dash only put in as space to make look nicer, please ignore dash)
Column 1 is sequencial integer (identity) (similar as Oracle sequence).
Column 2 is varchar with different lens and some are empty (nulls) (let's say empty row).
We don't know how many empty rows we got in between, maybe 0,1, or 40, or more.
----------------------------------------
Col1----------Col2
----------------------
1--------------TDF
2--------------null
3--------------null
4--------------S2
5--------------null
6--------------null
7--------------D666666
8--------------null
9--------------null
10-------------null
11-------------null
.
.
.



Question: Is it possible to use only one SQL statement to update and get result:
(Note: The SQL actually filled nulls in column 2 with its former value.)
----------------------------------------
Col1-------Col2
1--------------TDF
2--------------TDF
3--------------TDF
4--------------S2
5--------------S2
6--------------S2
7--------------D666666
8--------------D666666
9--------------D666666
10-------------D666666
11-------------D666666
.
.
.

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

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / a SQL问题, 与大侠们共同探讨. I'm thinking about this question for a while, couldn't get a One-Statement solution. With loop structure, it's very easy, but is there a One-Statement solution? Please take a look. Thanks. -brother-pooh(Pooh哥哥); 2006-11-10 {1404} (#3313166@0)
    本文发表在 rolia.net 枫下论坛SQL question:
    Please don't use sp, function, trigger or program coding.
    Is it possible to use only one SQL statement to achieve the following result?
    Of course, please tell if you think One-statement is impossible. (You don't have to give the loop coding).
    Thanks.

    Sample Table:(dash only put in as space to make look nicer, please ignore dash)
    Column 1 is sequencial integer (identity) (similar as Oracle sequence).
    Column 2 is varchar with different lens and some are empty (nulls) (let's say empty row).
    We don't know how many empty rows we got in between, maybe 0,1, or 40, or more.
    ----------------------------------------
    Col1----------Col2
    ----------------------
    1--------------TDF
    2--------------null
    3--------------null
    4--------------S2
    5--------------null
    6--------------null
    7--------------D666666
    8--------------null
    9--------------null
    10-------------null
    11-------------null
    .
    .
    .



    Question: Is it possible to use only one SQL statement to update and get result:
    (Note: The SQL actually filled nulls in column 2 with its former value.)
    ----------------------------------------
    Col1-------Col2
    1--------------TDF
    2--------------TDF
    3--------------TDF
    4--------------S2
    5--------------S2
    6--------------S2
    7--------------D666666
    8--------------D666666
    9--------------D666666
    10-------------D666666
    11-------------D666666
    .
    .
    .

    Thanks.更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • tested on Oracle, but should be generic. -zdq(zdq); 2006-11-10 {130} (#3313200@0)
      update sample_table t
      set t.Col2 =
      (SELECT t2.Col2
      FROM sample_table t2
      where t2.Col1 = t.Col1 - 1
      )
      where t.Col2 IS NULL;
      • Seems something wrong.... -zdq(zdq); 2006-11-10 (#3313204@0)
        • Corrected -zdq(zdq); 2006-11-10 {383} (#3313216@0)
          update sample_table t
          set t.t_value = (SELECT t2.t_value
          FROM sample_table t2
          where t2.Col1 = (select max(t3.Col1)
          from sample_table t3
          where t3.t_value IS NOT NULL
          AND t3.Col1 < t.Col1))
          where t.Col2 IS NULL;
          • me too……but I don't like it -newkid(newkid); 2006-11-10 (#3313226@0)
            • ? -brother-pooh(Pooh哥哥); 2006-11-10 (#3313262@0)
            • The query is fine. What is the problem? -looi500(looi); 2006-11-10 (#3313314@0)
          • Great answer! but a misspelling: t_value and Col2 should be the same column -digitworm(digitworm); 2006-11-10 (#3313324@0)
            • You are right, I used t_value for testing, forgot to change all of them back to Col2. Thanks! -zdq(zdq); 2006-11-11 (#3314344@0)
          • This answer is straightforward, and I thought it was not efficient enough. But after looking at the explain plan I had to change my mind, the optimizer is surely smarter than I am. -newkid(newkid); 2006-11-10 (#3313348@0)
    • Check it out -886xyz(cqcq); 2006-11-10 {321} (#3313212@0)
      SELECT COALESCE (t1.col2,
      SELECT t2.col2
      FROM ts t2
      WHERE t2.col2 IS NOT NULL
      AND t2.col1 < t1.col1
      ORDER BY col1 DESC
      FETCH FIRST 1 ROWS ONLY,
      -99999)
      FROM ts t1;
      • what about updates? brother bear wants to update his table. -newkid(newkid); 2006-11-10 (#3313351@0)
        • See #3313843 -886xyz(cqcq); 2006-11-11 (#3314385@0)
    • It's possible in a read-consistent DB like Oracle. The answer I come up with now is pretty ugly so I won't post it here. -newkid(newkid); 2006-11-10 (#3313217@0)
    • mine .....test in MS SQL2000.....And I do not like it either. -hard20(hard20); 2006-11-10 {189} (#3313286@0)
      Select t1.[id],max(t2.[type])
      from test1 t1 , (select [id], [type] from test1
      where [type] is not null
      ) t2

      where t1.[id] > = t2.[id]
      group by t1.[id]
      order by t1.[id]
      • logically wrong: where t1.[id] > = t2.[id]. in LZ's sample data, you will get 'TDF' for all rows -newkid(newkid); 2006-11-10 (#3313368@0)
    • why make it so complicated? it would be hard to maintain it. -sunday8(sunday8); 2006-11-10 (#3313801@0)
    • My try: -shz(shz); 2006-11-10 {144} (#3313843@0)
      update T
      set col2 = (select top 1 t2.col2 from T t2 where t2.col1 < t.col1 and t2.col2 is not null order by t2.col1 desc)
      where t.col2 is null
      • Excellent answer to MS-SQL! -brother-pooh(Pooh哥哥); 2006-11-13 (#3317111@0)
    • 用游标做只要扫描一遍，用SQL实现则要反复扫描表, 这就是为什么我觉得不满意的地方 -newkid(newkid); 2006-11-11 (#3314853@0)
      • 可以用OLAP函数，在Oracle有First_Value,这些对查询make sense,对update意义不大. -886xyz(cqcq); 2006-11-11 (#3315264@0)
        • 在11/12月的Oracle杂志上Tom有解答，用的是Merge和Last_value ignore nulls.对于空值太多的表,这样也行,但没必要全更新. -886xyz(cqcq); 2006-11-15 (#3322078@0)
          • 有URL吗？谢谢！ -newkid(newkid); 2006-11-15 (#3322484@0)
            • 看完了，实在是高! 我以前不知道有 IGNORE NULLS -newkid(newkid); 2006-11-15 (#3322612@0)
      • 使用SQL Update比使用CURSOR好. SQL UPDATE也只要扫描一遍. 它的SUB QUERIES都会用到INDEX(假设COL1有INDEX,COL2没有INDEX). 用CURSOR就会把整个TABLE的COL1和COL2读到程序里, 这就有TRAFFIC OVERHEAD. -looi500(looi); 2006-11-13 (#3317715@0)
        • SQL UPDATE效率最高，但内部也是用游标实现的。SUB QUERY虽然用到INDEX,但相当于每一行都要作一次SELECT, 既扫描了索引又扫描了表。如果CURSOR是在存储过程里实现的，我们可以认为数据都没有离开DB的BUFFER，就没有NETWORK TRAFFIC了。 -newkid(newkid); 2006-11-15 (#3322475@0)
          • 挺撅的 -looi500(looi); 2006-11-15 (#3322512@0)
    • Thanks to DX: zdq, newkid, looi500, 886xyz, hard20, shz, digitworm and sunday8,etc. Excellent answers and discussions! Just one more question to discuss... ? -brother-pooh(Pooh哥哥); 2006-11-13 {475} (#3317155@0)
      SHZ gave an excellent answer to MS-SQL! I really like the idea as it solved with only one level sub query.
      Unfortunately, "ORDER BY" is not allowed in Oracle sub-query, is there a similar sql in Oracle? Update the table with only one level inner select? (like shz's)

      The following is SHZ's solution to MS-SQL:
      -- --
      update T
      set col2 = (select top 1 t2.col2 from T t2 where t2.col1 < t.col1 and t2.col2 is not null order by t2.col1 desc)
      where t.col2 is null
      ;
      • In Oracle, you can't do ORDER BY and rownum < xx in one level query -zdq(zdq); 2006-11-13 {85} (#3317225@0)
        You have to use order by first, then use rownum < xx to get the TOP xx sorted records

More Topics

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