为联合分组查询结果添加VARCHAR自增ID及架构选型咨询

我正在设计数据库，已编写如下联合分组查询语句：

select Name, prod_cat, prod_country, count(*)
from table1 
group by Name, prod_cat, prod_country

union

select Name, prod_cat, prod_country, count(*)
from table2 
group by Name, prod_cat, prod_country

现需为该查询结果添加以U000001起始、格式为U+6位数字的VARCHAR类型自增ID列，同时咨询以下两个问题：

1. 考虑后续会有更多数据接入，将查询结果存入临时表/视图还是使用存储过程更符合数据库优化要求？
2. 如何为上述查询添加该VARCHAR类型自增ID列，是否需要使用声明变量并递增的方式？

预期结果如下：

问题1：临时表/视图/存储过程选型建议 #

• 视图：适合需要实时获取最新数据的场景，它是虚拟表，每次查询都会重新执行底层联合分组逻辑，保证数据实时性。但数据量增大、查询频繁时，重复计算会带来性能损耗。
• 临时表：适合需要多次复用查询结果的场景，一次性计算后将结果存入临时表，后续直接查询临时表即可，性能优于重复执行联合查询。但临时表仅在当前会话有效，会话结束后自动销毁；若需长期保留结果，建议使用普通物理表。
• 存储过程：并非存储结果的载体，而是封装查询逻辑的容器。如果后续需要定期刷新结果、或结合其他业务逻辑（比如自动生成ID、同步数据），可以用存储过程封装「执行联合查询+插入到目标表（临时/普通）」的整套流程，提升逻辑复用性和维护性。

总结：要实时数据选视图；要会话内复用结果选临时表；有复杂定期执行/业务逻辑需求选存储过程封装流程。

问题2：自定义格式自增ID的实现方式 #

不需要强制使用声明变量递增的方式（当然也支持），利用窗口函数是更简洁通用的方案，不同数据库的具体实现如下：

MySQL 实现 #

用ROW_NUMBER()生成连续数字，结合LPAD格式化6位长度并拼接前缀：

SELECT 
    CONCAT('U', LPAD(ROW_NUMBER() OVER (ORDER BY Name, prod_cat, prod_country), 6, '0')) AS Prod_ID,
    Name,
    prod_cat,
    prod_country,
    cnt
FROM (
    select Name, prod_cat, prod_country, count(*) as cnt
    from table1 
    group by Name, prod_cat, prod_country
    union
    select Name, prod_cat, prod_country, count(*) as cnt
    from table2 
    group by Name, prod_cat, prod_country
) AS union_result;

注：ORDER BY子句可根据业务需求调整，确保ID生成顺序符合预期。

SQL Server 实现 #

用ROW_NUMBER()生成序号，FORMAT函数格式化6位数字：

SELECT 
    'U' + FORMAT(ROW_NUMBER() OVER (ORDER BY Name, prod_cat, prod_country), '000000') AS Prod_ID,
    Name,
    prod_cat,
    prod_country,
    cnt
FROM (
    select Name, prod_cat, prod_country, count(*) as cnt
    from table1 
    group by Name, prod_cat, prod_country
    union
    select Name, prod_cat, prod_country, count(*) as cnt
    from table2 
    group by Name, prod_cat, prod_country
) AS union_result;

Oracle 实现 #

用ROW_NUMBER()生成序号，LPAD格式化后拼接前缀：

SELECT 
    'U' || LPAD(ROW_NUMBER() OVER (ORDER BY Name, prod_cat, prod_country), 6, '0') AS Prod_ID,
    Name,
    prod_cat,
    prod_country,
    cnt
FROM (
    select Name, prod_cat, prod_country, count(*) as cnt
    from table1 
    group by Name, prod_cat, prod_country
    union
    select Name, prod_cat, prod_country, count(*) as cnt
    from table2 
    group by Name, prod_cat, prod_country
) union_result;

可选：变量递增方式（MySQL示例） #

如果偏好使用变量实现，也可以这么写，但不如窗口函数简洁：

SET @id := 0;
SELECT 
    CONCAT('U', LPAD((@id := @id + 1), 6, '0')) AS Prod_ID,
    Name,
    prod_cat,
    prod_country,
    cnt
FROM (
    select Name, prod_cat, prod_country, count(*) as cnt
    from table1 
    group by Name, prod_cat, prod_country
    union
    select Name, prod_cat, prod_country, count(*) as cnt
    from table2 
    group by Name, prod_cat, prod_country
) AS union_result
ORDER BY Name, prod_cat, prod_country;

内容的提问来源于stack exchange，提问作者Abhiram Reddy Kotu