显示两个表中的排序数据集

Question

我试图构建一个视图，该视图从活动和存档表中选择，但仅在活动文件夹中不存在记录时才从归档文件夹返回记录。它还必须只返回带有最新时间戳的归档记录。

谢谢

示例：

Active:

*ID  ItemID   Price*
1    001     1.00

2    002     4.99

3    004     2.00

档案馆

*ID ItemID   Price   ArchiveDate*

1    001    0.99    1/1/2013

2    002    3.78    1/1/2013

3    003    5.00    1/5/2013

4    005    3.49    1/5/2013

5    003    6.99    1/10/2013

应该返回以下数据集

*ItemID Price*

001     1.00

002     4.99

003     6.99   <-- From Archive

004     2.00

005     3.49   <-- From Archive

Answer 1

因此，您需要从活动(联盟的第一部分下面[，然后追加档案[联盟的第二部分]的记录。对于Archive记录，exlcude那些在活动表中不存在的-- NOT子句--并且只获取具有MAX(ArchiveDate)子句最近日期的记录。

SELECT  ItemID
        ,Price
FROM    ACTIVE
UNION
SELECT  ItemID
        ,Price
FROM    ARCHIVE AA
WHERE   AA.ArchiveDate = (SELECT MAX(ArchiveDate) FROM ARCHIVE AA1 WHERE AA1.ItemID = AA.ItemID)
AND     NOT EXISTS (SELECT 1 FROM ACTIVE A WHERE A.ItemID = AA.ItemID)

Answer 2

您可以使用以下内容：

SELECT ItemID, Price
FROM 
(
-- All of the active records
SELECT ItemID, Price 
FROM Active 
UNION ALL    
-- combined with the latest archive records for missing actives
SELECT ItemID, Price 
FROM Archive AS A1
WHERE ArchiveDate=(SELECT MAX(ArchiveDate) 
                   FROM Archive AS A2
                   WHERE A2.ItemID=A1.ItemID)
      AND NOT EXISTS(SELECT 1 
                     FROM Active AS AC 
                     WHERE AC.ItemID=A1.ItemID)
) AS FinalResult
-- Ordered by ItemID
ORDER BY ItemID

Answer 3

;with cte_archive as (
     select
         ItemID, Price,
         row_number() over (partition by ItemID order by ArchiveDate desc) as row_num
     from Archive 
)
select AC.ItemID, AC.Price
from Active as AC

union all

select AH.ItemID, AH.Price
from cte_archive as AH
where AH.ItemID not in (select ItemID from Active) and AH.row_num = 1