T-SQL Получить все годы от - до
Я пытаюсь найти списки продаж каждого магазина за каждый год из базы данных. Но я хочу распечатать все годы с нулевыми продажами (для некоторых лет/магазинов в моей базе данных не будет данных)
Я выполнил следующую процедуру, но она возвращает только годы и магазины с ненулевыми значениями.
SELECT YEAR(temp.INVOICE_DATE) AS Year, Shop.Name, SUM(temp.QTY * Product.Price) AS Total
FROM (
SELECT pci.INVOICE_ID, ci.STORE_ID, ci.INVOICE_DATE, pci.PRODUCT_ID, pci.QTY
FROM [Product_Customer Invoice] pci, [Customer Invoice] ci
WHERE pci.INVOICE_ID = ci.INVOICE_ID
)AS temp, Product, Shop
WHERE Product.PRODUCT_ID = temp.PRODUCT_ID AND Shop.STORE_ID = temp.STORE_ID
GROUP BY YEAR(temp.INVOICE_DATE), Shop.Name
ORDER BY Year ASC
Я получаю следующий результат
Я хотел бы попросить любые идеи, как включить 0 для лет или магазинов, чтобы не было никаких продаж.
Ответы 1
Используйте cross join для создания строк, а затем left join для ввода нужных данных:
SELECT y.Year, s.Shop.Name,
COALESCE(SUM(pci.QTY * p.Price), 0) AS Total
FROM Shop s CROSS JOIN
(SELECT DISTINCT invoice_date as year FROM [Product_Customer Invoice]
) y LEFT JOIN
[Product_Customer Invoice] pci
ON YEAR(pci.invoice_date) = y.year AND
pci.store_id = s.store_id LEFT JOIN
Product p
ON pci.PRODUCT_ID = p.PRODUCT_ID
GROUP BY y.year, s.name
ORDER BY y.year, s.name;
Обратите внимание, что это также исправляет ваш синтаксис join и удаляет ненужный подзапрос.
Это предполагает, что все годы, которые вы хотите, находятся в [Product_Customer Invoice]. Если вам нужен другой набор лет, либо явно перечислите их с помощью VALUES(), используйте рекурсивное CTE для их создания или используйте таблицу календаря.
Пробовали
GROUP BY ALL YEAR(temp.INVOICE_DATE), Shop.Name? (обратите внимание на "ВСЕ")