如何避免SQL Server错误在ORDER BY上有重复的列 [英] How to avoid SQL Server error on ORDER BY with duplicate columns
问题描述
尽管此问题引用了PHP，但它实际上并不是特定于PHP的，因此我没有将其标记为此类.
我们有一个支持多个数据库后端的PHP框架.
We have a PHP framework which supports multiple DB back-ends.
我们的数据对象类中有一个通用函数，该函数可让您使用指定的条件和排序顺序从基础表中获取记录.
There is a generic function in our data object class, which allows you to get records from the underlying table, with a specified criteria and sort order.
它看起来像这样:
function GetAll($Criteria, $OrderBy = "") {
...
// Add primary key (column 1) to end of order by list,
// so that returned order is predictable.
if ($OrderBy != "") {
$OrderBy .= ", ";
}
$OrderBy .= "1";
...
// Build and run query, returning the result as an array.
}
如果在Staff对象上指定StaffID的$OrderBy自变量，则生成的SQL看起来类似于以下内容:
If you specify an $OrderBy argument of StaffID on a Staff object, the resulting SQL looks something like the following:
SELECT * FROM adminStaff ORDER BY StaffID, 1;
这在MySQL后端上可以正常工作，而从我的网上搜索来看，在大多数其他数据库后端上也应该可以.但是，使用SQL Server时，会出现以下错误消息:
This works fine on a MySQL back-end, and from my searching of the web it should also be fine on most other DB back-ends. However, when using SQL Server, we get the following error message:
A column has been specified more than once in the order by list.
Columns in the order by list must be unique.
这是因为SQL Server禁止同一列在ORDER BY子句中出现多次.在这种情况下，StaffID是列1，因此我们在同一列中有多个实例.
This arises because SQL Server disallows the same column appearing multiple times in the ORDER BY clause. In this case StaffID is column 1 and therefore we have multiple instances of the same column.
是否可以在SQL Server中禁用此检查? MySQL提供了许多选项来启用/禁用严格性检查和不兼容的功能-SQL Server是否提供了可以使上述查询无错误运行的那种性质的东西?
Is there a way to disable this check in SQL Server? MySQL provides a lot of options to enable/disable strictness checks and incompatible features - does SQL Server provide anything of that nature that would allow the above query to run without errors?
如果没有，您是否对如何在数据对象层中解决此问题有任何建议?请记住，我们需要与期望这种行为的现有项目保持兼容性，因此仅在$OrderBy为空白时仅包括第一列是不够的.
If not, do you have any suggestions for how we could resolve this in our data-object layer? Bear in mind we need to maintain compatibility with existing projects which expect this behaviour, so it is not sufficient to only include the first column when $OrderBy is blank.
由于字段列表可以在数据对象配置中的其他位置进行自定义，因此情况也有些复杂，因此我们不能依赖*用作字段列表-它可以包含几乎所有在常规SQL字段列表中有效.但是，如果这样要求太高，那么解决上述较简单情况的方法将是一个不错的开始！
The situation is also slightly complicated in the fact that the field list is customisable elsewhere in the data object configuration, so we can't rely on * being used as the field list - it could contain pretty much anything that is valid in a normal SQL field list. However, if that is asking too much, a solution to the simpler case (as outlined above) would be a good start!
推荐答案
基本问题-是否可以抑制对ORDER BY列重复的这种SQL Server限制-Venu回答了:不，不是.
The basic question - is it possible to suppress this SQL Server restriction on ORDER BY column duplication - was answered by Venu: No it is not.
关于如何使用通用方式绕过此限制进行编码，有各种建议(大部分来自我).对于将来的读者来说，如果您要改编现有系统，那么这些答案可能是最有用的. (如果您是从头开始的，请尝试并完全避免这种情况.)
There are various suggestions (mostly from me) about how you could possibly code around this limitation in a generic manner. For any future readers, those answers are probably the most helpful if you are adapting an existing system. (If you are starting from scratch, just try and avoid this situation altogether.)
但是，我真正想到的解决方案是为DBAL的内部API添加版本控制. API版本现在为2，但是您可以调用setApiVersion(1)来指示后端使用旧版本的API.
However, the actual solution that I came to was to add versioning to our internal API for our DBAL. The API version is now 2 but you can call setApiVersion(1) to instruct the back-end to use the old version of the API instead.
v2与v1 *相同，只是它不再自动将列1添加到ORDER BY中，除非它完全为空.因此，新的(v2)项目可以解决SQL Server问题，而现有项目可以设置为使用v1 API，因此可以继续正常工作(但没有SQL Server兼容性).
v2 is identical to v1* except it no longer automatically adds column 1 to the ORDER BY unless it is completely blank. Therefore, the SQL Server issue is resolved for new (v2) projects, whilst existing projects can be set to use the v1 API and therefore continue to work correctly (but without SQL server compatibility).
(*实际上，我已经利用这次机会在v2中进行了其他一些重大更改，但这与该答案无关.)
(* Actually, I've taken this opportunity to make some other breaking changes in v2, but that is not relevant to this answer.)
这篇关于如何避免SQL Server错误在ORDER BY上有重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
