将表与连续ID合并到一个视图中

Question

我有以下表：

Answer1         |   Answer2     |   Answer3
AnswerID        |       ID      |   ID
QuestionnaireID |QuestionnaireId|   QuestID
EmployeeName    | Employeename  |   EmployeeName
PcName          |   PcName      |   PcName
Answer          |   AnswerText  |   Answer
AnswerDate      |   AnswerDate  |   Ans_Date

我想在MS SQL Server中创建视图，并且我想将这些值合并为一个视图，并通过固定的FIEDS进行修改。每个表都有从1开始的答案(或ID(是相等的，例如。1000，添加答案2的记录将更新答案2.ID为答案。

CREATE VIEW Answer
AS
SELECT  a.AnswerID ID,
    a.Answer Text,
    a.AnswerDate Date,
FROM    Answer1 a

我现在该怎么办？我想加入不是执行此类任务的方法。

谢谢

Answer 1

如果您需要连续值，为什么不使用row_number()并重新分配所有IDS？

select AnswerID as ID, a.Answer as Text, a.AnswerDate as Date,
       row_number() over (order by which, id) as new_id
from ((select a.*, 1 as which from answer1 a) union all
      (select a.*, 2 as which from answer2 a) union all
      (select a.*, 3 as which from answer3 a)
     ) a;

Answer 2

我不确定为什么要这样做，因为从视图中返回新ID不会对您有所帮助，但是如果您想要每个表的唯一ID，则可以尝试以下内容。

WITH TBL1 AS
(
 SELECT ROW_NUMBER() OVER(ORDER BY ID) [NewId], * FROM Answer1
),    
 TBL2 AS
(
  SELECT  ROW_NUMBER()  OVER(ORDER BY ID) 
     + (SELECT MAX([NewId]) FROM TBL1) AS [NewId2],* FROM Answer2
),    
TBL3 AS 
(
  SELECT  ROW_NUMBER()  OVER(ORDER BY ID) 
      + (SELECT MAX([NewId2]) FROM TBL2) AS RN2,* FROM Answer3
)
SELECT * FROM TBL1
UNION 
SELECT * FROM TBL2
UNION 
SELECT * FROM TBL3