sql - 在SQL Server CTE中获取 “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
               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

当我运行这个，我得到



有人可以告诉我我在做什么错吗？

而且，当我运行这个

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

我懂了

最佳答案

[编辑]

我试图重写您的查询，但是将别名与定义“d”的查询中的聚合列相关联后，即使您的查询也可以使用。

我认为您正在寻找以下内容:

第一:

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

第二个:

select
    c.duration,
    c.totalbookings,
    d.bkdqty
from
    (select
               month(bookingdate) as duration,
               count(*) as totalbookings
           from
               entbookings
           group by month(bookingdate)
    ) AS c
    inner join
    (select
               month(clothdeliverydate) 'clothdeliverydatemonth',
               SUM(CONVERT(INT, deliveredqty)) 'bkdqty'
           FROM
               barcodetable
           where
               month(clothdeliverydate) is not null
               group by month(clothdeliverydate)
    ) AS d
    on c.duration = d.duration

关于sql - 在SQL Server CTE中获取 “No column was specified for column 2 of ' d'”？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12119073/