×

Loading...
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

@Calgary

向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

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论