达梦与mssql的order by的区别

• 在单表简单查询时，mssql和dm8都可以通过查询字段名或别名进行order by

mssql和dm8，使用字段名进行order by 

select emp_ID,emp_Name from Employee order by emp_Name

mssql和dm8，使用字段别名进行order by 

select emp_ID,emp_Name as 姓名 from Employee order by 姓名

mssql和dm8，有字段别名，也可以使用字段名进行order by 

select emp_ID,emp_Name as 姓名 from Employee order by emp_Name

• 以下这种复杂查询，mssql和dm8使用order by就有所区别了

mssql的order by 示例：

--mssql order by 的使用情况
--该示例查询语句查询结果返回的字段为：id, name, icon, parentId, routePath, routeComponent, routeName, routeParentName
--使用情况1：使用order by mod_parentId,mod_ID
select mod_ID as id,mod_Name as name,mod_icon as icon,mod_parentId as parentId,mod_routePath as routePath,mod_routeComponent as routeComponent,mod_routeName as routeName,mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用union前面的查询语句实体字段mod_parentId,mod_ID进行排序
order by mod_parentId,mod_ID

----------------------------------------------------------------------
--使用情况2：使用order by parentId,id
select mod_ID as id,mod_Name as name,mod_icon as icon,mod_parentId as parentId,mod_routePath as routePath,mod_routeComponent as routeComponent,mod_routeName as routeName,mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用查询结果返回的字段parentId,id进行排序，其中parentId是别名字段，不是实体字段
order by parentId,id

--mssql order by 的使用结论：order by 后面的字段，可以是查询结果返回的字段，也可以是查询涉及到的实体字段。

--根据结论，那么该查询使用 order by parentId,id,mod_routeName,name 也应该是可以的
select mod_ID as id,mod_Name as name,mod_icon as icon,mod_parentId as parentId,mod_routePath as routePath,mod_routeComponent as routeComponent,mod_routeName as routeName,mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') != ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用查询结果返回的字段parentId,id,name 和 查询涉及到的实体字段mod_routeName进行排序
order by parentId,id,mod_routeName,name

mssql order by 的使用结论：order by 后面的字段，可以是查询结果返回的字段，也可以是查询涉及到的实体字段。

dm8的order by 示例：

--dm8 order by 的使用情况及结论
--该示例查询语句查询结果返回的字段为：id, name, icon, parentId, routePath, routeComponent, routeName, routeParentName
--使用情况1：使用order by mod_parentId,mod_ID，该语句会报错：无效的列名[MOD_PARENTID]
select mod_ID as id, mod_Name as name, mod_icon as icon, mod_parentId as parentId, mod_routePath as routePath, mod_routeComponent as routeComponent, mod_routeName as routeName, mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用union前面的查询语句实体字段mod_parentId,mod_ID进行排序
order by mod_parentId,mod_ID

----------------------------------------------------------------------
--使用情况2：使用order by parentId,id
select mod_ID as id, mod_Name as name, mod_icon as icon, mod_parentId as parentId, mod_routePath as routePath, mod_routeComponent as routeComponent, mod_routeName as routeName, mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用查询结果返回的字段parentId,id进行排序
order by parentId,id

--dm8 order by 的使用结论：order by 后面的字段，必须是查询结果返回的字段。

--根据结论，那么该查询使用 order by parentId,id,mod_routeName,name 应该会报错：无效的列名[MOD_ROUTENAME]
select mod_ID as id, mod_Name as name, mod_icon as icon, mod_parentId as parentId, mod_routePath as routePath, mod_routeComponent as routeComponent, mod_routeName as routeName, mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用查询结果返回的字段parentId,id,name 和 查询涉及到的实体字段mod_routeName进行排序
order by parentId,id,mod_routeName,name

--根据结论，那么该查询使用 order by parentId,id,routeName,name 应该是可以的
select mod_ID as id, mod_Name as name, mod_icon as icon, mod_parentId as parentId, mod_routePath as routePath, mod_routeComponent as routeComponent, mod_routeName as routeName, mod_routeParentName as routeParentName
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
union
select id,name,null as icon,null as parentId,null as routePath,null as routeComponent,null as routeName,null as routeParentName
from ParentMenu
inner join (
select mod_parentId
from Authority
inner join Module on mod_ID = aut_ModuleID
where aut_EmployeeID = 'manager' and isnull(mod_routePath,'') <> ''
group by mod_parentId) t on t.mod_parentId = ParentMenu.id
--使用查询结果返回的字段parentId,id,routeName,name
order by parentId,id,routeName,name

dm8 order by 的使用结论：order by 后面的字段，必须是查询结果返回的字段。