sql - 在sql server cte中获取 "No column was specified for column 2 of ' d'"？

Question

我有这个查询，但它没有按预期工作，

with c as (select 
               month(bookingdate) as duration, 
               count(*) as totalbookings 
           from 
               entbookings
           group by month(bookingdate)
          ),
     d as (SELECT 
               duration, 
               sum(totalitems) 
           FROM 
               [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty
           group by duration
          )

select 
    c.duration, 
    c.totalbookings, 
    d.bkdqty 
from
    c 
    inner join d 
    on c.duration = d.duration

当我运行这个时，我得到

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'd'.

谁能告诉我我做错了什么吗？

另外，当我运行这个时，

with c as (select 
               month(bookingdate) as duration, 
               count(*) as totalbookings 
           from 
               entbookings
           group by month(bookingdate)
          ),
     d as (select 
               month(clothdeliverydate), 
               SUM(CONVERT(INT, deliveredqty)) 
           FROM 
               barcodetable
           where 
               month(clothdeliverydate) is not null
               group by month(clothdeliverydate)
          )

select 
    c.duration, 
    c.totalbookings, 
    d.bkdqty 
from
    c 
    inner join d 
    on c.duration = d.duration

我明白

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'd'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'd'.

Answer 1

您只需为 CTE 中的聚合列提供别名

d as (SELECT 
   duration, 
   sum(totalitems) as sumtotalitems
FROM 
   [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty
group by duration
)