简单视图设计

[happy243]

表1有字段 a,代表员工
表2有字段 b,c,d,代表通用流程
表3有字段 a,b,c,d,有外键指向表1,代表针对某个员工的特殊流程
现在要个视图(a,b,c,d)
列出所有员工的流程
请问如何设计

[happy243]

算了我已经做出来了


WITH X(a,b,c,d,e) AS
(SELECT A.a,B.* FROM 表1 A, 表2 B WHERE A.b=B.b)
SELECT CASE WHEN Y.a IS NOT NULL THEN Y.a ELSE X.a END AS a,
CASE WHEN Y.b IS NOT NULL THEN Y.b ELSE X.b END AS b,
CASE WHEN Y.c IS NOT NULL THEN Y.c ELSE X.c END AS c,
CASE WHEN Y.d IS NOT NULL THEN Y.d ELSE X.d END AS d,
CASE WHEN Y.e IS NOT NULL THEN Y.e ELSE X.e END AS e
FROM X LEFT JOIN 表3 Y
ON X.a=Y.a AND X.b=Y.b AND X.c=Y.c AND X.d=Y.d