我想为下面的场景获取数据

Question

 Table 1                                     Table2
    Name       Month     Cast                   Month
    --------------------------                  ----------------
    Client1    Jan       200                    Jan
    Client1    Feb       150                    Feb
    Client2    Jan       110                    Mar
    Client2    Apr       120                    .
                                                .
                                                Dec





Ouput
-----------------------
Name      Month    Cast
--------------------------
Client1   Jan      200
Client1   Feb      150
Client1   Mar      0
Client1   Apr      0
...
....
Client2   Jan     110
Client2   Feb     0
Client2   Mar     0
Client2   Apr     120
Client2   May     0
.....
....

结果必须显示所有包含表1中可能不存在的所有月份(如果不是强制转换为0)的客户端。我已经尝试了右外部和交叉join...no结果。

Answer 1

你可以试试下面的脚本-

 SELECT A.Name,B.month,COALESCE(C.Cast,0) Cast
 FROM (
    SELECT DISTINCT  1 CN, Name FROM Table1
 ) A
 INNER JOIN (
    SELECT 1 CN,    month FROM Table2
 ) B ON A.CN = B.CN
 LEFT JOIN Table1 C 
 ON A.Name = C.Name
 AND B.month = C.Month

Answer 2

您必须先创建笛卡儿产品(cte ClientMonth)，然后再连接回原始表以获得您想要的数据.

With ClientMonth as (
                  select Distinct Name,t2.[Month] 
                  from Table_1 t1
                  Cross Join  Table_2 t2
                  )
Select cm.Name, cm.[Month], isnull(amt,0)
from ClientMonth cm
Left Join Table_1 t3 on t3.Name = cm.Name and t3.Month = cm.Month

Answer 3

试试看。

SELECT name, m1 as month ,max(cast) as cast from
(
SELECT [id]
  ,[name]
  ,b.month as m1
  ,case when a.month =b.month then [cast] else 0 end as cast
 FROM [Table_1] a,[Table_2] b
)c
group by name,m1
order by name