sql - 按不使用光标分组

Question

考虑以下查询。我为此查询创建了一个游标。我的问题是无法在游标中正常工作来分组。但是当我执行查询时，它会按结果给出完美的分组。

当我使用 FETCH NEXT 和 WHILE 循环迭代游标时，它会为同一 point_id 提供多个组。

你能帮我解决这个问题吗？？？提前致谢..

select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
group by point_id,timestamp,_val


DECLARE MYCUR CURSOR 
FOR
    select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
    from TCF1_PULLCORD 
    where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
    group by point_id,timestamp,_val

Answer 1

这是您的数据库表达“请不要对我使用光标”的方式。 :D

开个玩笑，但说真的，您会发现遍历表变量比使用游标具有更好的性能。

DECLARE @timestamps TABLE( 
    TS DATETIME,
    POINT_ID VARCHAR(100),
    _VAL VARCHAR(100)
    )
DECLARE @currTimeStamp DATETIME 

INSERT INTO @timestamps 
select timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'
group by point_id,timestamp,_val


WHILE (SELECT COUNT(TS) FROM @timestamps > 0)
BEGIN
    SELECT @currTimeStamp = MIN(TS) FROM @timestamps
    --Do work here
    ...

    --Delete the timestamp we just worked on
    DELETE FROM @timestamps WHERE TS = @currTimeStamp
END 

此外，除非您的 SELECT 正在做我们在这里看不到的其他事情，否则 GROUP BY 应该是不必要的。如果您没有执行任何聚合函数(例如 SUM、MAX、MIN)，而您实际上只是想获得独特的组合，那么 SELECT DISTINCT 是一种更有效的方法。

在这种情况下，您的选择将是:

select DISTINCT timestamp as 'TS',REPLACE(REPLACE(POINT_ID,'[','_'),']','_') AS POINT_ID,_VAL 
from TCF1_PULLCORD 
where timestamp between '11/01/2011 6:30:00 AM' and '12/01/2011 6:29:59 AM'