Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH UserInGamesAggregatedItems_CTE (Username,
- [Game Name],
- [Item Stenght Sum],
- [Item Defence Sum],
- [Item Speed Sum],
- [Item Mind Sum],
- [Item Luck Sum])
- AS
- (
- SELECT u.Username,
- g.Name AS [Game Name],
- SUM(s.Strength) AS [Item Stenght Sum],
- SUM(s.Defence) AS [Item Defence Sum],
- SUM(s.Speed) AS [Item Speed Sum],
- SUM(s.Mind) AS [Item Mind Sum],
- SUM(s.Luck) AS [Item Luck Sum]
- FROM Users AS u
- INNER JOIN UsersGames AS ug
- on u.Id = ug.UserId
- INNER JOIN Games AS g
- ON ug.GameId = g.Id
- INNER JOIN UserGameItems AS ugi
- ON ug.Id = ugi.UserGameId
- INNER JOIN Items AS i
- ON ugi.ItemId = i.Id
- INNER JOIN [Statistics] AS s
- ON i.StatisticId = s.Id
- GROUP BY u.Username,
- g.Name
- ),
- UserInGamesAggregatedCharacters_CTE (Username,
- [Game Name],
- [Character Name],
- [Character Stenght Max],
- [Character Defence Max],
- [Character Speed Max],
- [Character Mind Max],
- [Character Luck Max])
- AS
- (
- SELECT u.Username,
- g.Name AS [Game Name],
- MAX(c.Name) AS [Character Name],
- MAX(s.Strength) AS [Character Stenght Max],
- MAX(s.Defence) AS [Character Defence Max],
- MAX(s.Speed) AS [Character Speed Max],
- MAX(s.Mind) AS [Character Mind Max],
- MAX(s.Luck) AS [Character Luck Max]
- FROM Users AS u
- INNER JOIN UsersGames AS ug
- ON u.Id = ug.UserId
- INNER JOIN Games AS g
- ON ug.GameId = g.Id
- INNER JOIN Characters AS c
- ON ug.CharacterId = c.Id
- INNER JOIN [Statistics] AS s
- ON c.StatisticId = s.Id
- GROUP BY u.Username,
- g.Name
- ),
- UserInGamesAggregatedGames_CTE (Username,
- [Game Name],
- [Game Stenght Max],
- [Game Defence Max],
- [Game Speed Max],
- [Game Mind Max],
- [Game Luck Max])
- AS
- (
- SELECT u.Username,
- g.Name AS [Game Name],
- MAX(s.Strength) AS [Game Stenght Max],
- MAX(s.Defence) AS [Game Defence Max],
- MAX(s.Speed) AS [Game Speed Max],
- MAX(s.Mind) AS [Game Mind Max],
- MAX(s.Luck) AS [Game Luck Max]
- FROM Users AS u
- INNER JOIN UsersGames AS ug
- ON u.Id = ug.UserId
- INNER JOIN Games AS g
- ON ug.GameId = g.Id
- INNER JOIN GameTypes AS gt
- ON g.GameTypeId = gt.Id
- INNER JOIN [Statistics] AS s
- ON gt.BonusStatsId = s.Id
- GROUP BY u.Username,
- g.Name
- ),
- AggregatedUserInGamesTotalStats_CTE (Username,
- GameName,
- CharacterName,
- TotalStrenght,
- TotalDefence,
- TotalSpeed,
- TotalMind,
- TotalLuck)
- AS
- (
- SELECT u.Username,
- g.Name,
- c_cte.[Character Name],
- i_cte.[Item Stenght Sum] + c_cte.[Character Stenght Max] + g_cte.[Game Stenght Max] AS [Total Strenght],
- i_cte.[Item Defence Sum] + c_cte.[Character Defence Max] + g_cte.[Game Defence Max] AS [Total Defence],
- i_cte.[Item Speed Sum] + c_cte.[Character Speed Max] + g_cte.[Game Speed Max] AS [Total Speed],
- i_cte.[Item Mind Sum] + c_cte.[Character Mind Max] + g_cte.[Game Mind Max] AS [Total Mind],
- i_cte.[Item Luck Sum] + c_cte.[Character Luck Max] + g_cte.[Game Luck Max] AS [Total Luck]
- FROM Users AS u
- INNER JOIN UsersGames AS ug
- ON u.Id = ug.UserId
- INNER JOIN Games AS g
- ON ug.GameId = g.Id
- INNER JOIN UserInGamesAggregatedItems_CTE AS i_cte
- ON i_cte.Username = u.Username AND
- i_cte.[Game Name] = g.Name
- INNER JOIN UserInGamesAggregatedCharacters_CTE AS c_cte
- ON c_cte.Username = u.Username AND
- c_cte.[Game Name] = g.Name
- INNER JOIN UserInGamesAggregatedGames_CTE AS g_cte
- ON g_cte.Username = u.Username AND
- g_cte.[Game Name] = g.Name
- -- GROUP BY u.Username, g.Name
- --HAVING u.Username = 'skippingside'
- --ORDER BY u.Username ASC,
- -- g.Name ASC
- )
- SELECT DISTINCT *
- FROM AggregatedUserInGamesTotalStats_CTE AS a
- ORDER BY a.TotalStrenght DESC,
- a.TotalDefence DESC,
- a.TotalSpeed DESC,
- a.TotalMind DESC,
- a.TotalLuck DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement