Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH help AS (SELECT ug.UserId,
- ug.GameId,
- SUM(s.Strength) AS Strength,
- SUM(s.Defence) AS Defence,
- SUM(s.Speed) AS Speed,
- SUM(s.Mind) AS Mind,
- SUM(s.Luck) AS Luck
- FROM UsersGames AS ug
- INNER JOIN UserGameItems AS ugi
- ON ugi.UserGameId = ug.Id
- INNER JOIN Items AS i
- ON ugi.ItemId = i.Id
- INNER JOIN [Statistics] AS s
- ON s.Id = i.StatisticId
- GROUP BY ug.UserId, ug.GameId)
- SELECT DISTINCT u.Username,
- g.[Name] AS 'Game',
- c.[Name] AS 'Character',
- s1.Strength + s2.Strength + h.Strength AS 'Strength',
- s1.Defence + s2.Defence + h.Defence AS 'Defence',
- s1.Speed + s2.Speed + h.Speed AS 'Speed',
- s1.Mind + s2.Mind + h.Mind AS 'Mind',
- s1.Luck + s2.Luck + h.Luck AS 'Luck'
- FROM UsersGames AS ug
- INNER JOIN Users AS u
- ON u.Id = ug.UserId
- INNER JOIN Games AS g
- ON g.Id = ug.GameId
- INNER JOIN UserGameItems AS ugi
- ON ugi.UserGameId = ug.Id
- INNER JOIN Items AS i
- ON ugi.ItemId = i.Id
- INNER JOIN Characters AS c
- ON c.Id = ug.CharacterId
- INNER JOIN GameTypes AS gt
- ON gt.Id = g.GameTypeId
- INNER JOIN [Statistics] AS s1
- ON s1.Id = c.StatisticId
- INNER JOIN [Statistics] AS s2
- ON s2.Id = gt.BonusStatsId
- INNER JOIN help AS h
- ON h.UserId = u.Id
- AND h.GameId = g.Id
- ORDER BY Strength DESC, Defence DESC, Speed DESC, Mind DESC, Luck DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement