如何在SQL查询中获得最多租用的汽车 [英] How to get most rented out cars in SQL query

问题描述

假设我有一个带有CarID列的表，并且当他们来租用时，每个id都会租给一个客户。我希望能够找到的是通过SQL查询得出的5辆租车最多的汽车是什么样的，客户最喜欢的汽车是什么，租金最多，哪些汽车是出租最少的5辆汽车。



所以如何用sql查询呢



请回复ASSP

谢谢。



我尝试了什么：



创建程序dbo .SPCarRental_GetRentedCarsTop5

AS

SELECT *

来自[CarRental]

WHERE

（

CarID IN

（

SELECT TOP（5）CarID

来自[CarRental]作为table1

GROUP BY CarID

订购CarID DESC

）

）

Suppose I have a table with CarID column and each id is rented to a a customer when they come to rent it. What I want to be able to find out is through SQL query that what are the 5 most rented out cars like which cars does the customer like and is rents the most and which cars are rented out the 5 least rented out cars.

so how do I do it with an sql query

Please reply ASSP
Thank you.

What I have tried:

CREATE PROCEDURE dbo.SPCarRental_GetRentedCarsTop5
AS
SELECT *
FROM [CarRental]
WHERE
(
CarID IN
(
SELECT TOP (5) CarID
FROM [CarRental] as table1
GROUP BY CarID
ORDER BY CarID DESC
)
)

推荐答案

分解句子并写下子查询，直到得到你想要的东西。



5辆租来的车 - >按ID计算汽车并按降序排序



客户喜欢哪些汽车 - >按照客户ID的id降序计算汽车

Break down the sentences and write sub queries until you get what you want.

"5 most rented cars" -> count the cars by id and sort descending

"which cars the customer likes" -> count the cars by id sort descending having the customer's id

没有你的db模式，很难回答这个问题;因为我们只能假设你有什么数据。



您在查询中引用了表 CarRental 。我猜这些是对特定汽车的不同保留，由 CarID 列标识。如果这是我的（标准化）数据库，我还会有一张 Car 表，其中CarID为PK，由租赁表引用。



如果所有这些都是真的;您提供的查询将为您提供有关预订的详细信息，而不是关于汽车本身的详细信息。即使这是你想要的，你也不会得到最受欢迎的5，因为你的聚合只在CarID上（基本上什么都不做）并按CarID排序。那么一个合理的假设是子查询总是以降序响应最后5个CarID

你想要做的是保持基于CarID的聚合;你想对那些CarID的Count进行排序，降序

Without your db schema it is kind of hard to answer this; as we can only assume what data you have.

You have referenced the table CarRental in your query. I am guessing that these are distinct reservations for a particular car, identified by the column CarID. If this were my (normalized) DB, I would also have a table for Car with a PK of CarID, as referenced by the rental table.

If all of this is true; the query you have presented would provide you details about the reservations and not so much about the cars themselves. Even if that is what you wanted you are not going to get the 5 most popular as your aggregation is only on the CarID (essentially doing nothing) and sorting by CarID. It is a reasonable assumption then that the subquery is going to always respond with the last 5 CarIDs in descending order
What you would want to do is to keep the aggregate based on CarID, however; you want to do the order on the Count of those CarIDs, descending

SELECT   *
FROM     CarRental
WHERE    CarID IN (
   SELECT   TOP (5) CarID
   FROM     CarRental
   GROUP BY CarID
   ORDER BY Count(CarID) DESC
)

如果我在我的数据库模式上是正确的，那么你有一个Car表;而你想要Car详细信息 - 查询是一个简单的编辑，让主查询引用该表并保持子查询不变：

If I was right on my db schema, that you have a Car table; and you wanted the Car details instead- the query is an easy edit to have the main query reference that table and keeping the subquery as is:

SELECT   *
FROM     Car
WHERE    CarID IN (
   SELECT   TOP (5) CarID
   FROM     CarRental
   GROUP BY CarID
   ORDER BY Count(CarID) DESC
)

这篇关于如何在SQL查询中获得最多租用的汽车的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！