CHill60
Я использовал этот код, чтобы воссоздать ваш сценарий (включая его здесь на случай, если у вас есть запрос на мое решение!)
declare @tab table (ResultID int, Test_UK int, Test_US int, Test_UK_Score int, Test_US_Score int)
insert into @tab (ResultID,Test_UK,Test_US,Test_UK_Score,Test_US_Score) values
( 1 , 1 , 2 , 48 , 11),
( 2 , 4 , 1 , 21 , 24),
( 3 , 3 , 1 , 55 , 71),
( 4 , 5 , 6 , 18 , 78),
( 5 , 7 , 4 , 19 , 49),
( 6 , 1 , 3 , 23 , 69),
( 7 , 5 , 2 , 98 , 35),
( 8 , 6 , 7 , 41 , 47)
Теперь я смог получить ваши результаты, используя табличную переменную (аналогичную моим тестовым данным), но вы, вероятно, классифицировали бы ее как "временную таблицу". Я также смог получить ваши результаты, используя общее табличное выражение (CTE), но вы, вероятно, классифицировали бы его как "подзапрос", хотя на самом деле это не так.
Есть еще один способ сделать это с помощью
оператор UNPIVOT[
^]. Осложнение возникает здесь потому, что вам нужно распустить на
два столбцы для получения нужных вам результатов.
Здесь есть отработанный пример -
SQL Banana: SQL UNPIVOT на нескольких Столбцах[
^]
В итоге я получил
select TestId, MAX(Score) AS MaxScore, MIN(Score) AS MinScore
FROM
(
SELECT ResultId, Test_UK, Test_US, Test_UK_Score,Test_US_Score
FROM @tab
) src
UNPIVOT
(
TestId FOR T IN (Test_UK, Test_US)
) PVT1
UNPIVOT
(
Score for S IN (Test_UK_Score, Test_US_Score)
) PVT2
WHERE SUBSTRING(S, 6,2) = SUBSTRING(T, 6,2)
GROUP BY TestID
Моменты, которые следует отметить:
- Это может показаться похожим на "подзапрос", но это всего лишь синтаксис для
UNPIVOT запрос
- Вам не нужно "гнездиться" в доме.
UNPIVOTс - ты просто связываешь их вместе
- Обратите внимание на колонки "составлено"
T и
S. Вот где происходит реальная сила сопоставления Test_UK с Test_UK_Score - попробуйте вынуть это
WHERE и посмотрим, что из этого получится.
EDIT: знаете ли вы, что ваш дизайн таблицы не очень хорош - он должен быть нормализован. Одним из способов сделать это было бы использовать конструкцию объединения, упомянутую @mohibur-rashid, чтобы вставить нормализованные данные в новую (постоянную) таблицу, например
declare @tab2 table (ResultID int, TestId int, Score int, TestCountry varchar(3))
insert into @tab2
select ResultId, Test_UK as TestId, Test_UK_Score as Score, 'UK' as TestCountry
from @tab
union
select ResultId, Test_US, Test_US_Score, 'US' as TestCountry
from @tab
Обратите внимание на новую колонку, в которой указывается страна, в которой проводился тест.
Почему это лучше? Ну получить желаемые результаты тривиально
select TestId, MAX(score) AS MaxScore, MIN(Score) as MinScore
from @tab2
GROUP BY TestId
Он также расширяемый. Если бы вы начали сдавать тесты и в Австралии, то вам пришлось бы потрудиться изменить исходную таблицу и изменить свой запрос. Но с нормализованной версией вам просто нужно иметь новый код для TestCountry. например
insert into @tab2 (ResultID, TestId, Score, TestCountry) values
(9, 8, 41, 'AU'),
(10, 8, 32, 'US'),
(11, 8, 42, 'UK')
И никаких изменений в запросе вообще.
Конструкция UNPIVOT часто используется для нормализации таблиц "на лету"