如何使用DESC列来更新SQL中的表？ [英] How to update the tables in SQL with order the columns DESC?

问题描述

亲爱的朋友们。

我有一个gridview。它绑定到sql数据库。 Sql有一个包含一些列的表（名称，照片，分数，等级，id）。我的gridveiw显示了一些保存在sql中的图片。问题是我无法显示排名。例如，我有两张图片，图片A的得分为6，图片B的得分为8.图片A的等级为2，图片B的等级为1.Gridview显示图片和等级（通过使用数据集合器和按desc排序用于基于分数对图片进行分类。但是，当sql中的表被更新并且例如图片A的分数变为9时，其等级必须为1并且图片B的等级必须为2.但是通过使用按分数排序而言。排名列不会更新。因此，gridview无法显示每张图片的真实等级。如何使用表格更新按表格排序？

祝福

Dear My friends.
I have a gridview. It is binded to a sql data bank. Sql has a table with some columns (name, photo, score, rank, id). My gridveiw shows some pictures which is saved in sql. The problem is that I could not show the rank. for example, I have two pictures, picture A has a score of 6 and picture B has a score of 8. the rank of picture A is 2 and the rank of picture B is 1. Gridview shows the pictures and the rank (by using of datareader and " order by desc" for sorting the pictures based on score). But, when the table in sql is updated and for example the score of picture A changed to 9, its rank must be 1 and the rank of picture B must be 2. But by using of "order by score desc" the column of rank does not update. Therefore the gridview could not show the true rank of each picture. How to use update of table with sorting the table order by score?
Best Wishes

推荐答案

使用实际的表格设计，当你改变时记录的得分，你必须通过所有表重新计算排名。



您可以考虑删除您的Rank列，并计算查询中的排名代替。类似于：

With your actual table design, when you change the score of a record, you have to recompute the ranks through all the table.

You may consider getting rid of your Rank column, and compute the rank in the query instead. Something like:

SELECT
   t.Id
 , t.Score
 , ROW_NUMBER() OVER (ORDER BY t.Score DESC) AS Rank
 , t.Name
 , t.Photo
FROM TableName AS t

希望这会有所帮助。



更正了OVER子句。 [/ edit]

Hope this helps.

[edit] Corrected OVER clause. [/edit]

你没有在SQL中订购表 - 当你从数据库中提取它们时，你可以在它们填充表和视图的SELECT查询中对它们进行排序。如果你没有指定一个订单，那么SQL可以自由地按照它认为方便的任何顺序服务器行 - 如果你连续两次发出相同的查询，它可能不一样。



因此，请查看绑定GridView时使用的SELECT查询，并确保其形式为：

You don't "order tables" in SQL - you order them when you pull them from the DB as part of the SELECT query that you populate your tables and views with. If you do not specify an order then, then SQL is at liberty to server rows in any order it finds convenient - and that may not be the same if you issue the same query twice in a row.

So look for the SELECT query that you use when you bind the GridView, and make sure it is of the form:

SELECT * FROM MyTable ORDER BY MyRankingColumn DESC

这篇关于如何使用DESC列来更新SQL中的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！