无效的列名与RANK OVER函数SQL

Question

尝试创建一个按产品销售的总数量的表，并选择按日期分段的销售数量第三高的产品。不断出现错误

无效的列名

为RANK () OVER语句的别名:

select 
    RANK () OVER (PARTITION BY t3.orderdate order by t3.total_amt_ordered) as ranking,
    t3.productid, 
    t3.orderdate,
    t3.total_amt_ordered
from 
    (select 
         t2.productid,
         t1.orderdate,
         SUM(t2.orderqty) as total_amt_ordered 
     from
         saleslt.salesorderheader t1 
     inner join 
         saleslt.salesorderdetail t2 on t1.salesorderid = t2.salesorderid 
     group by 
         productid, orderdate) t3
where 
    ranking = 3; 

Answer 1

WHERE在RANK之前求值，因此您不能在没有派生表/CTE的情况下直接使用它，但它也在SUM之后计算，导致以下查询:

select *
from 
  (  select 
         t2.productid,
         t1.orderdate,
         SUM(t2.orderqty) as total_amt_ordered,
         RANK ()
         OVER (PARTITION BY t1.orderdate
               order by SUM(t2.orderqty)) as ranking
     from
         saleslt.salesorderheader t1 
     inner join 
         saleslt.salesorderdetail t2 on t1.salesorderid = t2.salesorderid 
     group by 
         productid, orderdate
  ) t3
where 
    t3.ranking = 3; 

Answer 2

下面的查询将返回按订单日期级别划分的所有产品id和排名为3的所有订单数量。

SELECT * FROM
     (
        select 
                DENSE_RANK () OVER (PARTITION BY t3.orderdate order by  
                t3.total_amt_ordered DESC ) as ranking,
                t3.productid, 
                t3.orderdate,
                t3.total_amt_ordered
          from ( 
                 select 
                         t2.productid,
                         t1.orderdate,
                         SUM(t2.orderqty) as total_amt_ordered 
                   from
                         saleslt.salesorderheader t1 
                     inner join
                         saleslt.salesorderdetail t2 
                     on t1.salesorderid=t2.salesorderid 
                   group by productid, orderdate) t3
      ) Z
where Z.ranking= 3; 

Answer 3

您可以这样使用，您不能在where条件下使用排名查询和相同查询

;WITH cte
AS (SELECT
    RANK() OVER (PARTITION BY t3.orderdate ORDER BY t3.total_amt_ordered) AS ranking,
    t3.productid,
    t3.orderdate,
    t3.total_amt_ordered
FROM (SELECT
    t2.productid,
    t1.orderdate,
    SUM(t2.orderqty) AS total_amt_ordered
FROM saleslt.salesorderheader t1
INNER JOIN saleslt.salesorderdetail t2
    ON t1.salesorderid = t2.salesorderid
GROUP BY    productid,
            orderdate) t3)
SELECT
    *
FROM cte
WHERE ranking = 3;