服务器显示0,当没有记录被发现时，SQL服务器显示计数为0

即使这是旧的，有几个downvotes，最近我解决我的问题。我必须生成一个包含日期的表格。

SELECT SUM(CONVERT(int, case(vtl.oeil) WHEN RTRIM('I') THEN 2 ELSE 1 END)) as nb,

CONVERT(date, c.Date_commande) as Date_commande,

RTRIM(f.Code_fournis) AS Code_fournis

FROM Commande c

LEFT JOIN Fournisseur f

ON c.Code_fournis = f.Code_fournis

WHERE c.Code_fournis = 'XNZ'

AND Convert(date, c.Date_commande)

BETWEEN '2015-11-30'

AND '2015-12-04'

GROUP BY CONVERT(date, c.Date_commande), f.Code_fournis

UNION ALL

SELECT '0', date, 'XNZ'

FROM allDates

WHERE date

BETWEEN '2015-11-30'

AND '2015-12-04'

AND date NOT IN (

SELECT convert(date, c.Date_commande) as date

FROM allDates ad

INNER JOIN Commande c

ON ad.date = convert(date, c.Date_commande)

WHERE c.Code_fournis = 'XNZ'

and convert(date, c.Date_commande)

BETWEEN '2015-11-30'

AND '2015-12-04'

)

ORDER BY c.date_commande asc

这与我所要求的有点不同，但希望它可以帮助某人。