足球头对头 [英] Soccer head to head

问题描述

如果我发送查询[team-Id] 1& ;;我希望得到总和（目标）两个团队从头到头Teaams如果我发送查询[team-Id] 2& 2，则2将返回3-1 3不应该返回任何东西

I want to get sum(goals) both team from head to head Teaams if i send query [team-Id] 1 & 2 will return 3-1 if i send query [team-Id] 2 & 3 should not return anything

Tour-Id, Match Id, Team-Id, Goals
    1       1          1       1
    1       1          2       1
    1       1          1       1
    1       1          1       1

    1       2          1       1
    1       2          3       1

我尝试了什么：

What I have tried:

PARAMETERS teamA Long, teamB Long;
SELECT (SELECT SUM(Goal) FROM [Match Details] WHERE [tid] = teamA) AS Goals_Scored, (SELECT SUM(Goal) FROM [Match Details] WHERE [tid] = teamB) AS Goals_Scored2
FROM (SELECT DISTINCT [tid]    
        FROM [Match Details]  )  AS tmp
GROUP BY tid;

推荐答案

事实证明这很困难，所以我不禁想到我错过了什么。但是，这里有...



第一个问题是获得两队的比赛ID。我想出了这个（我确信这可能更简单，我现在看不到它）

This is turning out to be quite difficult, so I can't help but think that I've missed something. However, here goes...

First problem was to get the Match Id for the two teams. I came up with this (which is the bit I'm sure can be simpler, I just can't see it at the moment)

SELECT [Tour-Id], [Match Id], [Team-Id], Goals 
	FROM [Match Details] 	WHERE [Match Id] IN (
			SELECT DISTINCT [Match Id] FROM [Match Details] WHERE [Team-Id] = 1 AND [Match Id] IN (SELECT [Match Id] FROM [Match Details] WHERE [Team-Id] = 2))

然后插入进入一个WHERE子句，以便我可以得到所有相关的分数：

I then plugged that into a WHERE clause so that I could get all of the relevant scores:

SELECT [Tour-Id], [Match Id], [Team-Id], Goals 
	FROM [Match Details] 	WHERE [Match Id] IN (
			SELECT [Match Id] FROM [Match Details] WHERE [Team-Id] = 1 AND [Match Id] IN (SELECT [Match Id] FROM [Match Details] WHERE [Team-Id] = 2))

然后我将其插入PIVOT（参见在SQL查询中使用Pivot的简单方法 [ ^ ]）

I then plugged that into a PIVOT (see Simple Way To Use Pivot In SQL Query[^] )

SELECT [Tour-Id], [Match Id],ISNULL([1],0) AS [Team 1 Score],ISNULL([2],0) AS [Team 2 Score]
FROM (	SELECT [Tour-Id], [Match Id], [Team-Id], Goals 
	FROM [Match Details] 	WHERE [Match Id] IN (
			SELECT DISTINCT [Match Id] FROM [Match Details] WHERE [Team-Id] = 1 AND [Match Id] IN (SELECT [Match Id] FROM [Match Details] WHERE [Team-Id] = 2))
) as s PIVOT (SUM(Goals) FOR [Team-Id] IN ([1],[2])) AS pvt

结果

Tour    Match   Team 1 Score  Team 2 Score
1	1	3	      1

下一个问题当然是我已经对第1组和第2组进行了硬编码。我可以在WHERE子句中更改它但我不能在PIVOT中使用变量。所以我不得不创建一个动态查询并运行它。所以我的最终代码是：

The next problem of course is that I've hard-coded team 1 and team 2. I can change that in the WHERE clause but I can't use variables in the PIVOT. So I had to create a dynamic query and run that. So my final code is:

--Parameters passed in
declare @teamA int = 1
declare @teamB int = 2

DECLARE @sql nvarchar(max)
DECLARE @A nvarchar(3) = CAST(@teamA AS nvarchar)
DECLARE @B nvarchar(3) = CAST(@teamB AS nvarchar)

SET @sql = 'SELECT [Tour-Id], [Match Id],ISNULL([' + @A + '],0) AS [Team ' + @A + ' Score],ISNULL(['
+ @B + '],0) AS [Team ' + @B + ' Score]
FROM (	SELECT [Tour-Id], [Match Id], [Team-Id], Goals 
	FROM [Match Details] 	WHERE [Match Id] IN (
			SELECT DISTINCT [Match Id] FROM [Match Details] WHERE [Team-Id] = ' + @A + 
					' AND [Match Id] IN (SELECT [Match Id] FROM [Match Details] WHERE [Team-Id] = ' + @B + '))
) as s PIVOT (SUM(Goals) FOR [Team-Id] IN ([' + @A + '],[' + @B + '])) AS pvt'

EXEC sp_executeSQL @sql

顺便提一下你的桌子有问题。如果球队没有进球，会发生什么？你将无法分辨哪支队伍参加了比赛。



如果我这样做，我会有一张桌子给出了比赛详情：

There is a problem with your table by the way. What happens if a team does not score any goals? You won't be able to tell which teams played in that match.

If I was doing this I would have a table that gave the Match Details:

create table [Match Details2]
(
	id int identity(1,1),
	[Tour-id] int,
	[Match Id] int,
	TeamA int,
	TeamB int
        -- probably other columns for Date, Venue etc
)
insert into [Match Details2] values
(1,1,1,2),
(1,2,1,3)

还有一个单独的表记录了所有得分：

And a separate table that records any goals scored:

create table [Match Scores]
(
	MatchId int,	-- FK to [Match Details2]
	TeamId int,
	GoalTime int  -- minutes into the game when goal is scored
        -- potentially other columns like Scorer, type of Goal etc
)

insert into [Match Scores] values
(1, 1, 15),
(1, 2, 30),
(1, 1, 35),
(1, 1, 70),
(2, 1, 40),
(2, 3, 76)

我只是做了几次，但我可以通过这个查询得到原始数据：

I just made up some times but I can get your original data back with this query:

select [Tour-id], [Match Id], TeamId, 1 as Goal
from [Match Details2] M
inner join [Match Scores] S ON M.[Match Id]=S.MatchId

最终查询变得更加简单（尽管需要动态sql仍然存在）：

The final query becomes much simpler (although the need for dynamic sql still stands):

SELECT * FROM (
SELECT [Tour-id], [Match Id], TeamId, COUNT(GoalTime) as Goals
FROM [Match Details2] M
INNER JOIN [Match Scores] S ON M.id=S.MatchId
WHERE (M.TeamA = @teamA AND M.TeamB = @teamB) OR (M.TeamA = @teamB AND M.TeamB = @teamA)
GROUP BY [Tour-id], [Match Id], TeamId
) pvt
PIVOT (	MAX(Goals) FOR TeamId in ([1],[2])) S

这篇关于足球头对头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！