无法在我的报表中获取 TOP 和 ORDER BY 工作

Question

我必须根据评论数量显示排名前n的电影，并根据评论按降序列出，到目前为止我已经能够做到这一点：

string mySql = "SELECT Movies.MovieName, COUNT(Reviews.MovieID) AS 
NumberOfReviews FROM Reviews INNER JOIN Movies ON Movies.MovieID = 
Reviews.MovieID GROUP BY MovieName";
///Sets up the connection to the server 
OleDbConnection db = new OleDbConnection();
db.ConnectionString = FormMainMenu._DBConnectionInfo;
//open database
db.Open();
OleDbCommand cmd = new OleDbCommand(mySql, db);
//Creates a data set out of the data
OleDbDataAdapter adapter = new OleDbDataAdapter(mySql, db);
DataSet ds = new DataSet();
//Fills the table
adapter.Fill(ds);
DataTable dt = ds.Tables["TABLE"];      //Creates a datatable of all the movies in the database
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dt;
dataGridView1.DataSource = bindingSource;
adapter.Update(ds);
//close the database
db.Close();

我不能做的是把TOP放在哪里，只在DataTable中列出5或10等，并让ORDER BY工作

使用访问数据库

Answer 1

您可以尝试以下操作-它将在sql server 中工作

SELECT top 5 Movies.MovieName, COUNT(Reviews.MovieID) AS 
NumberOfReviews FROM Reviews INNER JOIN Movies ON Movies.MovieID = 
Reviews.MovieID GROUP BY MovieName
order by COUNT(Reviews.MovieID) desc

对于oracle，您可以尝试低于

select * from 
(SELECT Movies.MovieName, COUNT(Reviews.MovieID) AS 
NumberOfReviews FROM Reviews INNER JOIN Movies ON Movies.MovieID = 
Reviews.MovieID GROUP BY MovieName
order by COUNT(Reviews.MovieID) desc
) where rownum<=10

Answer 2

Top不能用于Oracle，则可以使用row_number。

SELECT MovieName, NumberOfReviews
FROM
(
SELECT MovieName, NumberOfReviews, 
row_number() over (ORDER BY NumberOfReviews DESC) as rn
FROM
(
SELECT m.MovieName, COUNT(r.MovieID) AS NumberOfReviews
FROM Reviews r
INNER JOIN Movies m
ON m.MovieID = r.MovieID
GROUP BY m.MovieName
) 
)  
WHERE rn <= 5;

对于DB版本12，fetch子句可以用作：

SELECT m.MovieName, COUNT(r.MovieID) AS NumberOfReviews
FROM Reviews r
INNER JOIN Movies m
ON m.MovieID = r.MovieID
GROUP BY m.MovieName
FETCH FIRST 5 ROWS ONLY;