如何获得与SQL服务器连接的所有值?

Question

我有一个表，给出了每个客户的价值总和，我想要的是按月按客户制作销售报告，但是有些客户在某个月没有购买任何东西，尽管我希望他们以0值出现，以与当前月份进行比较。

;with Months AS
(SELECT 1 AS MonthNum
UNION ALL
SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12)
select c.client_id,Months.MonthNum 'Mês2021', sum(sales) 'v' from fact_table as c cross join Months
where Months.MonthNum=month(c.date)
and year(c.date)=2021 group by c.intCodEntidade,Months.MonthNum order by c.client_id,Months.MonthNum

但是它不返回我想要的空值，相反，它过滤并且不显示空值。

请heelp。

Answer 1

你没有解释得很好，但让我们假设这是你的最小可复制示例:

declare @fact_table as table (
[date] datetime,
client_id int,
sales money
)
insert into @fact_table values ('26/09/21', 1, 10)
insert into @fact_table values ('26/10/21', 1, 15)
;with Months AS
(SELECT 1 AS MonthNum
UNION ALL
SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12)
select c.client_id,Months.MonthNum 'Mês2021', sum(sales) 'v' from @fact_table as c cross join Months
where Months.MonthNum=month(c.date)
and year(c.date)=2021 
group by c.client_id,Months.MonthNum
order by c.client_id,Months.MonthNum

结果(这对你来说是错误的)是:client_id | Mês2021 |----------- | ----------- | ---------------------1 | 9 | 100001 | 10 | 15,00

并且您希望所有客户在每个月都出现。是吗?如果是这样，也许你的问题的解决办法是加入"客户"。表:

declare @fact_table as table (
[date] datetime,
client_id int,
sales money
)
declare @clients as table (
client_id int
)
insert into @fact_table values ('26/09/21', 1, 10)
insert into @fact_table values ('26/10/21', 2, 15)
insert into @clients values (1)
insert into @clients values (2)
;with Months AS
(SELECT 1 AS MonthNum
UNION ALL
SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12)
select l.client_id,m.MonthNum 'Mês2021', isnull(sum(sales),0) 'v' 
from @clients l cross join Months m
left join @fact_table c
on l.client_id = c.client_id and m.MonthNum=month(c.date) and year(c.date)=2021
group by l.client_id,m.MonthNum
order by l.client_id,m.MonthNum

它会返回这个:

<表类>client_idMes2021vtbody><<tr>110, 00120, 00130, 00140, 00150, 00160, 00170, 00180, 001910, 001100, 001110, 001120, 00210, 00220, 00230, 00240, 00250, 00260, 00270, 00280, 00290, 0021015日002110, 002120, 00