请帮助我，我在这里下注.目前，我正在使用sql -2000 [英] plz help me I stake here. currenly I am working in sql -2000

问题描述

请帮助我，我在这里下注.目前，我正在使用sql -2000.
和我的桌子是这样的:-

plz help me I stake here. currenly I am working in sql -2000.
and My table like this :-

PerformanceDate	  Score	  Category Reason	UserName	Event_Date
-----------------------------------------------------------------------------------
2009-07-14 11:20    0   	L2  MVR     	System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  MVR     	System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  Collision   System  	2009-07-14 11:20
2009-07-14 11:20    4   	L2  MVR     	System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  MVR     	System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  Collision   System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  Collision   System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  MVR     	System  	2009-07-14 11:20
2009-07-14 11:20    0   	L2  MVR     	System  	2009-07-14 11:20

而且我需要像这样的输出.

AND I NEED A OUTPUT LIKE THIS.

<pre lang="css">

PerformanceDate   Score   Category Reason   UserName    Event_Date
-----------------------------------------------------------------------------------
2009-07-14 11:20    0       L2  MVR         System      2009-07-14 11:20
2009-07-14 11:20    0       L2  Collision   System      2009-07-14 11:20
2009-07-14 11:20    4       L2  MVR         System      2009-07-14 11:20
2009-07-14 11:20    0       L2  MVR         System      2009-07-14 11:20
2009-07-14 11:20    0       L2  Collision   System      2009-07-14 11:20
2009-07-14 11:20    0       L2  MVR         System      2009-07-14 11:20
</pre>

注意:如果以前的行数据和当前的行数据都相同，则消除当前的数据.

NOTE : if previous rows data and current rows data both are same then eliminate the current data.

推荐答案

使用DISTINCT关键字，SQL Server不会返回重复项数据.

选择DISTINCT(....

哦，您希望它比这更复杂.我认为您可能需要使用游标才能做到这一点.

Use the DISTINCT keyword, and SQL Server won''t return duplicate data.

select DISTINCT (....

Oh, you want it a little more complex than that. I would think you may need to use a cursor to do this.

尝试一下.

对于Sql Server 2000

Try this.

For Sql Server 2000

SELECT IDENTITY(int,1,1)  AS row_num,PerformanceDate, Score ,Category ,Reason ,UserName,Event_Date
 into #temptable
  FROM tbl_Temp

SELECT
    [current].PerformanceDate,[current].Score ,[current].Category ,[current].Reason ,[current].UserName,[current].Event_Date,row_num
FROM
    #temptable AS [current]
LEFT OUTER JOIN
    #temptable AS previous
    ON [current].PerformanceDate = previous.PerformanceDate
    AND [current].Score = previous.Score
    AND [current].Category = previous.Category
    AND [current].Reason = previous.Reason
    AND [current].UserName = previous.UserName
      AND [current].Event_Date = previous.Event_Date
    AND [current].row_num = previous.row_num + 1
WHERE
    [current].PerformanceDate = previous.PerformanceDate
    AND [current].Score <> previous.Score
    AND [current].Category <>previous.Category
    AND [current].Reason <> previous.Reason
    AND [current].UserName <>previous.UserName
      AND [current].Event_Date <>previous.Event_Date
ORDER BY
    [current].PerformanceDate

WITH CTE_TEST AS
(
    SELECT PerformanceDate, Score ,Category ,Reason ,UserName,Event_Date,
           ROW_NUMBER() OVER (PARTITION BY PerformanceDate, Score ,Category ,Reason ,UserName,Event_Date ORDER BY PerformanceDate) AS row_num
    FROM tbl_Temp
)
SELECT
    [current].PerformanceDate,[current].Score ,[current].Category ,[current].Reason ,[current].UserName,[current].Event_Date,
FROM
    CTE_TEST AS [current]
LEFT OUTER JOIN
    CTE_TEST AS previous
    ON [current].PerformanceDate = previous.PerformanceDate
    AND [current].Score = previous.Score
    AND [current].Category = previous.Category
    AND [current].Reason = previous.Reason
    AND [current].UserName = previous.UserName
      AND [current].Event_Date = previous.Event_Date
    AND [current].row_num = previous.row_num + 1
WHERE
    [current].PerformanceDate = previous.PerformanceDate
    AND [current].Score <> previous.Score
    AND [current].Category <>previous.Category
    AND [current].Reason <> previous.Reason
    AND [current].UserName <>previous.UserName
      AND [current].Event_Date <>previous.Event_Date
ORDER BY
    [current].PerformanceDate

我已经在桌子上测试过了.它的工作鳍.如果没有用，请将您的表格脚本发送给我，我会帮您

I have tested it with my table. Its working fin. If it didnt work, send me your table script, i will help you

这篇关于请帮助我，我在这里下注.目前，我正在使用sql -2000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！