我有一个查询,我只能得到最高记录,但现在我必须改变它,以便它将产生一个游戏列表,以发送。
目前,它是基于User_ID = 1的,但是我需要为所有的user_ID运行。
有人知道我怎么能改变这个查询吗?
这个查询应该产生一个用户的列表,谁是到期的游戏,以及他们的游戏到期。
http://sqlfiddle.com/#!3/48bc1/1

此sql Fiddle的预期结果是:-
Leona Hill Blur XBOX 360
Rodney Hill Blur XBOX 360
Gillian Hill Formal 1 PS3
Gillian Hill Need for Speed XBOX 360发布于 2014-04-27 18:58:57
这是基于SQLFiddle中的数据-我注意到那里没有'LASTNAME‘列。
此外,我还在消化,如果这是正确的或不正确,但请看一下。
/* Find the next games for a user to rent */
/* You cant do it in one swoop because each customers choice will affect the next one */
/* and you have to insert the result into the rentals in between. */
SELECT FIRSTNAME, GAME_NAME, GAME_PLATFORM
FROM [Games]
JOIN (SELECT [Favourites_ID], [User_ID], [Game_name], [Game_platform],
[Priority], [Priority_level], [DatePicked],
RANK() OVER (PARTITION BY [User_ID] ORDER BY [Priority], [DatePicked]) AS Rank
FROM Favourites) [Favourites]
ON [Favourites].[Game_name] = [Games].[Name]
JOIN [Users]
ON [Users].[User_ID] = [Favourites].[User_ID]
JOIN (SELECT [User_ID], MIN([RemainingRentable]) RemainingRentable FROM (
/* Remaining rentable at a time */
SELECT
[Subscriptions].[Game_at_a_time] - (
/* Number of games currently rented by the customer */
SELECT COUNT(*) AS [CurraentlyRented]
FROM [Rentals]
WHERE [Rentals].[Date_returned] IS NULL
AND [Users].[User_ID] = [Rentals].[User_ID]
) AS RemainingRentable,
[Users].[User_ID]
FROM [Users]
JOIN [Subscriptions]
ON [Users].[Subscription_ID] = [Subscriptions].[Subscription_ID]
UNION
/* Remaining rentable this month */
SELECT
[Subscriptions].[Max_games] - (
/* Number of total games rented by the customer this month */
SELECT COUNT(*) AS [RentedThisMonth]
FROM [Rentals]
WHERE MONTH([Rentals].[Date_rented]) = MONTH(GETDATE())
AND YEAR([Rentals].[Date_rented]) = MONTH(GETDATE())
AND [Users].[User_ID] = [Rentals].[User_ID]
) AS RemainingRentable,
[Users].[User_ID]
FROM [Users]
JOIN [Subscriptions]
ON [Users].[Subscription_ID] = [Subscriptions].[Subscription_ID] ) A
GROUP BY [User_ID]
) Rentability
ON [Rentability].[User_ID] = [Users].[User_ID]
WHERE
[Games].[Quantity] > (
/* Number of currently rented copies */
SELECT COUNT(*) FROM [Rentals]
WHERE [Games].[Game_barcode] = [Rentals].[Game_barcode]
AND [Rentals].[Date_returned] IS NULL
)
AND Rank <= RemainingRentable
GROUP BY FIRSTNAME, GAME_NAME, GAME_PLATFORMhttps://stackoverflow.com/questions/23326466
复制相似问题