问题描述
我正在尝试创建一个足球池，用户可以在其中挑选每场比赛的胜者。然后我想在用户选择旁边显示用户选择和单词Won或Lost。我还想显示每个用户拥有的胜利数量。
我有第一部分工作，但我想计算每一行的胜利。
我有2张桌子。
1st Table：UserPicks
I'm trying to make a Football pool where the user picks the winner of each game. Then I would like to display users picks and the word Won or Lost next to the users picks. I also would like to display the number of wins each user has.
I have the first part working but I'm trying to count the wins in each row.
I have 2 tables.
1st Table: UserPicks
Columns: FullName, Week, Game_1, Game_2, Game_3, Game_4, Game_5
2nd Table：WeeklyResults - 仅赢得团队的名称
2nd Table: WeeklyResults - Only name of teams that won
Columns: Week, GameResults_1, GameResults_2, GameResults_3, GameResults_4, GameResults_5
感谢您提供任何帮助...
我尝试过：
这显示赢或输。这很好用
Thanks for any help you give...
What I have tried:
This displays a Win or Lost. This is working good
SELECT UserPicks.FullName, UserPicks.Game_1, UserPicks.Game_2, UserPicks.Game_3, UserPicks.Game_4, UserPicks.Game_5,
(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 'Win' ELSE 'Lost' END) AS GameResult_1,
(CASE WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 'Win' ELSE 'Lost' END) AS GameResult_2,
(CASE WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 'Win' ELSE 'Lost' END) AS GameResult_3,
(CASE WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 'Win' ELSE 'Lost' END) AS GameResult_4,
(CASE WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 'Win' ELSE 'Lost' END) AS GameResult_5
FROM UserPicks
JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
WHERE WeeklyResults.Week = 'Week1'
这是我尝试计算胜利的代码：< br>但是它无法正常工作
This is my code to try and count the wins:<br> But it's not working correctly
SELECT FullName, SUM(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 1
WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 1
WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 1
WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 1
WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 1 END) AS [Count of Wins]
FROM UserPicks
JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
GROUP BY FullName推荐答案
SELECT FullName, Game_1, Game_2, Game_3, Game_4, Game_5, GameResult_1 + GameResult_2 + GameResult_3 + GameResult_4 + GameResult_5 AS TotalPoints
FROM (
SELECT UserPicks.FullName, UserPicks.Game_1, UserPicks.Game_2, UserPicks.Game_3, UserPicks.Game_4, UserPicks.Game_5,
(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 1 ELSE 0 END) AS GameResult_1,
(CASE WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 1 ELSE 0 END) AS GameResult_2,
(CASE WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 1 ELSE 0 END) AS GameResult_3,
(CASE WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 1 ELSE 0 END) AS GameResult_4,
(CASE WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 1 ELSE 0 END) AS GameResult_5
FROM UserPicks
JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
WHERE WeeklyResults.Week = 'Week1' ) AS CommonData
至于2。
假设您的查询返回如下内容：
As to 2.
Assuming that your query returns something like this:
FullName Game1 Game2 Game3 Game4 Game5 GameResult1 GameResult2 GameResult3 GameResult4 GameResult5
User1 Game1 Game2 Game3 Game4 Game5 1 1 0 1 0
User1 Game1 Game2 Game3 Game4 Game5 1 0 1 1 0
User1 Game1 Game2 Game3 Game4 Game5 0 1 1 1 1
User1 Game1 Game2 Game3 Game4 Game5 1 1 0 0 0
User1 Game1 Game2 Game3 Game4 Game5 0 1 0 1 0
使用：
use:
-- Create the table and insert sample data.
DECLARE @commondata TABLE (FullName VARCHAR(30), Game1 VARCHAR(30), Game2 VARCHAR(30), Game3 VARCHAR(30), Game4 VARCHAR(30), Game5 VARCHAR(30),
GameResult1 INT, GameResult2 INT, GameResult3 INT, GameResult4 INT, GameResult5 INT)
INSERT INTO @commondata (FullName, Game1, Game2 , Game3, Game4, Game5,
GameResult1, GameResult2, GameResult3, GameResult4, GameResult5)
VALUES ('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,1,0,1,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,0,1,1,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 0,1,1,1,1),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,1,0,0,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 0,1,0,1,0)
-- Unpivot the table.
SELECT FullName, Game, SUM(Points) As Total
FROM
(SELECT *
FROM @commondata) pvt
UNPIVOT
(Points FOR Game IN
(GameResult1, GameResult2, GameResult3, GameResult4, GameResult5)
)AS unpvt
GROUP BY FullName, Game
结果：
Result:
FullName Game Total
User1 GameResult1 3
User1 GameResult2 4
User1 GameResult3 2
User1 GameResult4 4
User1 GameResult5 1
如果你只想要 FullName 和总积分，删除游戏列fr om SELECT list和 GROUP BY 声明。
In case you want only FullName and total points, remove Game column from SELECT list and GROUP BY statement.
这篇关于我正在尝试计算或获取与其他表中的其他值匹配的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！