我在ssrs折线图中使用以下查询。它根据每个订单日期计算每月记录的订单数量。

我的问题是，当一个月没有订单时，而不是说零或为空，它将一起删除该月的行。我希望它将其计为零，但也可以使用null。

基本上，无论是否包含信息，我都希望总是有十二行。

我怎样才能解决这个问题？有没有可以使用的表达方式？还是我遗漏了一些显而易见的东西？

SELECT
MONTH(Ord.OrdDate) AS 'MONTH',COUNT(CASE WHEN @Worker_ID1 IS NULL OR @Worker_ID1 = Worker.ID THEN 1 END) AS 'Worker1',COUNT(CASE WHEN @Worker_ID2 IS NULL OR @Worker_ID2 = Worker.ID THEN 1 END) AS 'Worker2',COUNT(CASE WHEN @Worker_ID3 IS NULL OR @Worker_ID3 = Worker.ID THEN 1 END) AS 'Worker3',COUNT(CASE WHEN @Worker_ID4 IS NULL OR @Worker_ID4 = Worker.ID THEN 1 END) AS 'Worker4',COUNT(CASE WHEN @Worker_ID5 IS NULL OR @Worker_ID5 = Worker.ID THEN 1 END) AS 'Worker5'

FROM Ord
JOIN Prod ON Ord.Prod_ID = Prod.ID
JOIN ProdType ON Prod.ProdType_ID = ProdType.ID
JOIN Grouping ON Ord.Grouping_ID = Grouping.ID
JOIN Worker ON Grouping.Worker_ID = Worker.ID

WHERE @Year = YEAR(Ord.OrdDate)
AND (@ProdType_ID IS NULL OR @ProdType_ID = ProdType.ID)

GROUP BY MONTH(Ord.OrdDate)

//获取面试结果为已通过的
Integer passInterviewList = baseMapper.selectCount(new QueryWrapper<NeoCrmCustomerInterview>()
        .and(ele -> ele.eq("interview_status_init", CustomerInterviewEnum.PASS_WAIT_FEEDBACK.getCode()).or()
                .eq("interview_status_init", CustomerInterviewEnum.PASS_FEEDBACK.getCode()))
        //优化sql，interviewer_json未null或key不存在指定数据时会报错--优化兼容不存在问题
        //.last("and interviewer_json -> ''$.interviewerId'' like ''%" + id + "%''"));
        .last("CASE WHEN JSON_VALID(interviewer_json) THEN JSON_UNQUOTE(JSON_EXTRACT(interviewer_json, ''$.interviewerId'')) like ''%" + id + "%'' ELSE null END"));

当我在angular2-meteor项目中使用 Collection2时,demo的这些代码总是在终端给我警告：

No best common type exists among return expressions.

我该如何改进代码？谢谢

{
  createdAt: {
    type: Date,autovalue: function() {
      if (this.isInsert) {
        return new Date();
      } else if (this.isUpsert) {
        return {$setonInsert: new Date()};
      } else {
        this.unset();
      }
    }
  }
}

由于每个返回分支都需要一种Date类型,因此必须为每个if / else分支返回Date类型,或者您可以创建一个返回两种不同类型的union.

在任何一种情况下,如果类型为Date,则可以为第三个条件返回null.这在打字稿中是有效的.

autovalue: function() : Date|Object  {
    if (this.isInsert) {
        return new Date();
    } else if (this.isUpsert) {
        return {$setonInsert: new Date()};
    } else {
        this.unset();
        return null;
    }
}

我收到错误消息：

syntax error (missing operator) in query expression '((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                  dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number)'.

我以前遇到过此问题，并且知道它不喜欢我的括号设置，但是我所做的一切似乎都无法使MS Access感到满意。

