时间戳

首页 > 解决方案 > ORDER BY 子句在视图、内联函数、派生表、子查询等中无效，除非还指定了 TOP 或 FOR XML

sql - ORDER BY 子句在视图、内联函数、派生表、子查询等中无效，除非还指定了 TOP 或 FOR XML

问题描述

我正在尝试结合以下 3 个查询：

select distinct  
    col1 
from 
    [dbname] 

select  
    COUNT(*)col2  
from 
    [dbname]  
group by 
    col1 
order by 
    col1

select 
    (COUNT(**)-1) /  COUNT(distinct col3)
from 
    [dbname] 
group by 
    col1 
order by 
    col1

作为 ：

select distinct col1 , 
        (select  COUNT(*)col2 from [dbname] group by col1 order by col1 )as something, 
        (select (COUNT(*)-1) /  COUNT(distinct col3)from [dbname] group by col1 order by col1) as something1 
from [dbname]

但我收到以下错误：

ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP 或 FOR XML。

如何正确构图？

标签： sqlsql-serversql-server-2008tsql

解决方案

您可以使用subquery：

select col1, count(*) something1,
       count(*)-1 / (select count(distinct col3) from dbname where col1 = d.col1) something2
from dbname d
group by col1;

但是，您也可以直接将其变形为单个SELECT语句

select col1, count(*) something1,
       (count(*)-1 / count(distinct col3)) something2
from dbname d
group by col1;

编辑：注意除以零错误，因此您可以包含case表达式

select col1, count(*) something1,
       coalesce((count(*)-1/(case when count(distinct col3) = 0 
                                  then null else count(distinct col3) end)), 0) something2
from dbname d
group by col1;

推荐阅读