一条这样的sql怎么拼才是对的:SELECT
DISTINCT(t_dim_vdn.vdn_key),DISTINCT(t_dim_brand.SUPNAME)
FROM gdmdw.t_fact_all_smsinfo_dtb t_fact_smsinfo_dtb,
gdmdw.t_dim_brand t_dim_brand,
gdmdw.t_dim_vdn
WHERE t_fact_smsinfo_dtb.brand_key = t_dim_brand.BRAND_KEY
AND t_fact_smsinfo_dtb.vdn_key = t_dim_vdn.vdn_key就是要vdn_key,SUPNAME都要去重复?
DISTINCT(t_dim_vdn.vdn_key),DISTINCT(t_dim_brand.SUPNAME)
FROM gdmdw.t_fact_all_smsinfo_dtb t_fact_smsinfo_dtb,
gdmdw.t_dim_brand t_dim_brand,
gdmdw.t_dim_vdn
WHERE t_fact_smsinfo_dtb.brand_key = t_dim_brand.BRAND_KEY
AND t_fact_smsinfo_dtb.vdn_key = t_dim_vdn.vdn_key就是要vdn_key,SUPNAME都要去重复?
100.00 3 TDBOSS
96.69 2 VIP
95.42 3 VIP
95.58 4 VIP
99.69 2 中国移动
88.99 3 中国移动
95.588 4 中国移动
。
。大家看 vdn_key和SUPNAME的列,现在要实现的效果的是根据最大可查询出有多少个不同的区域,如图:TDBOSS这个品牌只有3这个区域有,因为这里查出来的vdn_key 大家看一下 每种品牌对应的最多区域也就是2,3,4三个区域,所以因为TDBOSS它现在只有3这个区域,而2和4却没有查询显示,我现在要做的是如果TDBOSS这个品牌2和4没有的话 就赋一个空的值他要打到的效果应该是这样:smsSatisfyRate vdn_key SUPNAME
100.00 3 TDBOSS
0.00 2 TDBOSS
0.00 4 TDBOSS
96.69 2 VIP
95.42 3 VIP
95.58 4 VIP
.
.
.
.
这里需要注意的是要先根据这个品牌所占的做多区域来拼sql,就是要保证每种品牌和区域查出来的结果条数一样。如:现在品牌最多的区域有3个,就是2,3,4 所以我们就要求每种查出来的品牌都要有2,3,4区域对应的,这样做的主要原因是为了得到smsSatisfyRate 的值,也就是如果那个区域没值的话,就赋0.00的值给它。像这样:smsSatisfyRate vdn_key SUPNAME
100.00 3 TDBOSS
0.00 2 TDBOSS
0.00 4 TDBOSS
96.69 2 VIP
95.42 3 VIP
95.58 4 VIP
.
.
ROUND((SUM(RETURN3+RETURN4+return5)/SUM(RETURN1+return2+RETURN3+RETURN4+return5))*100,2) AS smsSatisfyRate,
t_dim_vdn.vdn_key,
t_dim_brand.SUPNAME
FROM gdmdw.t_fact_all_smsinfo_dtb t_fact_smsinfo_dtb,
gdmdw.t_dim_brand t_dim_brand,
gdmdw.t_dim_vdn
WHERE t_fact_smsinfo_dtb.brand_key = t_dim_brand.BRAND_KEY
AND t_fact_smsinfo_dtb.vdn_key = t_dim_vdn.vdn_key
AND t_fact_smsinfo_dtb.time_key >= 20101201
AND t_fact_smsinfo_dtb.time_key < 20101216
AND t_dim_brand.vdncode = 2
AND t_dim_brand.STATUS = 1
GROUP BY t_dim_brand.SUPNAME,t_dim_vdn.vdn_key
ORDER BY t_dim_brand.SUPNAME,t_dim_vdn.vdn_key
指导一下?高手们。
((select 1 as vdn_key union select 2 union select 3)a,
(select distinct SUPNAME from tb) b)
left join tb c on a.vdn_key=c.vdn_key and b.SUPNAME=c.SUPNAME
(select 1 as vdn_key union select 2 union select 3)a,
(select distinct SUPNAME from tb) b
left join tb c on a.vdn_key=c.vdn_key and b.SUPNAME=c.SUPNAME
--这样成不?
select isnull(c.smsSatisfyRate,0.00),a.vdn_key,b.SUPNAME from
(select 4 as vdn_key union select 2 union select 3)a,
(select distinct SUPNAME from tb) b
left join tb c on a.vdn_key=c.vdn_key and b.SUPNAME=c.SUPNAME