SELECT     dbo_tblMailList.mail_FirstName,dbo_tblMailList.mail_LastName,dbo_tblMailList.mail_Address1,dbo_tblMailList.mail_Address2,dbo_tblMailList.mail_City,dbo_tblMailList.mail_State,dbo_tblMailList.mail_Zip,dbo_tblMailList.mail_Phone1,dbo_tblMailList.mail_Email1,dbo_tblMailList.mail_Comp_GenAm,dbo_tblBidder.bidder_number,SUM(dbo_tblItem.item_pr) AS SumOfitem_pr,SUM(dbo_tblItem.item_premium) 
                      AS SumOfitem_premium,dbo_tblBidder.bidder_type,dbo_tblSale.sale_id
FROM         (dbo_tblMailList LEFT JOIN
                      dbo_tblBidder ON ((dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id) AND (dbo_tblBidder.bidder_sale_id IN (319)))) LEFT JOIN
                      dbo_tblSale ON ((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                      dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number))
GROUP BY dbo_tblMailList.mail_FirstName,dbo_tblMailList.mail_Comp_Art,dbo_tblMailList.mail_Comp_IndArt,dbo_tblMailList.mail_Comp_Fire,dbo_tblMailList.mail_Comp_Ceramic,dbo_tblSale.sale_id
HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)

如果有人有任何想法，请告诉我。

谢谢，詹姆斯

编辑

使用FROM下面的Gareth子句，我现在有：

SELECT     dbo_tblMailList.mail_FirstName,dbo_tblSale.sale_id
FROM    (
            (   dbo_tblMailList 
                LEFT JOIN dbo_tblBidder 
                    ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                    AND dbo_tblBidder.bidder_sale_id IN (319)
            )
            LEFT JOIN dbo_tblSale 
                ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
        )
        LEFT JOIN dbo_tblItem
            ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
            AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
GROUP BY dbo_tblMailList.mail_FirstName,dbo_tblSale.sale_id
HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)

但是我得到了错误 Extra ) in query expression ''.

我认为这可能与之有关AND dbo_tblBidder.bidder_sale_id IN (319)。如果将其更改为AND dbo_tblBidder.bidder_sale_id = 319，则会收到错误消息：

JOIN expression not supported.

它突出了dbo_tblBidder.bidder_sale_id = 319。

结论

这是我的最终工作代码：

SELECT     mail_ID,mail_FirstName,mail_LastName,mail_Address1,mail_Address2,mail_City,mail_State,mail_Zip,mail_Phone1,mail_Email1,mail_Comp_GenAm,SUM(SumOfitem_pr) AS SumOfitem_price,SUM(SumOfitem_premium) AS SumOfitem_premiums
FROM (
    SELECT     dbo_tblMailList.mail_ID,dbo_tblMailList.mail_FirstName,SUM(dbo_tblItem.item_premium) 
                          AS SumOfitem_premium
    FROM    (
                (   dbo_tblMailList 
                    LEFT JOIN dbo_tblBidder 
                        ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                )
                LEFT JOIN dbo_tblSale 
                    ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
            )
            LEFT JOIN dbo_tblItem
                ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
    WHERE      dbo_tblMailList.mail_Comp_GenAm = 1 AND dbo_tblBidder.bidder_sale_id IN (319)
    GROUP BY dbo_tblMailList.mail_ID,dbo_tblMailList.mail_Comp_Ceramic
    UNION
    SELECT     dbo_tblMailList.mail_ID,0 AS SumOfitem_pr,0 
                          AS SumOfitem_premium
    FROM    (
                (   dbo_tblMailList 
                    LEFT JOIN dbo_tblBidder 
                        ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                )
                LEFT JOIN dbo_tblSale 
                    ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
            )
            LEFT JOIN dbo_tblItem
                ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
    WHERE      dbo_tblMailList.mail_Comp_GenAm = 1
    GROUP BY dbo_tblMailList.mail_ID,dbo_tblMailList.mail_Comp_Ceramic
    )
GROUP BY mail_ID,SumOfitem_price,SumOfitem_premiums

谢谢加雷斯！