对更多表进行 UNION 时创建唯一 ID 的建议

Question

我有一个这样的视图

CREATE VIEW PEOPLE

AS

SELECT CustomerId, CustomerName FROM Customers
UNION ALL
SELECT EmployeeId, EmployeeName FROM Employees
UNION ALL
SELECT FriendId, FriendName From Friends

，现在我需要为该视图添加唯一的 ID，因为我当然可以有一个 CustomerId = 15 和一个 EmployeeID = 15

所以技巧我正在做的是以下

SELECT
  CAST('1' + CAST(CustomerId AS VARCHAR(30)) AS INT) as UniqueCustomerId,
 CustomerId, CustomerName FROM Customers
UNION ALL
SELECT
  CAST('2' + CAST(EmployeeId AS VARCHAR(30)) AS INT) as UniqueEmployeeId,
 EmployeeId, EmployeeName FROM Employees
UNION ALL
SELECT
  CAST('3' + CAST(FriendId AS VARCHAR(30)) AS INT) as UniqueFriendId, 
FriendId, FriendName From Friends

无论如何，由于我有很多记录，所以转换为 varchar(30) 并返回 int 是一项开销。

你能建议一个更好的方法吗？

Answer 1

如果你必须有一个 id，只需做数学计算：

SELECT  1000000 + CustomerID AS UniqueCustomerId
      , CustomerId
      , CustomerName
FROM    Customers
UNION ALL
SELECT  2000000 + EmployeeId AS UniqueEmployeeId
      , EmployeeId
      , EmployeeName
FROM    Employees
UNION ALL
SELECT  3000000 + FriendId AS UniqueFriendId
      , FriendId
      , FriendName
FROM    Friends 

Answer 2

我更喜欢这种方法，因为它仍然可以使用 CustomerID、EmployeeID 和 FriendID 上的索引

SELECT 1 [PersonType],CustomerId [PersonId], CustomerName [PersonName] FROM Customers
UNION ALL
SELECT 2, EmployeeId, EmployeeName FROM Employees
UNION ALL
SELECT 3, FriendId, FriendName From Friends

Answer 3

干净且可读性好，我在 Postgres 中使用它。

select CAST(row_number() OVER () AS bigint) AS id,
       *
from (
         select mtm.id     as mkt_tech_map_id,
                'vesivagu' as type,
                mv.geom,
                ''         as label
         from mkt_tech_map mtm
                  left join mkt_vesivagu mv on mtm.id = mv.mkt_tech_map_id

         union

         select mtm.id       as mkt_tech_map_id,
                'kraavisete' as type,
                mk.geom,
                ''           as label
         from mkt_tech_map mtm
                  left join mkt_kraavisete mk on mtm.id = mk.mkt_tech_map_id

         
     ) as q

Answer 4

这应该可以正常工作：

CREATE VIEW PEOPLE AS  
SELECT CustomerId, null as EmployeeId, null as FriendId, CustomerName FROM Customers 
UNION ALL 
SELECT null, EmployeeId, null, EmployeeName FROM Employees 
UNION ALL 
SELECT null, null, FriendId, FriendName From Friends