首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL Server查询创建列表不只是顶部

问SQL Server查询创建列表不只是顶部
EN

Stack Overflow用户
提问于 2014-04-27 17:16:58
回答 1查看 74关注 0票数 2

我有一个查询,我只能得到最高记录,但现在我必须改变它,以便它将产生一个游戏列表,以发送。

目前,它是基于User_ID = 1的,但是我需要为所有的user_ID运行。

有人知道我怎么能改变这个查询吗?

这个查询应该产生一个用户的列表,谁是到期的游戏,以及他们的游戏到期。

  1. 根据所选择的订阅包和租用表,列出未达到该月份游戏配额的用户。
  2. 将这个用户列表与他们最喜欢的列表进行比较,并根据哪个游戏是他们的最高优先级和日期给他们一个游戏。
  3. 所选择的游戏必须是库存的,需要与游戏表中的数量级别相比较。

http://sqlfiddle.com/#!3/48bc1/1

​

此sql Fiddle的预期结果是:-

代码语言:javascript
复制
Leona Hill Blur XBOX 360
Rodney Hill Blur XBOX 360
Gillian Hill Formal 1 PS3
Gillian Hill Need for Speed XBOX 360
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-04-27 18:58:57

这是基于SQLFiddle中的数据-我注意到那里没有'LASTNAME‘列。

此外,我还在消化,如果这是正确的或不正确,但请看一下。

代码语言:javascript
复制
/* 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_PLATFORM
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/23326466

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档