MSSQL: получить результаты выбора в той же строке
Сначала позвольте мне объяснить вам мою конструкцию БД.
Я использую MSSQL с драйвером sqlsrv php.
У меня есть две таблицы БД Names и AccountFieldValues.
Имена имеют переменное поле NameID и поле исправления StringValue.
Эта таблица содержит все имена переменных поля account ergo: FirstName, LastName и т. Д.
AccountFieldValues имеет одинаковые переменные поля FieldNameID, FieldValue и IdentityID.
Эта таблица содержит содержимое переменных поля account для каждого пользователя.
Теперь, чтобы получить конкретное переменное содержимое пользователя с IdentityID = 10 и StringValue = ‘FirstName’, вы можете создать такой запрос:
SELECT FieldValue FROM AccountFieldValues
WHERE IdentityID=10 AND FieldNameID IN (SELECT NameID FROM Names WHERE StringValue='FirstName')
Или с внутренним соединением
SELECT ACF.FieldValue FROM AccountFieldValues ACF
INNER JOIN Names N ON (N.StringValue='FirstName' AND N.NameID=ACF.FieldNameID)
WHERE ACF.IdentityID=10
Когда вы хотите содержимое одной переменной, оба метода работают хорошо.
Но теперь возникает проблема, когда я хочу получить несколько переменных содержимого в одном запросе, это становится сложно.
Я могу расширить два запроса, как это:
SELECT FieldValue FROM AccountFieldValues
WHERE IdentityID=10 AND FieldNameID IN
(SELECT NameID FROM Names WHERE StringValue='FirstName' OR StringValue='LastName')
И другой запрос, как этот:
SELECT ACF.FieldValue AS 'FirstName', ACF2.FieldValue AS 'LastName' FROM AccountFieldValues ACF
INNER JOIN Names N ON (N.StringValue='FirstName' AND N.NameID=ACF.FieldNameID)
INNER JOIN AccountFieldValues ACF2 ON (ACF2.IdentityID=ACF.IdentityID)
INNER JOIN Names N2 ON (N2.StringValue='LastName' AND N2.NameID=ACF2.FieldNameID)
WHERE ACF.IdentityID=10
Хотя это и ужасно, по сравнению с верхним методом, это даст мне результат FirstName и LastName в одной строке результатов. как это:
FirstName|LastName
------------------
Foo |Bar
С другой стороны, первый метод даст мне результат, похожий на этот:
FieldValue
----------
Foo
Bar
Это нежелательно, потому что я не могу получить требуемые AccountFieldValues для каждого пользователя в одном наборе результатов
Есть ли метод для получения результата таблиц, как с уродливыми объединениями, но более быстрый и легко расширяемый, как первый метод?
Решение
То, что вы ищете, это способ поворота ваших данных. Вот пример использования условного агрегирования:
SELECT
FirstName = MAX(CASE WHEN n.StringValue = 'FirstName' THEN acf.FieldValue END),
LastName = MAX(CASE WHEN n.StringValue = 'LastName' THEN acf.FieldValue END)
FROM AccountFieldValues acf
INNER JOIN Names n
ON n.NameId = acf.FieldNameId
WHERE acf.IdentityId = 10
GROUP BY acf.IdentityId
Это не динамично. Вы также можете удалить JOIN на Names и использовать acf.FieldId в вашем CASE вместо. Как это:
MAX(CASE WHEN acf.FieldId = <FIELD_ID_OF_FIRSTNAME> THEN acf.FieldValue END)
Если вам нужен динамический подход, вот один из способов использования динамического SQL:
DECLARE @sql1 NVARCHAR(MAX) = '',
@sql2 NVARCHAR(MAX) = '',
@sql3 NVARCHAR(MAX) = ''
DECLARE @identityId INT = 10
SELECT @sql1 =
'SELECT
acf.IdentityId' + CHAR(10)
SELECT @sql2 = @sql2 +
' , MAX(CASE WHEN acf.FieldNameId = ' + CONVERT(VARCHAR(10), t.FieldNameId) + ' THEN acf.FieldValue END) AS '
+ QUOTENAME(n.StringValue) + CHAR(10)
FROM(
SELECT DISTINCT FieldNameId FROM AccountFieldValues WHERE IdentityId = @identityId
)t
INNER JOIN Names n ON t.FieldNameId = n.NameId
SELECT @sql3 =
'FROM AccountFieldValues acf
INNER JOIN Names n
ON n.NameId = acf.FieldNameId
WHERE acf.IdentityId = @identityId
GROUP BY acf.IdentityId'
DECLARE @finalSql NVARCHAR(MAX) = ''
SET @finalSql = @sql1 + @sql2 + @sql3
EXEC sp_executesql @finalSql, N'@identityId INT', @identityId
Другие решения
Я обычно делаю это так:
SELECT *
FROM dbo.Names AS N
CROSS APPLY (
SELECT MAX(CASE WHEN N.StringValue='FirstName' THEN AFV.FieldValue END)
, MAX(CASE WHEN N.StringValue='LastName' THEN AFV.FieldValue END)
FROM dbo.AccountFieldValues AS AFV
WHERE AFV.FieldNameID = N.NameID
) AS AFV(FirstName, LastName);
И вы можете легко добавлять новые строки в CROSS APPLY там. Это в значительной степени PIVOT как Феликс Памиттан сказал.