sql - 如何转换为 CTE

Question

我对 CTE 作品感到有些困惑。我需要将我的语句从带有 UNION 的汇总转换为 CTE ......但 CTE 让我有点困惑。

任何想法如何解决这个问题？

SELECT
    ProductName,
    Sum ((ItemPrice - DiscountAmount)*Quantity) as ProductTotal
FROM
    Products 
 JOIN OrderItems 
ON Products.ProductID = OrderItems.productID

GROUP BY ProductName WITH ROLLUP

Order By ProductTotal

标签： sqlunioncommon-table-expression

Answer 1

您可以使用 CTE 将其表示为：

with cte as (
      select ProductName,
             Sum((ItemPrice - DiscountAmount)*Quantity) as ProductTotal
      from Products p join
           OrderItems oi
           on p.ProductID = oi.productID
      group by ProductName
     )
select *
from cte
union all
select null, sum(producttotal)
from cte
order by producttotal;