代码之家 › 专栏 › 技术社区 › soren.enemaerke

SQL单查询更新

sql-update tsql sql

soren.enemaerke · 技术社区 · 16 年前

我必须将值插入数据库中的一个新列中,但我不能以一致的方式来处理这个问题。有很多数据,所以手工做任何事情都是不可能的。我来安排一下舞台:

我们有一个名为occurrence的表和一个名为occurrenceBuckets的表,其中每个occurrence都引用它被分配到的bucket。以前这是单向引用,但由于各种原因,我们决定将引用从OccurrenceBucket添加回第一个引用(即首次)。现在的表格如下:

CREATE TABLE Occurrence
  OccurrenceID uniqueidentifier,
  OccurrenceBucketID uniqueidentifier,
  OccurrenceTime datetime,
  OccurrenceMessage nvarchar
  ...other meta data...

CREATE TABLE OccurrenceBucket
  OccurrenceBucketID uniqueidentifier,
  ...other meta data...
  FirstOccurrenceID uniqueidentifier,
  FirstOccurrenceTime datetime,
  FirstOccurrenceMessage nvarchar

我正在寻找一种方法来确定属于bucket的第一个事件,并将第一个occurrenceID、第一个occurrenceTime和第一个occurrenceMessage的值分配给所有occurrence bucket的第一个事件。

你们当中有没有SQL专家有时间帮我解决问题,我所有的尝试都是为了产生错误或不完整的事件选择。

3 回复 | 直到 14 年前

Adriaan Stander 16 年前

你可以试试这个

DECLARE @Occurrence TABLE(
        OccurrenceID INT,
        OccurrenceBucketID INT,
        OccurrenceTime DATETIME,
        OccurrenceMessage VARCHAR(MAX)
)

INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 1, 1, '01 Jan 2009', 'A'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 2, 1, '02 Jan 2009', 'B'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 3, 1, '03 Jan 2009', 'C'


INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 4, 2, '04 Jan 2009', 'D'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 5, 2, '05 Jan 2009', 'E'
INSERT INTO @Occurrence (OccurrenceID,OccurrenceBucketID,OccurrenceTime,OccurrenceMessage)  
SELECT 6, 2, '06 Jan 2009', 'F'

SELECT * FROM @Occurrence

DECLARE @OccurrenceBucket TABLE(
        OccurrenceBucketID INT,
        FirstOccurrenceID INT,
        FirstOccurrenceTime DATETIME,
        FirstOccurrenceMessage VARCHAR(MAX)
)

INSERT INTO @OccurrenceBucket (OccurrenceBucketID) SELECT 1
INSERT INTO @OccurrenceBucket (OccurrenceBucketID) SELECT 2

SELECT * FROM @OccurrenceBucket


UPDATE  @OccurrenceBucket
SET     FirstOccurrenceID = OccurrenceID,
        FirstOccurrenceTime = OccurrenceTime,
        FirstOccurrenceMessage = OccurrenceMessage
FROM    @OccurrenceBucket oc INNER JOIN
        (
            SELECT  o.*
            FROM    @Occurrence o INNER JOIN
                    (
                        SELECT  OccurrenceBucketID,
                                MIN(OccurrenceID) FirstOccurrenceID
                        FROM    @Occurrence
                        GROUP BY OccurrenceBucketID
                    ) Mins ON o.OccurrenceID = mins.FirstOccurrenceID
        ) Vals ON oc.OccurrenceBucketID = Vals.OccurrenceBucketID

SELECT * FROM @OccurrenceBucket

编辑:

UPDATE  @OccurrenceBucket 
SET     FirstOccurrenceID = OccurrenceID, 
        FirstOccurrenceTime = OccurrenceTime, 
        FirstOccurrenceMessage = OccurrenceMessage 
FROM    @OccurrenceBucket oc INNER JOIN 
        ( 
                SELECT  o.* 
                FROM    @Occurrence o INNER JOIN 
                                ( 
                                        SELECT  OccurrenceBucketID, 
                                                        MIN(OccurrenceTime) FirstOccurrenceTime 
                                        FROM    @Occurrence 
                                        GROUP BY OccurrenceBucketID 
                                ) Mins ON o.OccurrenceTime = mins.FirstOccurrenceTime 
        ) Vals ON oc.OccurrenceBucketID = Vals.OccurrenceBucketID

Joey Gumbo 16 年前

好吧,要在桶中找到第一个事件,下面的工作不应该做吗?

SELECT TOP 1
  OccurranceID, OccurranceTime, OccurranceMessage
FROM Occurance
WHERE
  OccurranceBucketID = @OccurranceBucketID
ORDER BY
  OccurranceTime ASC

您可以将返回的字段分配给变量,然后相应地更新OccurrenceBucket。

^{注意:“发生”中没有“A”。}

Andy Jones 16 年前

此答案取决于每次事件的发生时间是唯一的:

更新ocpbuck集
occBuck.FirstOccurrenceID=发生的occ.OccurrenceID,
occBuck.FirstOccurrenceTime=发生时间,
occBuck.FirstOccurrenceMessage=occ.OccurrenceMessage
从
dbo.occurrencebucket作为ockbuck
内部联接dbo.occBuck.occurrenceBucketId上作为occ出现=occ.occurrenceBucketId
内部联接(选择OccurrenceBucketId,
Min(occurrenceTime)作为“minoccurrenceTime”
源dbo.occurrence
按occ.occurrencebucketid=minocc.occurrencebucketid和
occ.occurrenceTime=minocc.minoccurrenceTime