This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / 向SQL.高手请教，怎样用T-SQL写这个store procedure，实现需求。谢谢。
-gpsouth(大熊);
2005-6-5
{710}
(#2330998@0)
-
"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,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
-gpsouth(大熊);
2005-6-6
{241}
(#2331285@0)
-
哦，这样啊，还有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)