T-Sql
GROUP BY 組內行欄位不同的多個條件
每個人，
我正在努力尋找以下任務的解決方案：
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.我已經正確地進行了必要的 JOIN。我不會用模式和特定的表及其相應的元組來打擾您。相反，請在上述“raw-dataset-JOIN”查詢的第一個結果下方找到。我請求您的幫助，因為我很感興趣如何按使用者名和名稱對結果進行分組，僅顯示“使用者名+名稱”多次出現的單行。我似乎無法得到它，因為項目在重複的“使用者名+名稱”事件中有所不同，對於 MS SQL Server，顯然數據不完全相同，因此可以正確“分組”？
是否應該通過使用 VIEW 或子查詢來獲得結果？
請協助 - 感謝您的幫助！
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正確的輸出應該是這樣的：
Ps 在 SELECT 中使用以下 w/o Items.Price 使用者名被正確分組，我粘貼了一個螢幕截圖，因為否則會命中 30.000 個字元標記：
除非我誤解了，否則所需的輸出是使用者名、遊戲、購買的物品總數以及為物品支付的總價格。這可以通過計算項目和對價格求和來獲得，如下所示：
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];當您在 group by 中包含項目價格時，要獲得每個使用者名和遊戲的單一價格，要求所有價格必須相同。但是，這不是您所擁有的，因為您不想在結果集中包含實際的項目詳細資訊。因此，從 group by 中刪除 item price 然後對該列求和應該會給您想要的結果。