子查询上的SQL GROUP BY

I have a query that will return results from 2 tables into 1 using a UNION ALL, which all works as I need it to. However I need to run a GROUP BY and an ORDER BY on the returned dataset however I am getting many errors and I'm not sure how to solve it.

我有一个查询,将使用UNION ALL将2个表的结果返回到1,这些都可以正常工作。但是我需要在返回的数据集上运行GROUP BY和ORDER BY,但是我遇到了很多错误,我不知道如何解决它。

Here is my Query:

这是我的查询:

SELECT ProductID, Quantity 
FROM BasketItems 
UNION ALL 
SELECT ProductID, Quantity 
FROM OrderItems

This will return a results set such as this:

这将返回一个结果集,如下所示:

ProductID  Quantity  
15         2
20         2
15         1
8          5
5          1

I then want to run a GROUP BY on the ProductID field and then finally an ORDER BY DESC on the Quantity field. So in the final output, this particular results set will finally result in this:

然后,我想在ProductID字段上运行GROUP BY,最后在Quantity字段上运行ORDER BY DESC。所以在最终输出中,这个特定的结果集最终会导致:

ProductID
8
15
20
5

I can then run queries on this result set as I usually do

然后,我可以像往常一样对此结果集运行查询

EDIT:

As stated above, but maybe not implied enough is that I will need to run queries on the returned results, which isn't working as you cannot run a query on a set of results that have an ORDER BY clause (so far as I gathered from the error list)

如上所述,但可能没有暗示我将需要对返回的结果运行查询,这不起作用,因为您无法对具有ORDER BY子句的一组结果运行查询(就我收集而言)来自错误列表)

If you want more information on the problem, here it is:

如果您想了解有关该问题的更多信息,请点击此处:

From this results set, I want to get the products from the product table that they relate to

从这个结果集中,我想从产品表中获取与之相关的产品

SELECT * FROM Products WHERE ID IN (
    SELECT ProductID
    FROM
    (
        SELECT ProductID, Quantity  
        FROM BasketItems  
        UNION ALL  
        SELECT ProductID, Quantity  
        FROM OrderItems 
    ) v
    GROUP BY ProductID
    ORDER BY SUM(Quantity) DESC
)

However, I get this error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

但是,我收到此错误:ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效,除非还指定了TOP,OFFSET或FOR XML。

The output of products need to be in the order that they are returned in the sub query (By quantity)

产品的输出必须按子查询中返回的顺序(按数量)

3 个解决方案

#1

SELECT Products.*
FROM Products
     INNER JOIN 
(
    SELECT ProductID, Sum(Quantity) as QuantitySum
    from
    (
        SELECT ProductID, Quantity  
        FROM BasketItems  
        UNION ALL  
        SELECT ProductID, Quantity  
        FROM OrderItems 
    ) v
    GROUP BY ProductID
) ProductTotals
    ON Products.ID = ProductTotals.ProductID
ORDER BY QuantitySum DESC

#2

will this work?

这会有用吗?

SELECT ProductID
    from
    (
        SELECT ProductID, Quantity  
        FROM BasketItems  
        UNION ALL  
        SELECT ProductID, Quantity  
        FROM OrderItems 
    ) temp
    GROUP BY temp.ProductID
    ORDER BY SUM(temp.Quantity) desc

#3

Here's a cte version (no live test so please excuse blunders)

这是一个cte版本(没有现场测试,所以请原谅错误)

EDIT

;WITH myInitialdata_cte(ProductID,Quantity)
AS
    (
    SELECT ProductID, Quantity FROM BasketItems
    UNION ALL  
    SELECT ProductID, Quantity  FROM OrderItems 
    )
SELECT b.ID
FROM 
     myInitialdata_cte a
     INNER JOIN Products b ON
         a.ProductID = b.ID
GROUP BY ProductID
ORDER BY SUM(a.Quantity) DESC

#1

SELECT Products.*
FROM Products
     INNER JOIN 
(
    SELECT ProductID, Sum(Quantity) as QuantitySum
    from
    (
        SELECT ProductID, Quantity  
        FROM BasketItems  
        UNION ALL  
        SELECT ProductID, Quantity  
        FROM OrderItems 
    ) v
    GROUP BY ProductID
) ProductTotals
    ON Products.ID = ProductTotals.ProductID
ORDER BY QuantitySum DESC

#2