Tsql - 使用 max 和 group by 获取整行信息

发布于 2024-09-13 22:53:01 字数 349 浏览 3 评论 0原文

我有一个表，其中包含以下列：

PK1   PK2   ID   DATE    Value  flag

我进行的计算涉及取每个 ID 的最大值。

  select id,
         max(value) 
    from table 
group by id

我想在我正在使用的行上标记标志。如果 id 和 max(value) 对应于多行，则标记具有最大日期的行。如果它们具有相同的 id，则 max(value) 和 max(date) 恰好标记这些行之一（不关心此时是哪一行）

有什么想法吗？

谢谢！

原文

I have a table, with the following columns:

PK1   PK2   ID   DATE    Value  flag

I do a calculation that involves taking the max value per ID.

  select id,
         max(value) 
    from table 
group by id

I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the max date. If they have the same id,max(value) and max(date) flag exactly one of those rows (don't care which at that point)

Any ideas?

Thanks!

分享到QQ

分享到微博

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

发布评论

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

染火枫林 2024-09-20 22:53:01

对于 SQL2005+ 可能是这样的。（假设“Mark”表示更新flag列）

WITH cte AS
(
SELECT PK1, PK2, ID, DATE, Value, flag,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY value desc, date desc) AS RN
FROM table 
)
UPDATE cte
SET flag=CASE WHEN RN=1 THEN 1 ELSE 0 END

For SQL2005+ maybe something like this. (Assuming that "Mark" means update the flag column)

WITH cte AS
(
SELECT PK1, PK2, ID, DATE, Value, flag,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY value desc, date desc) AS RN
FROM table 
)
UPDATE cte
SET flag=CASE WHEN RN=1 THEN 1 ELSE 0 END

回复收藏 0 原文

给妤﹃绝世温柔 2024-09-20 22:53:01

使用 SQL Server 2005 或更高版本，您可以执行以下操作：

SELECT x.*
FROM (
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN
 FROM table
) x
WHERE x.RN = 1

With SQL Server 2005 or above, you can do the following:

SELECT x.*
FROM (
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN
 FROM table
) x
WHERE x.RN = 1

回复收藏 0 原文

怪我鬧 2024-09-20 22:53:01

为什么这不起作用？

update table
set flag = '1'
where id in (select id from (SELECT PK1, PK2, id, date,value, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN 
FROM table) t where RN = 1)

编辑：如果您不想（或在某些sql版本中不能）使用cte，则以下语句将起作用

以上将不起作用，因为正如下面马丁所说，id仍然是在列表中。

然而，如果有人不喜欢使用 cte，下面的方法将会起作用。（虽然不像 Martin 的解决方案那么优雅）

update table
set flag = '1'
where convert(varchar,PK1)+convert(varchar,PK2) in (select convert(varchar,PK1)+convert(varchar,PK2) from (SELECT PK1, PK2, id, date,value, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN 
FROM table) t where RN = 1)

Why does this NOT work?

update table
set flag = '1'
where id in (select id from (SELECT PK1, PK2, id, date,value, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN 
FROM table) t where RN = 1)

EDIT: the below statement WILL work if you don't want to (or can't in some sql versions) use a cte

The above will not work because, as martin says below, the id is still in the list.

however, the below will work if someone prefereds not to use a cte.(Not nearly as elegant as Martin's solution though)

update table
set flag = '1'
where convert(varchar,PK1)+convert(varchar,PK2) in (select convert(varchar,PK1)+convert(varchar,PK2) from (SELECT PK1, PK2, id, date,value, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS RN 
FROM table) t where RN = 1)

回复收藏 0 原文

~没有更多了~