具有组内行字段差异的多个标准分组
具有组内行字段差异的多个标准分组
提问于 2020-06-14 20:54:35
各位，
我正在努力为下列任务找到解决办法：
All users (1) that have a game (2) with their items count (3) and items price (4) are to be retrieved by a query
=> Display the username, game name, items count and items price. 我已正确地进行了必要的连接。我不会用模式和特定的表以及它们相应的元组来打扰您。相反，请在下面找到上面的“原始数据集-连接”查询的第一个结果。我请求您的帮助，因为我很感兴趣的是，如何按用户名和名称对结果进行分组，只显示一个行，用于多次出现"username+name“。我似乎无法得到它，因为在重复的"username+name“事件中，项是不同的，对于MS来说，数据显然是不相同的，以便能够正确地”分组“吗？
应该通过使用视图或子查询来获得结果吗？
请协助-感谢你的帮助！
SELECT Users.[Username], Games.[Name], COUNT(Items.[Name]), Items.Price FROM UsersGames
LEFT OUTER JOIN Games
ON Games.[Id] = UsersGames.GameId
JOIN UserGameItems
ON UserGameItems.UserGameId = UsersGames.[Id]
JOIN Items
ON Items.[Id] = UserGameItems.[ItemId]
JOIN Users
ON Users.Id = UsersGames.UserId
GROUP BY Users.[Username], Games.[Name], Items.PriceUsername Name Count Item Name Item Price
-------------------------------------------------- -------------------------------------------------- ----------- -------------------------------------------------- ---------------------
admincuttle Flax New Zealand 1 Cosmic Strand 243,00
admincuttle Flax New Zealand 1 Griswolds Masterpiece 342,00
admincuttle Flax New Zealand 1 Simplicitys Strength 29,00
admincuttle Flax New Zealand 1 Staff of Herding 42,00
admincuttle Flax New Zealand 1 Wall of Bone 537,00
admincuttle Florence 1 Esoteric Alteration 675,00
admincuttle Florence 1 Eye of Peshkov 772,00
admincuttle Florence 1 Hierophants Seal 146,00
admincuttle Florence 1 Starfire 423,00
admincuttle Freesia 1 Blessed of Haull 318,00
admincuttle Freesia 1 Hellfire Ring (Dexterity) 29,00
admincuttle Freesia 1 Razor Strop 396,00
admincuttle Moon orchid 1 Blackfeather 442,00
admincuttle Moon orchid 1 Chanon Bolter 272,00
admincuttle Moon orchid 1 Cloaks 249,00
admincuttle Moon orchid 1 Demon Hand 609,00
admincuttle Moon orchid 1 Gesture of Orpheus 365,00
admincuttle Moon orchid 1 Mad Monarchs Scepter 370,00
admincuttle Moon orchid 1 Shattered Core 180,00
admincuttle Moon orchid 1 Silver Star Piercers 265,00
admincuttle Moon orchid 1 The Horadric Hamburger 762,00
admincuttle Moon orchid 1 Tiklandian Visage 665,00
admincuttle Moon orchid 1 Two-Handed Flails 583,00
admincuttle Paris 1 Adventurers Journal 720,00
admincuttle Paris 1 Blessed of Haull 318,00
admincuttle Paris 1 Blitzbolter 18,00
admincuttle Paris 1 Dawn 96,00
admincuttle Paris 1 Greater Rift Keystone 396,00
admincuttle Paris 1 Ramaladnis Gift 326,00
admincuttle Paris 1 Sunder 668,00
admincuttle Paris 1 Tyraels Might (Diablo III) 607,00
admincuttle Paris 1 Wizard Hats 121,00
advisersspry Ablajeck 1 Alabaster Gloves 65,00
advisersspry Ablajeck 1 Bombardiers Rucksack 582,00
advisersspry Ablajeck 1 Calamity 68,00
advisersspry Ablajeck 1 Deaths Bargain 354,00
advisersspry Ablajeck 1 Denial 164,00
advisersspry Ablajeck 1 Gloves 103,00
advisersspry Ablajeck 1 Rozpedins Force 690,00
advisersspry Ablajeck 1 Thing of the Deep 799,00
advisersspry Ablajeck 1 Warmonger 773,00
advisersspry Allied Tion 1 Ahavarion, Spear of Lycander 313,00
advisersspry Allied Tion 1 Bottomless Potion of the Diamond 615,00
advisersspry Allied Tion 1 Broken Crown 237,00
advisersspry Allied Tion 1 Devil Tongue 584,00
advisersspry Allied Tion 1 Maces 760,00
advisersspry Allied Tion 1 Maloths Focus 743,00
advisersspry Allied Tion 1 Two-Handed Mighty Weapons 283,00
advisersspry Allied Tion 1 Wands 16,00
advisersspry Heather 1 Flying Dragon 621,00
advisersspry Heather 1 Giant Skull (Diablo III) 2,00
advisersspry Heather 1 Nailbiter 123,00
advisersspry Heather 1 Robes of the Rydraelm 208,00
advisersspry Heather 1 Steady Strikers 581,00
advisersspry Heather 1 Telrandens Hand 303,00
advisersspry Heather 1 Warmonger 773,00
advisersspry Heather 1 Wizard Hats 121,00
advisersspry Heather 1 Wondrous Deflectors 137,00
advisersspry Rose Bridal White 1 Buriza-Do Kyanon (Diablo III) 713,00
advisersspry Rose Bridal White 1 Goldskin (Diablo III) 449,00
advisersspry Rose Bridal White 1 Haunting Girdle 618,00
advisersspry Rose Bridal White 1 Helms 219,00
advisersspry Rose Bridal White 1 The Furnace 223,00
advisersspry Rose Bridal White 1 Valtheks Rebuke 573,00
advisersspry Rose Bridal White 1 Warstaff of General Quang 490,00
advisersspry Rose Bridal White 1 Ziggurat Tooth 788,00
Alex Edinburgh 1 Akanesh, the Herald of Righteousness 586,00
Alex Edinburgh 1 Band of Hollow Whispers 405,00
Alex Edinburgh 1 Drakons Lesson 341,00
Alex Edinburgh 1 Nailbiter 123,00
Alex Edinburgh 1 Robes of the Rydraelm 208,00
Alex Edinburgh 1 Skull of Resonance 45,00
Alex Edinburgh 1 Vile Ward 516,00
Alex Edinburgh 1 Voodoo Masks 117,00
Alex Flax New Zealand 1 Akarats Awakening 80,00
Alex Flax New Zealand 1 Board Walkers 477,00
Alex Flax New Zealand 1 Boon of the Hoarder 208,00
Alex Flax New Zealand 1 Bows 364,00
Alex Flax New Zealand 1 Maras Kaleidoscope (Diablo III) 791,00
Alex Flax New Zealand 1 Ring of Royal Grandeur 554,00
Alex Flax New Zealand 1 Spite 80,00
Alex Flax New Zealand 1 The Swami 211,00
Alex Flax New Zealand 1 White Oak Splinter 36,00
Alex Madrid 1 Eternal Union 47,00
Alex Madrid 1 Homunculus (Diablo III) 517,00
Alex Madrid 1 Madstone 795,00
Alex Madrid 1 Shields 380,00
Alex Madrid 1 Thing of the Deep 799,00
Alex Pink Mink 1 Alabaster Gloves 65,00
Alex Pink Mink 1 Bezoar Stone 510,00
Alex Pink Mink 1 Key of Bones 452,00
Alex Pink Mink 1 Moratorium 658,00
Alex Pink Mink 1 Sky Splitter 14,00
Alex Singapore 1 Rydraelm Tome 625,00
Alex Singapore 1 Shields 380,00
Alex Singapore 1 The Three Hundredth Spear 732,00
Alex Singapore 1 Two-Handed Flails 583,00
baleremuda Alderaan 1 Bane of the Stricken 445,00
baleremuda Alderaan 1 Blood-Magic Blade 529,00
baleremuda Alderaan 1 Drakons Lesson 341,00
baleremuda Alderaan 1 Essence of the Twin Seas 468,00
baleremuda Alderaan 1 Hilt of the Silver Wolf 26,00
baleremuda Alderaan 1 Incense Torch of the Grand Temple 533,00
baleremuda Alderaan 1 Pig Sticker 189,00
baleremuda Alderaan 1 Rabid Strike 231,00
baleremuda Alderaan 1 The Inquisitor 590,00
baleremuda Alderaan 1 Visage of Giyua 167,00
baleremuda Bali 1 Mojos 604,00
baleremuda Bali 1 Ramaladnis Gift 326,00
baleremuda Bali 1 Rings 673,00
baleremuda Bali 1 The Grin Reaper 375,00
baleremuda Foxtail fern 1 Echoing Fury 354,00
baleremuda Foxtail fern 1 Legendary Gems 244,00
baleremuda Foxtail fern 1 List of Legendary Items 67,00
baleremuda Foxtail fern 1 Prides Fall 465,00
baleremuda Foxtail fern 1 Sacred Harvester 678,00
baleremuda Foxtail fern 1 The Spider Queen’s Grasp 326,00
baleremuda Kiev 1 Bottomless Potion of Amplification 90,00
baleremuda Kiev 1 Emimeis Duffel 375,00
baleremuda Kiev 1 Erlang Shen 105,00
baleremuda Kiev 1 Fire Brand 235,00
baleremuda Kiev 1 Manticore 243,00
baleremuda Kiev 1 Spirit Guards 775,00
baleremuda São Paulo 1 Cindercoat 326,00
baleremuda São Paulo 1 Esoteric Alteration 675,00
baleremuda São Paulo 1 Oculus Ring 799,00
baleremuda São Paulo 1 Serpents Sparker 354,00
baleremuda São Paulo 1 The Grand Vizier 298,00
baroquegainful Kuala Lumpur 1 Cloaks 249,00
baroquegainful Kuala Lumpur 1 Gift of Silaria 527,00
baroquegainful Kuala Lumpur 1 Mad Monarchs Scepter 370,00
baroquegainful Kuala Lumpur 1 Pauldrons of the Skeleton King 183,00
baroquegainful Kuala Lumpur 1 String of Ears (Diablo III) 113,00
baroquegainful London 1 Blind Faith 528,00
baroquegainful London 1 Bottomless Potion of the Diamond 615,00
baroquegainful London 1 Captured Soul 380,00
baroquegainful London 1 Cord of the Sherma 739,00
baroquegainful London 1 Earthshatter 160,00
baroquegainful London 1 Flails 278,00
baroquegainful London 1 Golden Scourge 477,00
baroquegainful London 1 Ice Climbers 166,00
baroquegainful London 1 Longshot 565,00
baroquegainful London 1 Pledge of Caldeum 193,00
baroquegainful London 1 Sever 782,00
baroquegainful London 1 The Ess of Johan 613,00
baroquegainful London 1 Vengeful Wind 507,00
baroquegainful London 1 Wooden Stake 96,00
baroquegainful Macau 1 Angelic Shard 517,00
baroquegainful Macau 1 Etrayu 576,00
baroquegainful Macau 1 Jaces Hammer of Vigilance 410,00
baroquegainful Macau 1 Penders Purchase 232,00
baroquegainful Macau 1 Split Tusk 717,00
baroquegainful Seoul 1 Belt of the Trove 387,00
baroquegainful Seoul 1 Empyrean Messenger 503,00
baroquegainful Seoul 1 Giant Skull (Diablo III) 2,00
baroquegainful Seoul 1 Greater Rift Keystone 396,00
baroquegainful Seoul 1 Hand Crossbows 302,00
baroquegainful Seoul 1 Inviolable Faith 305,00
baroquegainful Seoul 1 Mutilation Guard 500,00
baroquegainful Seoul 正确的输出应该如下所示：
附注：通过选择下面的w/o Items.Price，用户名被正确地分组，我正在粘贴一个屏幕快照，因为30.000字符的标记被击中了，否则：
回答 1
Database Administration用户
回答已采纳
发布于 2020-06-14 21:52:33
除非我误解了，希望输出的是用户名，游戏，购买的项目总数，以及为项目支付的总价。这可以通过计算项目并将价格相加得到，如下所示：
SELECT u.[Username]
, g.[Name]
, COUNT(i.[Name]) AS [Items Count]
, SUM(i.Price) AS [Items Price]
FROM UsersGames AS ug
LEFT JOIN Games AS g
ON g.[Id] = ug.[GameId]
JOIN UserGameItems AS ugi
ON ugi.[UserGameId] = ug.[Id]
JOIN Items AS i
ON i.[Id] = ugi.[ItemId]
JOIN Users AS u
ON u.Id = ug.UserId
GROUP BY u.[Username], g.[Name];若要获得每个用户名和游戏的单一价格，当您将项目价格包含在组中时，所有的价格必须是相同的。但是，这不是您所拥有的，因为您不希望在结果集中包括实际的项详细信息。因此，从组中删除项目价格，然后对列进行求和，就会给出您想要的结果。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/269139
复制相关文章
相似问题
腾讯云开发者
