代码之家 › 专栏 › 技术社区 › stackoverflowuser

查询帮助

tsql sql-server-2005 sql-server sql

stackoverflowuser · 技术社区 · 15 年前

根据下表

ID      Effort      Name
-------------------------
1       1           A
2       1           A
3       8           A
4       10          B
5       4           B
6       1           B
7       10          C
8       3           C
9       30          C

我想检查一个名称的总工作量是否小于40,然后为该名称添加一行,其中efforce=40-(total efforce)。新行的ID可以是任何内容。如果总工作量大于40,则将其中一行的数据构造为40。

因此,应用上表中的逻辑后,

ID      Effort      Name
-------------------------
1       1           A
2       1           A
3       8           A
10      30          A

4       10          B
5       4           B
6       1           B
11      25          B

7       10          C
8       3           C
9       27          C

我正在考虑打开一个光标,保留总工作量的计数器,并根据逻辑在另一个临时表中插入现有行和新行。

我不确定这是否是一种有效的处理方法。我想知道有没有更好的方法。

4 回复 | 直到 15 年前

Quassnoi 15 年前

在 SQL Server 2008 ,这可以用一个 MERGE 声明:

DECLARE @efforts TABLE (id INT NOT NULL PRIMARY KEY, effort INT NOT NULL, name CHAR(1))

INSERT
INTO    @efforts
VALUES  (1, 1, 'A'),
        (2, 1, 'A'),
        (3, 8, 'A'),
        (4, 10, 'B'),
        (5, 4, 'B'),
        (6, 1, 'B'),
        (7, 10, 'C'),
        (8, 3, 'C'),
        (9, 30, 'C'),
        (10, 60, 'C')

SELECT  *
FROM    @efforts
ORDER BY
        name, id

;WITH    total AS
        (       SELECT  *
                FROM    @efforts e
                UNION ALL
                SELECT  ROW_NUMBER() OVER(ORDER BY name) +
                        (
                        SELECT  MAX(id)
                        FROM    @efforts
                        ),
                        40 - SUM(effort),
                        name
                FROM    @efforts
                GROUP BY
                        name
                HAVING  SUM(effort) < 40
        ),
        source AS
        (
        SELECT  *,
                (
                SELECT  SUM(effort)
                FROM    total ep
                WHERE   ep.name = e.name
                        AND ep.id <= e.id
                ) AS ce,
                COALESCE(
                (
                SELECT  SUM(effort)
                FROM    total ep
                WHERE   ep.name = e.name
                        AND ep.id < e.id
                ), 0) AS cp
        FROM    total e
        )
MERGE
INTO    @efforts e
USING   source s
ON      e.id = s.id
WHEN MATCHED AND 40 BETWEEN cp AND ce THEN
UPDATE
SET     e.effort = s.effort + 40 - ce
WHEN MATCHED AND cp > 40 THEN
DELETE
WHEN NOT MATCHED BY TARGET THEN
INSERT  (id, effort, name)
VALUES  (id, effort, name);

SELECT  *
FROM    @efforts
ORDER BY
        name, id

在 SQL Server 2005 ,您将需要两个语句(在一个事务中):

DECLARE @efforts TABLE (id INT NOT NULL PRIMARY KEY, effort INT NOT NULL, name CHAR(1))

INSERT
INTO    @efforts
VALUES  (1, 1, 'A')

INSERT
INTO    @efforts
VALUES  (2, 1, 'A')
INSERT
INTO    @efforts
VALUES  (3, 8, 'A')
INSERT
INTO    @efforts
VALUES  (4, 10, 'B')
INSERT
INTO    @efforts
VALUES  (5, 4, 'B')
INSERT
INTO    @efforts
VALUES  (6, 1, 'B')
INSERT
INTO    @efforts
VALUES  (7, 10, 'C')
INSERT
INTO    @efforts
VALUES  (8, 3, 'C')
INSERT
INTO    @efforts
VALUES  (9, 30, 'C')
INSERT
INTO    @efforts
VALUES  (10, 60, 'C')

;WITH    total AS
        (
        SELECT  *,
                COALESCE(
                (
                SELECT  SUM(effort)
                FROM    @efforts ep
                WHERE   ep.name = e.name
                        AND ep.id <= e.id
                ), 0) AS cp
        FROM    @efforts e
        )
DELETE
FROM    total
WHERE   cp > 40

INSERT
INTO    @efforts
SELECT  (
        SELECT  MAX(id)
        FROM    @efforts
        ) +
        ROW_NUMBER() OVER (ORDER BY name),
        40 - SUM(effort),
        name
FROM    @efforts
GROUP BY
        name
HAVING  SUM(effort) < 40

SELECT  *
FROM    @efforts
ORDER BY
        name, id

richardtallent 15 年前

我认为第一部分可以这样做:

 INSERT INTO tbl(Effort, Name)
 SELECT 40 - SUM(Effort), Name
 FROM tbl
 GROUP BY Name
 HAVING SUM(Effort) < 40)

第二部分比较难。也许你可以改成这样做?

 INSERT INTO tbl(Effort, Name)
 SELECT 40 - SUM(Effort), Name
 FROM tbl
 GROUP BY Name
 HAVING SUM(Effort) <> 40)

这样做的目的是,而不是更改实际数据,而是添加一行 消极的 如果总工作时间超过40小时,则为名称编号,或者 积极的 值,如果是40小时。这对于您的数据完整性来说似乎比处理原始值要安全得多。

cjk 15 年前

这将为您提供需要修改的名称:

SELECT Name, SUM(Effort)
FROM Table
GROUP BY Name
HAVING SUM(Effort) < 40

在临时表中选择它,为40-sum添加一列,然后从中创建一个insert语句。比光标好得多。

codingbadger 15 年前

This will do the first part:

Insert Into dbo.Test (Name, Effort)
Select t.Name, 40 - SUM(t.Effort)
From dbo.Test t
Group By t.Name
Having SUM(t.Effort) < 40

第二部分是:

Update a
Set a.Effort = a.Effort - b.AmountToDeduct
From dbo.Test a
Join (
         Select t.Name, (40 - SUM(t.Effort)) as 'AmountToDeduct'
         From dbo.Test t
         Group By t.Name
         Having SUM(t.Effort) > 40
     )b on a.Name = b.Name
Where a.ID = (Select MAX(c.ID) 
              From dbo.Test c   
          Where c.Name = a.Name
        )