数据库SQL - 查询以查找前5个值和每个值生成的总数 [英] Database SQL - Query to find top 5 values and total generated by each value

问题描述

我需要一个SQL查询来列出前5个最常租用的DVD，按降序排序

顺序。对于我需要显示的每个记录：DVD标题，租用频率和每个DVD标题产生的总租金收入



我的桌子：

DVD



我的属性：

DVD-Title，

DVD销售（该记录中单个DVD标题的销售数量），

DVD-Total（该记录中单个DVD标题的销售总额（£）



我自己有这个：



选择DVD标题，DVD销售

来自DVD

WHERE

（

DVD-sales IN

（

SELECT TOP（5 ）DVD销售

来自DVD表格

GROUP BY DVD销售

订购DVD销售DESC

）

）



（表作为一个关键词？还是应该把我的表名放在那里？）



不确定上述代码是否正确，我不知道怎么办总租金每个DVD标题产生的收入





请在你的答案中包含一个SQL代码（SQL服务器代码很好）解决方案，谢谢！

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.

My table:
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

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 don't know how to do the total rent income generated for each DVD title


Please include a SQL code (SQL server code is fine) solution in your answers, thank you!

推荐答案

除非我遗漏了什么，否则您应该可以直接进行前5名选择并按所需列进行排序。

Unless I am missing something, you should be able to do a straight forward top 5 select and order by the required column.

--test data setup
declare @DVD table (id int identity(1,1),[DVD-title] varchar(100),[DVD-sales] int, [DVD-total] decimal(5,2));
insert into @DVD
select 'titleI', 9, 27
union all select 'titleJ', 6, 18
union all select 'titleG', 3, 9
union all select 'titleB', 2, 6
union all select 'titleE', 2, 6
union all select 'titleC', 10, 30
union all select 'titleD', 4, 12
union all select 'titleA', 5, 15
union all select 'titleF', 9, 27
union all select 'titleH', 12, 36

select top (5)
	[DVD-title],
	[DVD-sales],
	[DVD-total]
from @DVD
order by [DVD-sales] desc
;

这篇关于数据库SQL - 查询以查找前5个值和每个值生成的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！