SQL动态ORDER BY使用别名
发布时间:2021-04-03 04:16:39 所属栏目:MsSql教程 来源:网络整理
导读:使用SQL Server,我可以使用别名来命令正常的SELECT查询: SELECT u.FirstName + ' ' + u.LastName as PhysicianName,COUNT(r.Id) as ReferralsCountFROM Referrals r INNER JOIN Users u ON r.PhysicianId = u.IdGROUP BY r.PhysicianId,u.FirstName,u.LastNa
使用SQL Server,我可以使用别名来命令正常的SELECT查询: SELECT u.FirstName + ' ' + u.LastName as PhysicianName,COUNT(r.Id) as ReferralsCount FROM Referrals r INNER JOIN Users u ON r.PhysicianId = u.Id GROUP BY r.PhysicianId,u.FirstName,u.LastName ORDER BY PhysicianName 但是,尝试使用动态ORDER BY执行相同的操作: SELECT u.FirstName + ' ' + u.LastName as PhysicianName,COUNT(r.Id) as ReferralsCount FROM Referrals r INNER JOIN Users u ON r.PhysicianId = u.Id GROUP BY r.PhysicianId,u.LastName ORDER BY CASE WHEN @orderby = 'PhysicianName' THEN PhysicianName END,CASE WHEN @orderby = 'ReferralsCount' THEN ReferralsCount END 产生以下错误:
解决方法SELECT中定义的列别名只能在ORDER BY中单独使用.不在表达中.您可以按如下方式调整原始尝试. ;WITH T AS (SELECT u.FirstName + ' ' + u.LastName AS PhysicianName,COUNT(r.Id) AS ReferralsCount FROM Referrals r INNER JOIN Users u ON r.PhysicianId = u.Id GROUP BY r.PhysicianId,u.LastName) SELECT * FROM T ORDER BY CASE WHEN @orderby = 'PhysicianName' THEN PhysicianName END,CASE WHEN @orderby = 'ReferralsCount' THEN ReferralsCount END (编辑:应用网_常德站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容
- SQL Server 2008:将列值转换为行
- 当div设置contentEditable=true时,重置其内容后无法光标定位
- sql-server – 我可以在存储过程中设置默认架构吗?
- sql-server – 使用GUI恢复数据库 – 要恢复的文件错误
- sql-server – 为什么我们需要在SQL Server中重建和重组索引
- sql – 用于传递闭包的递归查询
- sql-server – 如何在不使用.NET的情况下从SQL Server 2008
- 数据库设计 – 教授告诉我们将序列化的Java对象存储为blob而
- sql-server-2005 – 从SQL Server 2008降级到2005
- 在SQL中处理’可
站长推荐
热点阅读