CHill60
Это оказывается довольно трудно, поэтому я не могу не думать, что я что-то упустил. Тем не менее, здесь идет...
Первая проблема состояла в том, чтобы получить идентификатор матча для двух команд. Я придумал это (что, я уверен, может быть проще, я просто не вижу этого в данный момент)
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, чтобы получить все соответствующие оценки:
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-запросе[
^] )
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. Поэтому мне пришлось создать динамический запрос и запустить его. Итак, мой окончательный код таков:
--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
Кстати, есть проблема с вашим столом. Что будет, если команда не забьет ни одного гола? Вы не сможете сказать, какие команды играли в этом матче.
Если бы я делал это, у меня была бы таблица, которая давала бы Детали матча:
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)
И отдельная таблица, которая записывает любые забитые голы:
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)
Я просто придумал несколько раз, но я могу получить ваши исходные данные обратно с помощью этого запроса:
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 все еще остается):
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