更新排名列时遇到问题

发布于 2025-01-11 10:05:57 字数 858 浏览 3 评论 0原文

我对MySQL的了解是通过PHP更新选择删除安装。我用谷歌搜索了这个并转到 Stack Overflow 搜索词是 MySQL 排名更新。我发现的那些可以帮助我的。告诉他们如何解决问题的人。告诉他们通过选择而不是更新来做到这一点，甚至向他们展示如何做到这一点。

我知道如何通过选择谷歌教我来做到这一点。

我需要通过更新来做到这一点。

表和查询如下。

表

region_id	pstrank	101
101	20	1
101	19	2
101	19	2
。	15	4
100	20	1
100	13	2

查询

UPDATE `test1`
SET `rank` =
RANK() OVER(PARTITION BY `region_id` ORDER BY `pst` DESC)

当我运行此代码时，出现此错误窗口函数仅允许在 SELECT 列表和 ORDER BY 子句中使用。

您能告诉我如何解决这个问题吗？

感谢您的提前帮助

原文

My knowledge in MySQL is updating select deleting installing through PHP. I googled this and went to Stack overflow search terms were MySQL rank update. The ones I found that could help me. The persons that told them how to fix it. Told them do it through a select not an update, and even show them how to do it.

I know how to do it through a select Google taught me that.

I need to do it through an update.

Table and query are down below.

Table

region_id	pst	rank
101	20	1
101	19	2
101	19	2
101	15	4
100	20	1
100	13	2

Query

UPDATE `test1`
SET `rank` =
RANK() OVER(PARTITION BY `region_id` ORDER BY `pst` DESC)

When I run this code, I get this error.
Window function is allowed only in SELECT list and ORDER BY clause.

Can you please show me how to fix this issue?

Thank you for your help in advanced

分享到QQ

分享到微博

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

发布评论

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

吃颗糖壮壮胆 2025-01-18 10:05:57

您需要将表连接到返回排名的查询：

UPDATE test1 t1
INNER JOIN (
  SELECT region_id, pst, 
         RANK() OVER (PARTITION BY region_id ORDER BY pst DESC) `rank` 
  FROM test1
) t2 ON t2.region_id = t1.region_id AND t2.pst = t1.pst
SET t1.`rank` = t2.`rank`;

请参阅演示< /a>.

You need a join of the table to a query that returns the ranks:

UPDATE test1 t1
INNER JOIN (
  SELECT region_id, pst, 
         RANK() OVER (PARTITION BY region_id ORDER BY pst DESC) `rank` 
  FROM test1
) t2 ON t2.region_id = t1.region_id AND t2.pst = t1.pst
SET t1.`rank` = t2.`rank`;

See the demo.

回复收藏 0 原文

~没有更多了~