如何创建将原表中每列转为一行的视图？

要实现你需要的视图，核心是把原表的列转换为行（行转列），同时只提取原表第一行的对应列值，并绑定你自定义的货币代码。下面分不同数据库给出具体的SQL实现：

通用思路 #

1. 先获取原表的第一行数据（直接忽略其他行）
2. 使用UNION ALL将每个目标列转换成单独的行，同时指定自定义的currency值和视图的id
3. 将结果创建为视图

MySQL / PostgreSQL 实现 #

CREATE VIEW currency_rates AS
WITH first_row AS (
    -- 一次性获取原表第一行数据，避免重复查询原表
    SELECT hwerohwsd, dwerwefwf, wfsdwg, fwegwg 
    FROM your_table  -- 替换成你的原表实际名称
    LIMIT 1  -- 只取结果集的第一行
)
SELECT 1 AS id, 'EUR' AS currency, hwerohwsd AS rate FROM first_row
UNION ALL
SELECT 2 AS id, 'JPY' AS currency, dwerwefwf AS rate FROM first_row
UNION ALL
SELECT 3 AS id, 'GBP' AS currency, wfsdwg AS rate FROM first_row
UNION ALL
SELECT 4 AS id, 'CHF' AS currency, fwegwg AS rate FROM first_row;

SQL Server 实现 #

SQL Server用TOP 1替代LIMIT 1来获取第一行：

CREATE VIEW currency_rates AS
WITH first_row AS (
    SELECT TOP 1 hwerohwsd, dwerwefwf, wfsdwg, fwegwg 
    FROM your_table
)
SELECT 1 AS id, 'EUR' AS currency, hwerohwsd AS rate FROM first_row
UNION ALL
SELECT 2 AS id, 'JPY' AS currency, dwerwefwf AS rate FROM first_row
UNION ALL
SELECT 3 AS id, 'GBP' AS currency, wfsdwg AS rate FROM first_row
UNION ALL
SELECT 4 AS id, 'CHF' AS currency, fwegwg AS rate FROM first_row;

Oracle 实现 #

Oracle使用ROWNUM <= 1来筛选第一行数据：

CREATE VIEW currency_rates AS
WITH first_row AS (
    SELECT hwerohwsd, dwerwefwf, wfsdwg, fwegwg 
    FROM your_table
    WHERE ROWNUM <= 1
)
SELECT 1 AS id, 'EUR' AS currency, hwerohwsd AS rate FROM first_row
UNION ALL
SELECT 2 AS id, 'JPY' AS currency, dwerwefwf AS rate FROM first_row
UNION ALL
SELECT 3 AS id, 'GBP' AS currency, wfsdwg AS rate FROM first_row
UNION ALL
SELECT 4 AS id, 'CHF' AS currency, fwegwg AS rate FROM first_row;

关键说明 #

• CTE的作用：通过WITH子句一次性获取第一行数据，避免在每个SELECT里重复查询原表，提升执行效率。
• 忽略多余行：用LIMIT 1/TOP 1/ROWNUM <=1确保只取原表的第一行，不会被重复的id行干扰。
• 自定义货币标识：你可以直接修改'EUR'、'JPY'这些字符串，替换成你需要的其他货币代码。

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