Это проблема, которую я потратил на то, чтобы исследовать в прошлом. Мне кажется, что это должно было быть рассмотрено современными решениями RDBMS, но пока я не нашел ничего, что действительно касалось того, что я вижу быть невероятно распространенной потребностью в любом приложении Web или Windows с базовым контентом базы данных.
Я говорю о динамической сортировке. В моем мире фантазий это должно быть так просто, как что-то вроде:
ORDER BY @sortCol1, @sortCol2
Это канонический пример, предоставленный разработчиками newbie SQL и Хранимой процедурой на всех форумах через Интернет. "Почему это невозможно?" они спрашивают. Неизменно, кто-то в конце концов приходит, чтобы прочесть их о скомпилированном характере хранимых процедур, планов выполнения в целом и всех других причинах, по которым невозможно поставить параметр непосредственно в предложение ORDER BY.
Я знаю, что некоторые из вас уже думают: "Пусть клиент сделает сортировку". Естественно, это разгружает работу из вашей базы данных. Однако в нашем случае наши серверы баз данных даже не разбивают пот в 99% случаев, и они даже не являются многоядерными, а также любые другие незначительные улучшения в архитектуре системы, которые происходят каждые 6 месяцев. По этой причине, если сортировка сортировки наших баз данных не будет проблемой. Кроме того, базы данных очень хороши при сортировке. Они оптимизированы для этого, и у них были годы, чтобы понять это, язык для этого невероятно гибкий, интуитивно понятный и простой, и, прежде всего, любой начинающий разработчик SQL знает, как это сделать, и тем более важно знать, как его редактировать, вносить изменения, выполнять техническое обслуживание и т.д. Когда ваши базы данных далеки от налогообложения, и вы просто хотите упростить (и сократить) время разработки, это кажется очевидным выбором.
Тогда есть веб-проблема. Я играл с JavaScript, который будет выполнять сортировку HTML-таблиц на стороне клиента, но они неизбежно недостаточно гибки для моих нужд, и, опять же, поскольку мои базы данных не облагаются налогом и могут действительно сортировать действительно очень легко, я имеют трудное время, оправдывая время, которое потребуется для повторной записи или сортировки соавторов JavaScript. Как правило, это относится к сортировке на стороне сервера, хотя это, вероятно, гораздо предпочтительнее, чем JavaScript. Я не тот, кому особенно нравится накладные расходы DataSets, поэтому подавайте в суд на меня.
Но это приводит к тому, что это невозможно...— или, скорее, не легко. Я сделал, с предыдущими системами, невероятно рушительный способ получения динамической сортировки. Это было некрасиво, не интуитивно, просто или гибко, а начинающий автор SQL-кода терялся в течение нескольких секунд. Уже сейчас это выглядит не столько "решением", сколько "осложнением".
Следующие примеры не предназначены для раскрытия каких-либо передовых методов или хорошего стиля кодирования или чего-либо еще, и они не свидетельствуют о моих способностях как программиста T-SQL. Они такие, какие они есть, и я полностью признаю, что они сбивают с толку, плохая форма и просто хак.
Мы передаем целочисленное значение в качестве параметра хранимой процедуры (позвольте этому параметру просто "сортировать" ), и из этого мы определим связку других переменных. Например... let say sort is 1 (или по умолчанию):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
Вы уже можете видеть, как если бы я объявлял больше переменных @colX для определения других столбцов, я мог бы действительно создавать объявления с сортировкой столбцов на основе значения "сортировка"... чтобы использовать его, он обычно заканчивается поиском как и следующая невероятно беспорядочная статья:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
Очевидно, это очень урезанный пример. Реальный материал, так как у нас обычно есть четыре или пять столбцов для поддержки сортировки, каждый с возможным вторичным или даже третьим столбцом для сортировки в дополнение к этому (например, дата по убыванию, затем сортировка по порядку по имени возрастает) и каждый поддерживающий би- направленная сортировка, которая эффективно удваивает количество случаев. Да... он быстро причесывается.
Идея состоит в том, что можно "легко" изменить случаи сортировки, так что vehicleid сортируется до хранения в течение времени... но псевдо-гибкость, по крайней мере в этом простом примере, действительно заканчивается. По сути, каждый случай, который не прошел тест (поскольку наш метод сортировки не применяется к нему на этот раз) отображает значение NULL. И таким образом вы получаете предложение, которое работает следующим образом:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
Вы получаете идею. Он работает, потому что SQL Server эффективно игнорирует нулевые значения в соответствии с предложениями. Это невероятно сложно поддерживать, поскольку любой, у кого есть какие-либо базовые знания SQL, возможно, увидит. Если я потерял кого-то из вас, не чувствуйте себя плохо. Нам потребовалось много времени, чтобы заставить его работать, и мы все еще путаемся, пытаясь его отредактировать или создать новые. К счастью, ему не нужно часто меняться, иначе оно быстро станет "не стоило бы того".
Но это действительно сработало.
Мой вопрос: есть лучший способ?
У меня все в порядке с решениями, кроме хранимых процедур, поскольку я понимаю, что это может быть просто не выход. Предпочтительно, я хотел бы знать, сможет ли кто-нибудь сделать это лучше в хранимой процедуре, но если нет, как вы справляетесь с тем, чтобы позволить пользователю динамически сортировать таблицы данных (в двунаправленном порядке) с помощью ASP.NET? p >
И спасибо за то, что вы прочитали (или, по крайней мере, скимминг) такой длинный вопрос!
PS: Радуйтесь, я не показывал свой пример хранимой процедуры, поддерживающей динамическую сортировку, динамическую фильтрацию/текстовый поиск столбцов, разбиение на страницы с помощью ROWNUMBER() OVER и попытка... catch с откатом транзакций при ошибках... "behemoth-size" даже не начинает описывать их.
Update:
- Я бы хотел избежать динамического SQL. Разбор строки вместе и запуск EXEC на ней сильно осложняет задачу сохранения хранимой процедуры. Иногда я удивляюсь, хотя если бы недостатки такого дела не стоили бы этого, по крайней мере в этих специальных случаях динамической сортировки. Тем не менее, я всегда чувствую себя грязным, когда я делаю динамические строки SQL, подобные this — как я все еще живу в мире классического ASP.
- Большая причина, по которой мы хотим, чтобы хранимые процедуры в первую очередь были для безопасности. Я не собираюсь отвечать на вопросы безопасности, предлагаю только решения. С SQL Server 2005 мы можем устанавливать разрешения (на основе каждого пользователя, если необходимо) на уровне схемы для отдельных хранимых процедур, а затем напрямую отклонять любые запросы к таблицам. Критиковать плюсы и минусы этого подхода, возможно, по другому вопросу, но опять же это не мое решение. Я просто обезьяна кодов.:)
