×

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

@Florida

向SQL.高手请教，怎样用T-SQL写这个store procedure，实现需求。谢谢。

gpsouth(大熊)
A table has 2 columns:

ColA int
ColB datetime

Input some data as
ColA ColB
1 2/2/2005 (month/day/year)
1 2/3/2005
1 2/10/2005
1 3/10/2005
2 3/1/2005
2 3/5/2005
2 4/1/2005
2 5/1/2005

Need to write a store procedure:
The procedure has an input argument
refTime as datatime

What it do is:
For each group of rows with the same ColA, remove the rows which ColB < refTime, except the one which is the nearest to the refTime.

For example,
if refTime==2/5/2005, the following rows will be removed:
ColA ColB
1 2/2/2005

if refTime==3/6/2005, the following rows will be removed:
ColA ColB
1 2/2/2005
1 2/3/2005
2 3/1/2005
(#2330998@0)
Last Updated: 2005-6-5
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 向SQL.高手请教，怎样用T-SQL写这个store procedure，实现需求。谢谢。 -gpsouth(大熊); 2005-6-5 {710} (#2330998@0)
    A table has 2 columns:

    ColA int
    ColB datetime

    Input some data as
    ColA ColB
    1 2/2/2005 (month/day/year)
    1 2/3/2005
    1 2/10/2005
    1 3/10/2005
    2 3/1/2005
    2 3/5/2005
    2 4/1/2005
    2 5/1/2005

    Need to write a store procedure:
    The procedure has an input argument
    refTime as datatime

    What it do is:
    For each group of rows with the same ColA, remove the rows which ColB < refTime, except the one which is the nearest to the refTime.

    For example,
    if refTime==2/5/2005, the following rows will be removed:
    ColA ColB
    1 2/2/2005

    if refTime==3/6/2005, the following rows will be removed:
    ColA ColB
    1 2/2/2005
    1 2/3/2005
    2 3/1/2005
    • "select top1 ColA, Max(ColB) from table1 where ColB<= refTime group by ColA order by ColA desc." 没太看懂两个Remove是要干吗用的 ？ delete ? -hard20(hard20); 2005-6-5 (#2331205@0)
      • 谢谢。Remove 就是delete 掉那些符合条件的 rows。 -gpsouth(大熊); 2005-6-5 (#2331233@0)
    • oh ...俺没看清楚需求。select 语句应该这么写： Select t1.ColA,t2.ColB from Table1 t1,(select max(ColB) as ColB from table1 where ColB<=refTime) t2 where t1.ColB=t2.ColB. -hard20(hard20); 2005-6-5 (#2331234@0)
      • 那就是delete from Table1 where ColB <= (select max(ColB) from table1 where ColB <=refTime) -hard20(hard20); 2005-6-5 (#2331235@0)
        • delete from Table1 where COlB < (select max(ColB) from table1 where ColB <=refTime) (没等于，sorry ) -hard20(hard20); 2005-6-5 (#2331237@0)
          • 谢谢热心。select 语句还没看懂，不过上面的delete 语句不对。要求是：For each group of rows with the same ColA （'1' or '2' or ... ）, remove the rows which ColB<refTime, -gpsouth(大熊); 2005-6-6 {241} (#2331285@0)
            except the one which is the nearest to the refTime.

            For example,
            if refTime==3/6/2005, the following rows will be removed:
            ColA ColB
            1 2/2/2005
            1 2/3/2005
            2 3/1/2005


            而这两个rows 不要被删掉：
            ColA ColB
            1 2/10/2005
            2 3/5/2005
            • 哦，这样啊，还有each group的要求，俺从小就审题不仔细，不好意思啊。 那就是delete table1 t1 from( select ColA, Max(ColB) from table1 where ColB<= refTime group by ColA ) t2 where t1.ColA=t2.ColA and t1.ColB < t2.ColB -hard20(hard20); 2005-6-6 (#2331440@0)

More Topics

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