Какой SQL-запрос или представление покажет "динамические столбцы"
У меня есть таблица данных, и я разрешаю людям добавлять в эту таблицу метаданные.
Я даю им интерфейс, который позволяет им обрабатывать его так, как будто они добавляют дополнительные столбцы в таблицу, в которой хранятся их данные, но на самом деле я храню данные в другой таблице.
Data Table
DataID
Data
Meta Table
DataID
MetaName
MetaData
Поэтому, если бы им нужна была таблица, в которой хранятся данные, дата и имя, у меня были бы данные в таблице данных, и слово «Дата» в мета-имени, и дата в метаданных, и еще одна строка в таблица meta с «Name» в метаниме и именем в метаданных.
Теперь мне нужен запрос, который берет информацию из этих таблиц и представляет ее так, как если бы она поступала из одной таблицы с двумя дополнительными столбцами «Данные» и «Имя», чтобы для клиента это выглядело так, как будто есть одна таблица с их настраиваемыми столбцами. :
MyTable
Data
Date
Name
Или, другими словами, как мне отсюда уйти:
Data Table
DataID Data
1 Testing!
2 Hello, World!
Meta Table
DataID MetaName MetaData
1 Date 20081020
1 Name adavis
2 Date 20081019
2 Name mdavis
Сюда:
MyTable
Data Date Name
Testing! 20081020 adavis
Hello, World! 20081019 mdavis
Много лет назад, когда я делал это в MySQL с использованием PHP, я выполнил два запроса: первый для получения дополнительных метаданных, а второй - для их объединения. Я надеюсь, что в современных базах данных есть альтернативные методы решения этой проблемы.
Относится к варианту 3 этот вопрос.
-Адам
Ответы 3
SELECT DataTable.Data AS Data, MetaTable.MetaData AS Date, MetaTable.MetaName AS Name
FROM DataTable, MetaTable
WHERE DataTable.DataID = MetaTable.DataID
Вероятно, вы захотите добавить дополнительное предложение (AND Data = 'some value'), чтобы возвращать строки, которые интересуют пользователя.
AFAIK, вы можете сделать это на стороне сервера только с помощью динамической хранимой процедуры SQL.
Фактически код, который вы хотите генерировать динамически, выглядит следующим образом:
SELECT [Data Table].*
,[MyTable Date].MetaData
,[MyTable Name].MetaData
FROM [Data Table]
LEFT JOIN [MyTable] AS [MyTable Date]
ON [MyTable Date].DataID = [Data Table].DataID
AND [MyTable Date].MetaName = 'Date'
LEFT JOIN [MyTable] AS [MyTable Name]
ON [MyTable Name].DataID = [Data Table].DataID
AND [MyTable Name].MetaName = 'Name'
И вот код для этого:
DECLARE @sql AS varchar(max)
DECLARE @select_list AS varchar(max)
DECLARE @join_list AS varchar(max)
DECLARE @CRLF AS varchar(2)
DECLARE @Tab AS varchar(1)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Tab = CHAR(9)
SELECT @select_list = COALESCE(@select_list, '')
+ @Tab + ',[MyTable_' + PIVOT_CODE + '].[MetaData]' + @CRLF
,@join_list = COALESCE(@join_list, '')
+ 'LEFT JOIN [MyTable] AS [MyTable_' + PIVOT_CODE + ']' + @CRLF
+ @Tab + 'ON [MyTable_' + PIVOT_CODE + '].DataID = [DataTable].DataID' + @CRLF
+ @Tab + 'AND [MyTable_' + PIVOT_CODE + '].MetaName = ''' + PIVOT_CODE + '''' + @CRLF
FROM (
SELECT DISTINCT MetaName AS PIVOT_CODE
FROM [MyTable]
) AS PIVOT_CODES
SET @sql = 'SELECT [DataTable].*' + @CRLF
+ @select_list
+ 'FROM [DataTable]' + @CRLF
+ @join_list
PRINT @sql
--EXEC (@sql)
Вы можете использовать аналогичный динамический метод, используя пример оператора CASE для выполнения поворота.
Да, однако, вы можете сделать это динамически в одной сохраненной процедуре. Если ваши MetaNames фиксированы, вы можете использовать приведенный выше запрос как есть, но в противном случае он будет динамическим. Вскоре я опубликую код, который быстро его генерирует.
Спасибо, это было бы полезно. Не могли бы вы также упомянуть, на каком SQL-сервере вы знаете, что это работает?
Это будет работать на SQL Server, используя трюк SELECT для создания динамических строк без курсора. На других платформах есть CONCAT, или вам, возможно, придется использовать курсор. Сгенерированный им запрос должен быть в достаточной степени ANSI (вам нужно будет следить за плохими символами в именах таблиц или в MetaName).
Вы хотите развернуть каждую из строк пары имя-значение в MyTable ... Попробуйте этот sql:
DECLARE @Data TABLE (
DataID INT IDENTITY(1,1) PRIMARY KEY,
Data VARCHAR(MAX)
)
DECLARE @Meta TABLE (
DataID INT ,
MetaName VARCHAR(MAX),
MetaData VARCHAR(MAX)
)
INSERT INTO @Data
SELECT 'Data'
INSERT INTO @Meta
SELECT 1, 'Date', CAST(GetDate() as VARCHAR(20))
UNION
SELECT 1, 'Name', 'Joe Test'
SELECT * FROM @Data
SELECT * FROM @Meta
SELECT
D.DataID,
D.Data,
MAX(CASE MetaName WHEN 'Date' THEN MetaData ELSE NULL END) as Date,
MAX(CASE MetaName WHEN 'Name' THEN MetaData ELSE NULL END) as Name
FROM
@Meta M
JOIN @Data D ON M.DataID = D.DataID
GROUP BY
D.DataID,
D.Data
Подробнее о CASE лом SQL. 4guysfromrolla.com/webtech/102704-1.shtml
Как лучше всего запросить результат? Пример: SELECT * FROM Table WHERE Name = 'Joe Test' Нужно ли мне сохранять первые результаты и запрашивать эту таблицу? Или есть способ внедрить фильтр WHERE на первом проходе?
Итак, это вариант с двумя запросами, где я сначала выясняю, какие «динамические» столбцы мне нужно извлечь из метатаблицы, а затем составляю запрос из этих результатов?