# 9 关于数据仓库维度数据处理的方法探究系列——雪花维

## 5、雪花维概述

### 5.2实现

 CREATE TABLE t_tmp_xxx ( ID VARCHAR(20) NOT NULL, SuperID VARCHAR(20) NOT NULL, Name VARCHAR(50) , CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID) ) go CREATE TABLE t_tmp_super_xxx ( SuperID VARCHAR(20) NOT NULL, Name VARCHAR(50) , CONSTRAINT PK_t_tmp_super_xxx PRIMARY KEY (SuperID) ) go CREATE TABLE t_dem_xxx ( ID VARCHAR(20) NOT NULL, Name VARCHAR(50) , SuperID VARCHAR(20) NOT NULL, SuperName VARCHAR(50), CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID) ) go INSERT INTO t_tmp_xxx VALUES('1','1','a')INSERT INTO t_tmp_xxx VALUES('2','2','b')INSERT INTO t_tmp_xxx VALUES('3','3','c')INSERT INTO t_tmp_super_xxx VALUES('2','bb')INSERT INTO t_tmp_super_xxx VALUES('4','dd') Go CREATE VIEW v_dem_xxx AS SELECT '-1' as ID,’缺省’ Name,'-1' SuperID, ‘缺省' SuperName Union All SELECT a.ID ID,a.name Name, CASE WHEN b.superid IS NULL THEN '-1' ELSE b.superid END SuperID, CASE WHEN b.superid IS NULL THEN’缺省' ELSE b.name END SuperName FROM t_tmp_xxx a LEFT JOIN t_tmp_super_xxx b ON a.SuperID=b.SuperID

• 博文量
412
• 访问量
1110117