数据库SQL - 查询以查找每个值生成的前5个值和总计

I need an SQL query to list the top 5 most frequently rented DVD's, sorted in descending order. For each record i need to show: DVD title, the rented frequency and the total rent income generated by each DVD title.

我需要一个SQL查询来列出前5个最常租用的DVD,按降序排序。对于我需要显示的每个记录:DVD标题,租用频率和每个DVD标题产生的总租金收入。

My table: DVD

我的桌子:DVD

My attributes: DVD-Title, DVD-sales (Number of sales of the single DVD title in that record), DVD-Total (Total of sales (£) of the single DVD title in that record

我的属性:DVD-Title,DVD-sales(该记录中单个DVD标题的销售数量),DVD-Total(该记录中单个DVD标题的销售总额(£))

I have got this myself:

我自己有这个:

SELECT DVD-title, DVD-sales
FROM DVD
WHERE 
(
DVD-sales IN 
(
SELECT TOP (5) DVD-sales
FROM table as DVD       
GROUP BY DVD-sales     
ORDER BY DVD-sales DESC
)
)

(is "table as" a key word? or should i put my table name there?)

(“表是”关键词?还是应该把我的表名放在那里?)

Not sure if the above code is right or not, and i dont know how to do the total rent income generated for each DVD title

不确定上面的代码是否正确,我不知道如何为每个DVD标题生成总租金收入

Please include a SQL code solution in you answers, thank you!

请在你的答案中包含一个SQL代码解决方案,谢谢!

1 个解决方案

#1

If the table has a single row per title, it would be as simple as:

如果表格每个标题有一行,那么就像下面这样简单:

SELECT TOP 5 "DVD-Title", "DVD-Sales", "DVD-Total"
FROM DVD
ORDER BY "DVD-Sales" DESC

However, assuming the same title can occur multiple times, a query such as the following should work:

但是,假设可以多次出现相同的标题,则应执行以下查询:

SELECT TOP 5 "DVD-Title", TotalQuantity, TotalValue
FROM
(
    SELECT 
        DVD-Title,
        SUM("DVD-Sales") AS TotalQuantity,
        SUM("DVD-Total") AS TotalValue
    FROM DVD
    GROUP BY "DVD-Title"
) A
ORDER BY TotalQuantity DESC

What this query is doing is first summarising your data so that it has a single row per title, and then sorting this resulting data by the total quantity in descending order, and returning the first 5 results.

此查询的作用是首先汇总数据,使其每个标题有一行,然后按降序排列总数量,并返回前5个结果。

#1