如何获得在过去365天内购买次数最多但以前没有的客户

Question

我在编写这个sql查询时遇到问题。基本上，我需要找到在过去365天内销售额最多，但在365天之前销售额为零的前100家公司。这是在节点中完成的，所以它是一个字符串，我也在传递参数。开始日期是365天前的日期。

当我运行它时，它不会抛出任何错误，但也不会返回任何数据。

getMostSales365NoneBefore(startDate) {
let sqlQuery = '';
sqlQuery =
"SELECT TOP 100 SUM(nQuoteTotal) AS nSales, sCompany FROM Customer_Quotes WHERE (bDeleted=0 AND sStatus='Closed' AND dtFirstClosed > " +
"'" +
startDate +
"')" +
' AND (nSales < ' +
"'" +
startDate +
"') IS NOT NULL" +
' GROUP BY sCompany ORDER BY nSales DESC';
console.log(sqlQuery);
return sqlQuery;
}, ```

Answer 1

您应该正确地参数化查询，并将@startDate作为参数传递

请注意，不能在WHERE部分中引用聚合，只能在HAVING、ORDER BY或SELECT中引用

你想要的查询看起来像这个

SELECT TOP (100)
SUM(CASE WHEN dtFirstClosed >= @startDate THEN nQuoteTotal END) AS nSales,
sCompany
FROM Customer_Quotes
WHERE bDeleted = 0
AND sStatus = 'Closed'
GROUP BY sCompany
HAVING COUNT(CASE WHEN dtFirstClosed < @startDate THEN 1 END) = 0
ORDER BY nSales DESC