我正在尝试计算或获取与其他表中的其他值匹配的值的总和 [英] I'm trying to count or get the sum of values that match other values in a different table

问题描述

我正在尝试创建一个足球池，用户可以在其中挑选每场比赛的胜者。然后我想在用户选择旁边显示用户选择和单词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

推荐答案

嗯，你在这里得到答案： Sql CASE语句无法正常工作 [ ^ ]，但您甚至不会学习如何优化数据库。悲伤......



你有两个选择：

1.总结每场比赛（简单）

2.取消数据并对每个数据求和（更多com复制）





至于1.

Well, you've got your answer here: Sql CASE statement not working[^], but you won't even learn how to optimise your database... Sad...

You've got 2 options:
1. Sum up every game (simple)
2. unpivot data and sum each (more complicated)


As to 1.

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.

这篇关于我正在尝试计算或获取与其他表中的其他值匹配的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！