在 SQL 中写回 GROUP 编号

发布于 2024-11-10 12:12:17 字数 635 浏览 4 评论 0原文

我有一个无法修改的现有应用程序。它需要执行SQL GROUP BY，但不能。但是，它可以并且确实从同一个表中读取 GroupNumber 字段。

我现在要做的是执行分组 SQL 语句，在代码中处理它并将 GroupNumber 写回表中，以便应用程序可以执行其操作。我想做的是执行一条 SQL 语句，一步完成分组和写回。我不知道如何做到这一点，如果确实可能的话。简单的示例：

  SELECT FirstName, LastName, Age 
    FROM Persons 
   WHERE .... 
GROUP BY Age 
ORDER BY Age

我执行此操作，那么

for ( i = 1; i <= result_set.n; i++ )
  Sql = “UPDATE Persons 
            SET GroupNumber  = “ + fixed( i ) 
       + “WHERE Age = “ + fixed( result_set.Age[i] )

每次将记录添加到表中时是否都需要执行此操作（所以是的，如果添加了比我年轻的人，我的组号会更改 - 不要问）。

原文

I have an existing app I can’t modify. It needs to execute a SQL GROUP BY, but cannot. However it can and does read a GroupNumber field from the same table.

What I’m doing now is executing the grouping SQL statement, processing it in code and writing back the GroupNumber to the table so that App can do its thing. What I’d like to do is execute a single SQL statement to do both the grouping and the writeback in a single step. I can’t figure out how to do this, if indeed it’s possible. Simple example:

  SELECT FirstName, LastName, Age 
    FROM Persons 
   WHERE .... 
GROUP BY Age 
ORDER BY Age

I execute this, then do

for ( i = 1; i <= result_set.n; i++ )
  Sql = “UPDATE Persons 
            SET GroupNumber  = “ + fixed( i ) 
       + “WHERE Age = “ + fixed( result_set.Age[i] )

I need to do this every time a record gets added to the table (so yes, if someone younger than me gets added, my group number changes - don’t ask).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光倒影 2024-11-17 12:12:17

显然你想要一个触发器。但是，触发器定义因数据库服务器而异。我大胆猜测一下，您正在使用某个版本的 Microsoft SQL Server：创建触发器语法和几个示例可以在 http://msdn.microsoft.com/en-us/library/ms189799.aspx。触发器修改从中获取数据的同一个表可能会出现一些小问题，但我相信您通常可以在大多数 SQL Server 数据库中做到这一点（SQLite 可能是少数几个难以做到这一点的数据库之一）。

尝试一下，看看是否有帮助。

回复收藏 0 原文

空城之時有危險 2024-11-17 12:12:17

我不太确定你想要什么，这是我最好的猜测：

;WITH AllRows AS (--get one row per age, and number them
SELECT
    Age, ROW_NUMBER() OVER (PARTITION BY AGE ORDER BY Age) AS RowNumber
    FROM Persons
    WHERE ...
    GROUP BY Age
)
UPDATE p --update all the people, getting their GroupNumber based on their Age's row number
    SET GroupNumber=a.RowNumber
    FROM Persons           p
        INNER JOIN AllRows a ON p.Age=a.Age
    WHERE GroupNumber IS NULL OR GroupNumber!=a.RowNumber

我使用 SQL Server，但这是相当基于标准的代码。

I'm not really sure what you are after, here is my best guess:

;WITH AllRows AS (--get one row per age, and number them
SELECT
    Age, ROW_NUMBER() OVER (PARTITION BY AGE ORDER BY Age) AS RowNumber
    FROM Persons
    WHERE ...
    GROUP BY Age
)
UPDATE p --update all the people, getting their GroupNumber based on their Age's row number
    SET GroupNumber=a.RowNumber
    FROM Persons           p
        INNER JOIN AllRows a ON p.Age=a.Age
    WHERE GroupNumber IS NULL OR GroupNumber!=a.RowNumber

I use SQL Server, but this is fairly standards based code.

回复收藏 0 原文

~没有更多了~