为不存在日期的每个组获取额外的行

Question

我已经玩了好几天了，似乎不能想出什么东西。我有这样的查询:

select 
  v.emp_name as Name
  ,MONTH(v.YearMonth) as m 
  ,v.SalesTotal as Amount
from SalesTotals

结果如下:

 Name         m         Amount
 Smith        1         123.50
 Smith        2          40.21
 Smith        3         444.21
 Smith        4          23.21
 Jones        1         121.00
 Jones        2         499.00
 Jones        3          23.23
 Jones        4          41.82
 etc....

我需要做的是使用JOIN或其他东西，这样我就可以为每个月(1-12)获得NULL值，为每个名称:

 Name         m         Amount
 Smith        1         123.50
 Smith        2          40.21
 Smith        3         444.21
 Smith        4          23.21
 Smith        5           NULL
 Smith        6           NULL
 Smith        ...         NULL
 Smith        12          NULL
 Jones        1         121.00
 Jones        2         499.00
 Jones        3          23.23
 Jones        4          41.82
 Jones        5           NULL
 Jones        ...         NULL
 Jones        12          NULL
 etc....

我有一个"数字"表，并尝试做:

select 
  v.emp_name as Name
  ,MONTH(v.YearMonth) as m 
  ,v.SalesTotal as Amount
from SalesTotals
   FULL JOIN Number n on n.Number = MONTH(v.YearMonth) and n in(1,2,3,4,5,6,7,8,9,10,11,12)

但这只给了我6个额外的NULL行，我想要的实际上是每组名称的6个NULL行。我尝试过使用Group By，但不确定如何在这样的JOIN语句中使用它，甚至不确定这是否是正确的路线。

任何建议或指导都是非常感激的!

Answer 1

有一种方法:

select 
  s.emp_name as Name
  ,s.Number as m 
  ,st.salestotal as Amount
from (
  select distinct emp_name, number
  from salestotals, numbers 
  where number between 1 and 12) s left join salestotals st on 
    s.emp_name = st.emp_name and s.number = month(st.yearmonth)

Answer 2

你可以这样做:

SELECT  EN.emp_name Name,
        N.Number M,
        ST.SalesTotal Amount
FROM (  SELECT Number 
        FROM NumberTable
        WHERE Number BETWEEN 1 AND 12) N
CROSS JOIN (SELECT DISTINCT emp_name
            FROM SalesTotals) EN
LEFT JOIN SalesTotals ST
    ON N.Number = MONTH(ST.YearMonth)
    AND EN.emp_name = ST.emp_name