SQL server CROSS JOIN 的用法

SELECT @DateThreshold as dt,
		       t3.ck_id, t3.ck_name, t3.title_id,t3.title,
			   case when t4.num is null then 0 else t4.num end num,
			   case when t4.money is null then 0 else t4.money end money,
			   t3.startDay,t3.endDay  
		from 
		(SELECT t1.ck_id, t1.ck_name, t2.title_id,t2.title,t2.startDay,t2.endDay from
		(SELECT DISTINCT ck_id, ck_name FROM dws_erp_ku_ck_age_num_money_init WHERE dt = @DateThreshold) t1
		CROSS JOIN (SELECT DISTINCT id AS title_id ,title,startDay,endDay FROM dim_ku_kuAgeDateSet WHERE dt = @DateThreshold) t2
		) t3 
		left join (SELECT ck_id, ck_name, title_id, title, num, money 
				FROM dws_erp_ku_ck_age_num_money_init 
				WHERE dt = @DateThreshold) t4
		on t3.ck_id = t4.ck_id and t3.title_id = t4.title_id
		order by t3.ck_id

先将 两个表的维度进行 笛卡尔积 cross join，

SELECT t1.ck_id, t1.ck_name, t2.title_id,t2.title,t2.startDay,t2.endDay from
		(SELECT DISTINCT ck_id, ck_name FROM dws_erp_ku_ck_age_num_money_init WHERE dt = @DateThreshold) t1
		CROSS JOIN (SELECT DISTINCT id AS title_id ,title,startDay,endDay FROM dim_ku_kuAgeDateSet WHERE dt = @DateThreshold) t2

再和数据表进行left join 
最后补齐数据 缺失的数据补0 返回结果如下：

--------------------------------------------------------------------------------------------

CROSS JOIN 是 SQL 中的一种连接类型，它会产生两个表之间的笛卡尔积（Cartesian product）。这意味着，如果第一个表有 𝑚m 行，第二个表有 𝑛n 行，那么 CROSS JOIN 会生成 𝑚×𝑛m×n 行的结果集，每一行都是第一个表的一行与第二个表的所有行的组合。

例如，假设有两个表：

Table A:

id	name
1	a
2	b

Table B:

id	value
10	x
20	y

执行 CROSS JOIN 后的结果将是：

A.id	A.name	B.id	B.value
1	a	10	x
1	a	20	y
2	b	10	x
2	b	20	y

在之前的 SQL 示例中，我们使用 CROSS JOIN 来生成所有可能的月份、公司名称和地区的组合。这确保了即使在某些月份没有数据的情况下，也能得到完整的记录，并且可以用 COALESCE 来填充缺失的值为0。

--------------------------------

select t2.ym,
       t2.tel_name,
	   t2.area_name,
	   case when t1.value is null then 0 else t1.value end from
(SELECT 
    t4.ym,
    t3.tel_name,
    t4.area_name
FROM (
    -- 获取所有相关的公司名称
    SELECT DISTINCT tel_name
    FROM dwd_contractlist_ext
    WHERE dt = '2024-10-16' AND ym between '2023-03' and '2023-11' AND tel_name LIKE '%颇尔%' AND area_name LIKE '%北京%'
) t3
CROSS JOIN (
    -- 获取所有相关的月份和区域
    SELECT DISTINCT ym, area_name
    FROM dwd_contractlist_ext
    WHERE dt = '2024-10-16' AND ym between '2023-03' and '2023-11' AND tel_name LIKE '%颇尔%' AND area_name LIKE '%北京%'
) t4) t2
LEFT JOIN (
    -- 计算每个公司的总金额
    SELECT ym, tel_name, area_name, SUM(TaxDstYhMoney) AS value
    FROM dwd_contractlist_ext
    WHERE dt = '2024-10-16' AND ym between '2023-03' and '2023-11' AND tel_name LIKE '%颇尔%' AND area_name LIKE '%北京%'
    GROUP BY ym, tel_name, area_name
) t1
ON t2.ym = t1.ym AND t2.tel_name = t1.tel_name AND t2.area_name = t1.area_name