MSSQL查询问题 - 将别名放在查询的GROUP BY部分

I have a query which basically looks like this:

我有一个基本上看起来像这样的查询：

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        t.Title
        , t.ItemID
        , t.UploadDate
        , t.SevenDaySales as SelectedColumnSales
        , t.QuantitySold
        , t.CurrentPrice
        , t.CurrentPrice as LastSalePrice

FROM 
        dbo.SearchedUserItems t
WHERE 
        T.SearchedUserID = 5   
GROUP BY 
        t.ItemID,t.UploadDate,t.SelectedColumnSales,t.QuantitySold,t.CurrentPrice,LastSalePrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

The issue is that I can't finish the query because it says that columns SelectedColumnSales and LastSalePrice are nonexistent...

问题是我无法完成查询，因为它表示SelectedColumnSales和LastSalePrice列不存在...

Is there any workaround for this ? =/

这有什么解决方法吗？ = /

How can I finish this query for it to actually work and use the aliases in group by statement, or some other way?

如何才能完成此查询以实际工作并在group by语句或其他方式中使用别名？

Can someone help me out ?

有人可以帮我吗？

4 个解决方案

#1

Due to the way the query is processed by SQL Server, you cannot use aliases in the GROUP BY clause.

由于SQL Server处理查询的方式，您不能在GROUP BY子句中使用别名。

SQL Server will process your query starting at the FROM clause. In your case processing will then move to WHERE, and then GROUP BY, before moving to SELECT.

SQL Server将从FROM子句开始处理您的查询。在您的情况下，处理将移至WHERE，然后移至GROUP BY，然后再移至SELECT。

Because of this, by definition your alias SelectedColumnSales does not yet exist.

因此，根据定义，您的别名SelectedColumnSales尚不存在。

You need to use actual column names in your group by definition, you cannot use column aliases.

您需要根据定义在组中使用实际列名，不能使用列别名。

Swap SelectColumnSales for t.SevenDaySales and LastSalePrice for t.CurrentPrice in the GROUP BY

为GROUP BY中的t.CurrentPrice交换t.SevenDaySales和LastSalePrice的SelectColumnSales

#2

It's a simple fix. You can't use alias names in the GROUP BY. Instead, use the source column name:

这是一个简单的修复。您不能在GROUP BY中使用别名。而是使用源列名称：

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        t.Title
        , t.ItemID
        , t.UploadDate
        , t.SevenDaySales as SelectedColumnSales
        , t.QuantitySold
        , t.CurrentPrice
        , t.CurrentPrice as LastSalePrice

FROM 
        dbo.SearchedUserItems t
WHERE 
        T.SearchedUserID = 5   
GROUP BY 
        t.ItemID,t.UploadDate,t.SevenDaySales,t.QuantitySold,t.CurrentPrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

#3

I don't understand why you are using a window function like that in an aggregation query. One way to do what you want uses apply:

我不明白为什么你在聚合查询中使用这样的窗口函数。实现您想要的用途的一种方法适用：

SELECT      . . . 
FROM        dbo.searcheduseritems sui 
CROSS apply (VALUES 
            ( 
                        sevendaysales, 
                        currentprice 
            ) 
            ) v(selectedcolumnsales, lastsaleprice) 
WHERE       sui.searcheduserid = 5 
GROUP BY    sui.itemid, 
            sui.uploaddate, 
            v.selectedcolumnsales, 
            sui.quantitysold, 
            sui.currentprice, 
            v.lastsaleprice 
ORDER BY    v.selectedcolumnsales DESC offset + 55*0 rowsFETCH next 55 rows only

The query doesn't make sense to me (as being something useful), but this will allow you to do what you want.

查询对我来说没有意义（作为有用的东西），但这将允许你做你想要的。

#4

Wrap the original query up in a derived table (sub-query), then you can use the column aliases in the GROUP BY:

将原始查询包装在派生表（子查询）中，然后您可以使用GROUP BY中的列别名：

SELECT
     COUNT(*) OVER () AS TotalRowsFound,
     dt.*
FROM
(
    SELECT 
            t.Title
            , t.ItemID
            , t.UploadDate
            , t.SevenDaySales as SelectedColumnSales
            , t.QuantitySold
            , t.CurrentPrice
            , t.CurrentPrice as LastSalePrice
    FROM 
            dbo.SearchedUserItems t
    WHERE 
            T.SearchedUserID = 5   
) dt
GROUP BY ItemID, UploadDate, SelectedColumnSales, QuantitySold, CurrentPrice, LastSalePrice
ORDER BY SelectedColumnSales  DESC
OFFSET + 55*0 ROWS FETCH NEXT 55 ROWS ONLY;

#1