我想以月份格式检索年度数据，如果在任何月份有空条目，那么它应该显示零值。我也使用虚拟表日历，但它不会正常吗？

（[查询日期]> =转换（DATETIME，cal.Months））或 （[查询日期]为空） Group By cal .Months; select cal.months,Count(enqno) As EnquiryFrom #Calender calLeft Outer Join Enquiry eOn Month(cal.months)=Month([Enquiry date])Where([Enquiry date]<=Convert(DATETIME,cal.Months))AND([Enquiry date]>=Convert(DATETIME,cal.Months))OR([Enquiry date]IS NULL)Group By cal.Months;推荐答案 我会将我的#Calendar更改为Month和Year字段（int）而不是datetime。然后加入 DatePart（MONTH，[查询日期] = cal.Month和DATEPART（年，[查询日期]）= cal.Year 。 我还要重命名我的所有字段以删除空格以删除每个字段周围的[]的要求。I would change my #Calendar to have Month and Year fields (int) instead of a datetime. Then join the DatePart(MONTH,[Enquiry Date] = cal.Month and DATEPART(YEAR,[Enquiry Date]) = cal.Year.I would also rename all my fields to remove the spaces to remove the requirement of [] around every field. 你可以这样做： - 创建一个临时表，其中包含所需日期之间的所有月份，每条记录将有count = 0 然后有另一个临时表可用月份和计数的表格 应用左连接以获取所需数据 you can do it in this way:-Create a temp table that will have all the months between required dates and each record will have count=0Then Have another Temp Table with available months and countApply left join to get required DataWITH cteAS (SELECTdt = DATEADD(DAY, -(DAY(@StartDate) - 1), @StartDate)UNION ALLSELECTDATEADD(MONTH, 1, dt)FROMcteWHEREdt < DATEADD(DAY, -(DAY(@EndDate) - 1), @EndDate))SELECTCONVERT(char(3), dt, 100) + '/' + CONVERT(char(4), dt, 120) AS [month],CAST(0 AS decimal) AS EqcountINTO#AllMonthsDataFROMcte;SELECTCONVERT(char(3), [Enquiry date], 100) + '/' + CONVERT(char(4), [Enquiry date], 120) AS [month],COUNT(enqno) AS EqcountINTO#AllAvailableEnquiresFROMEnquiry eWHERE([Enquiry date] <= @StartDate)AND ([Enquiry date] >= @EndDate)GROUP BYCONVERT(char(3), [Enquiry date], 100) + '/' + CONVERT(char(4), [Enquiry date], 120);SELECTAMD.month,ISNULL(A.Eqcount,AMD.Eqcount) AS EqCountFROM#AllMonthsData AMDLEFT JOIN #AllAvailableEnquires A ON AMD.month = A.month;DROP TABLE #AllMonthsData;DROP TABLE #AllAvailableEnquires; SELECT cal.Months, COUNT(enqno) AS EnquiriesFROM #calendar cal LEFT JOIN enquiryON Month(cal.Months) = Month([enquiry date])WHERE (YEAR([enquiry date])=YEAR(GetDate())) OR [enquiry date] IS NULLGROUP BY cal.Months 这篇关于我想以月份格式检索年度数据，如果在任何月份有空条目，那么它应该显示零值。我也使用虚拟表日历，但它不会正常吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！