SQL 按别名分组

Group By Alias name

提问by Yousi

I have the following T-SQL query:

我有以下 T-SQL 查询：

select 
    count(CaseId),
    (SELECT DATEDIFF(day,CreateDate,LastActivityDate)) AS DiffDate
from 
    VW_Case_Analysis
where 
    CaseStatus = 'C' 
    and LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
Group By 
    DiffDate

I am getting the following error:

我收到以下错误：

Msg 207, Level 16, State 1, Line 15
Invalid column name 'DiffDate'.

消息 207，级别 16，状态 1，第 15 行
无效的列名称“DiffDate”。

The idea behind this query is that I want to get number of cases solved (closed) within how many days.

这个查询背后的想法是我想在多少天内解决（关闭）的案例数量。

Example:

例子：

Days

天

1 = 3 cases

1 = 3 个案例

2 = 50 cases

2 = 50 个案例

3 = 20 cases

3 = 20 个案例

how can I achieve this?

我怎样才能做到这一点？

回答by John Woo

You need to used the whole expression in the GROUP BYclause or just wrap the whole statement in a subquery an do the grouping on the outer statement.

您需要在GROUP BY子句中使用整个表达式，或者只是将整个语句包装在子查询中，然后对外部语句进行分组。

The reason why you can't use ALIASon the GROUP BYclause that is created on the same level of the SELECTstatement is because the GROUP BYis executed before the SELECTclause in which the ALIASis created.

为什么你不能使用的原因ALIAS的GROUP BY是在同级别中创建条款SELECT语句是因为GROUP BY在之前执行SELECT其中的条款ALIAS被创建。

This is the SQL Order of Operation:

这是 SQL 操作顺序：

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

• FROM 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• SELECT 子句
• ORDER BY 子句

Try this query,

试试这个查询，

SELECT  COUNT(CaseId),
        DiffDate
FROM
        (
            select  CaseId,
                    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
            from    VW_Case_Analysis
            where   CaseStatus = 'C' and 
                    LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
        ) sub
Group By DiffDate

回答by Ahmed

You cannot use alias in the group by clause.

您不能在 group by 子句中使用别名。

Either use a derived table or in your case simple remove the 2nd SELECT.

要么使用派生表，要么在您的情况下简单地删除第二个 SELECT。

select 
    count(CaseId),
    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
from 
    VW_Case_Analysis
where 
    CaseStatus = 'C' 
    and LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
Group By 
    DATEDIFF(day,CreateDate,LastActivityDate)

回答by DK91

Wouldn't this get you the same results?

这不会给你同样的结果吗？

select  Count(CaseId),
                    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
            from    VW_Case_Analysis
            where   CaseStatus = 'C' and 
                    LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
GROUP BY DATEDIFF(day,CreateDate,LastActivityDate)

I am a novice at SQL so maybe I am not grasping it correctly. I saw the comment above mine with the exact syntax but I didn't know if it was right...

我是 SQL 的新手，所以也许我没有正确掌握它。我用确切的语法看到了我上面的评论，但我不知道它是否正确......

回答by user4848354

this is a easy example of Implementation Discussion

这是实现讨论的一个简单示例

select selectResualt.num ,count(selectResualt.num) as count  
from (select src_num As num from mytable  UNION ALL select dis_num As num from mytable) as selectResualt
group by selectResualt.num
order by count(selectResualt.num) DESC;>

回答by Ricardo

You can use CROSS APPLYto create an alias and use it in the GROUP BYclause, like so:

您可以使用CROSS APPLY来创建别名并在GROUP BY子句中使用它，如下所示：

select 
    count(CaseId),
    DiffDate
from 
    VW_Case_Analysis
cross apply
    (SELECT DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate) Alias
where 
    CaseStatus = 'C' 
    and LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
Group By 
    DiffDate