如何创建含多列表的视图？合并无关联表的distinct状态列

要实现从三个无关联表中提取各自状态列的去重值，生成带id列的合并表/视图，核心思路是给每个表的去重结果生成虚拟行号，以此作为关联键进行全连接，避免UNION导致的列合并或JOIN无关联键的问题。

适用数据库（支持窗口函数与FULL JOIN：PostgreSQL、SQL Server等） #

CREATE VIEW status_summary AS
SELECT
    COALESCE(c.id, co.id, p.id) AS id,
    c.customer_status,
    co.company_status,
    p.product_status
FROM
    -- 给table1的去重客户状态生成行号
    (SELECT 
        ROW_NUMBER() OVER () AS id, 
        customer_status 
     FROM (SELECT DISTINCT customer_status FROM table1) AS cs) AS c
-- 全连接table2的去重公司状态
FULL JOIN
    (SELECT 
        ROW_NUMBER() OVER () AS id, 
        company_status 
     FROM (SELECT DISTINCT company_status FROM table2) AS cos) AS co
ON c.id = co.id
-- 全连接table3的去重产品状态
FULL JOIN
    (SELECT 
        ROW_NUMBER() OVER () AS id, 
        product_status 
     FROM (SELECT DISTINCT product_status FROM table3) AS ps) AS p
ON COALESCE(c.id, co.id) = p.id;

代码说明 #

1. 每个子查询先通过SELECT DISTINCT提取对应表的唯一状态值，再用ROW_NUMBER()生成连续行号作为虚拟关联键。
2. 使用FULL JOIN确保三个表的所有状态值都被保留，即使某一行仅在其中一个或两个表中有数据。
3. COALESCE(c.id, co.id, p.id)用来生成连续的id值，避免因某表行号缺失导致id为空。

MySQL 8.0以下版本（无窗口函数，用自定义变量生成行号） #

由于MySQL早期版本不支持FULL JOIN，需要用LEFT JOIN + UNION RIGHT JOIN模拟全连接：

CREATE VIEW status_summary AS
-- 先取左连接的结果（包含table1所有行，匹配table2、table3对应行）
SELECT
    COALESCE(c.id, co.id, p.id) AS id,
    c.customer_status,
    co.company_status,
    p.product_status
FROM
    (SELECT 
        @row1 := @row1 + 1 AS id, 
        customer_status 
     FROM (SELECT DISTINCT customer_status FROM table1) AS cs,
          (SELECT @row1 := 0) AS init) AS c
LEFT JOIN
    (SELECT 
        @row2 := @row2 + 1 AS id, 
        company_status 
     FROM (SELECT DISTINCT company_status FROM table2) AS cos,
          (SELECT @row2 := 0) AS init) AS co
ON c.id = co.id
LEFT JOIN
    (SELECT 
        @row3 := @row3 + 1 AS id, 
        product_status 
     FROM (SELECT DISTINCT product_status FROM table3) AS ps,
          (SELECT @row3 := 0) AS init) AS p
ON c.id = p.id
-- 再补充右连接中仅table2/table3有的行
UNION
SELECT
    COALESCE(c.id, co.id, p.id) AS id,
    c.customer_status,
    co.company_status,
    p.product_status
FROM
    (SELECT 
        @row1 := @row1 + 1 AS id, 
        customer_status 
     FROM (SELECT DISTINCT customer_status FROM table1) AS cs,
          (SELECT @row1 := 0) AS init) AS c
RIGHT JOIN
    (SELECT 
        @row2 := @row2 + 1 AS id, 
        company_status 
     FROM (SELECT DISTINCT company_status FROM table2) AS cos,
          (SELECT @row2 := 0) AS init) AS co
ON c.id = co.id
RIGHT JOIN
    (SELECT 
        @row3 := @row3 + 1 AS id, 
        product_status 
     FROM (SELECT DISTINCT product_status FROM table3) AS ps,
          (SELECT @row3 := 0) AS init) AS p
ON co.id = p.id
WHERE c.id IS NULL;

内容的提问来源于stack exchange，提问作者Chris